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”
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.
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”
If you haven’t done so already, log into your Twitter account, go into Settings and enable the Always use HTTPS option. Click Save.
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”