Building an Excel One-Month Calendar

One of my duties at work is to put together a calendar of various due dates every quarter. Of course, I could manually put the day numbers in, but where’s the fun in that?! The trickiest part in getting Excel to do this automatically is getting the first of the month on the right day. Most people would jump straight to multiple IF() functions, but there is a much more elegant way using the CHOOSE() function.  Continue reading “Building an Excel One-Month Calendar”

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”