The INDIRECT() function

It’s been a while since I posted anything. Here’s some training I did for my work recently:

Using Excel’s INDIRECT function

QUESTION: How can we find the average of X months starting with month Y and be able to change those variables without changing the formula? Continue reading “The INDIRECT() function”

Correcting AutoCorrect

For the most part, Excel’s AutoCorrect feature works well; however, sometimes it makes changes that I just don’t want. The one that always trips me up is when it changes (c) to ©. I often put letters in parenthesis to reference a footnote, like (a), (b) and ultimately the problematic (c).

Unfortunately, unlike with Word, you can’t just press Ctrl-Z and undo the auto-correction. Fixing this in Excel requires a little more work. Continue reading “Correcting AutoCorrect”

Fast input of data by row instead of column

By default, if you press the Enter key after you type in some data, Excel moves the selected cell down. This can result in a lot of extra keystrokes or mouse clicks, if you need to enter data by row rather than column. Of course, you can change this action, but it’s kind of a pain if it’s just for a one-time use. Continue reading “Fast input of data by row instead of column”

Best way to use a pivot table as data for a report

If you know how to use Pivot tables, you may be tempted to use one as the source data for a report. You should almost never do that. Pivot tables are not meant to be static . If the data that drives the pivot table changes unexpectedly, your report changes and you may not realize it. Not only that, but when you reference a cell in a Pivot table, you get the weird GETPIVOTDATA function which doesn’t copy relatively very well. Continue reading “Best way to use a pivot table as data for a report”

Don’t hide rows/columns, use Group feature

Take a look at this worksheet snippet. What do you see?

Right off the bat, you should notice is that rows 11 to 15 are grouped and hidden from view. A closer look should also tell you that rows 3 to 8 are hidden. Continue reading “Don’t hide rows/columns, use Group feature”

Using VLOOKUP function to detect new lines of data

Let’s say you have a Balance Sheet that you update each month with data from your accounting system. The thing that always trips you up is when a new GL account has been added and it isn’t already in your Balance Sheet. If you’ve got a lot of accounts, this can be a daunting task to do manually. Continue reading “Using VLOOKUP function to detect new lines of data”

Using IFERROR function with VLOOKUPs

One of my favorite new Excel 2007 functions is IFERROR. It provides an elegant way to handle errors that previously would have required ugly IF functions. I use it a lot when I have a column of VLOOKUP functions, which can return #N/A if it can’t find whatever it is looking for. Here’s an example: Continue reading “Using IFERROR function with VLOOKUPs”

Formula for last day of the month using prior month date

I often need to show the last day of the month as a title for columns in a worksheet. It’s nice to use a formula because then, next year, you just change the first month and the rest update auto-magically, accounting for possible leap year (like the one coming up in 2012). Continue reading “Formula for last day of the month using prior month date”