Intermediate | Excel Semi-Pro

Intermediate

Extract Time from a Date-Time Number in Excel

October 9, 2012 Intermediate

I have a worksheet that tracks start and stop times for different events throughout the day, all during the week. Sometimes I have to pull out the Time of Day, irrespective of the Date, with the TIME function. The TIME function has three arguments: Hour, Minute, Second. I could use =TIME(11,30,0) in a cell to […]

Get the full story …

Extract a Date from a Date-Time Number in Excel

September 15, 2012 Intermediate

I recently saw a spreadsheet with the following function =LEFT(A2,8) where cell A2 was equal to 08/06/12 12:23 PM. The user was trying to extract the Date from the Date-Time value. The problem for me was that the LEFT function returns a “text” value. Excel is good at recognizing text values that look like dates, […]

Get the full story …

Add Macro Button to the Toolbar in Excel 2011

July 7, 2012 Intermediate

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps. […]

Get the full story …

Control + Home in Excel for Mac

June 30, 2012 Features

One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years. I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go […]

Get the full story …

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

April 9, 2012 Formulas

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the […]

Get the full story …

How to Update a List or Range without OFFSET

March 22, 2012 Formulas

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome. Worksheet cells that use Data Validation for a drop-down list can simplify the […]

Get the full story …

Select Excel’s Used Range on a Mac

March 20, 2012 Beginner

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button. The used range on a worksheet starts with cell A1 and ends with the last […]

Get the full story …

Pay Periods and Funky PivotTable Controls

October 31, 2011 Intermediate

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart. Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations […]

Get the full story …

Fill Down a Formula with VBA

September 6, 2011 Formulas

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column […]

Get the full story …

A Dynamic Dependent Drop Down List in Excel

May 25, 2011 Formulas

The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011. In this post I’ll create a Table to […]

Related Posts Plugin for WordPress, Blogger...
Get the full story …