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”
Here’s quick tip for you: You can quickly duplicate a worksheet tab by holding the Ctrl key down while you drag it.
Continue reading “Quickly duplicate Excel tab with Ctrl+drag”
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”
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”
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”
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”
Ever hide one or more rows at the top of your worksheet and scratch your head when you want to unhide them? Just press the F5 key and manually enter a cell address for one of the hidden rows. Continue reading “Unhide top row”
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”
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”
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”