The "To-Date" Column
I was first introduced to this concept a few years ago when I first started working in my company's FP&A (Financial Planning & Analysis) group. The idea behind showing a "To-Date" column in a report is to allow your leadership (ie the decision-makers) a view of how the month (quarter, year, etc...) is progressing. The only problem with this type of view (from an analyst's point of view) is when you are at the end of a month or quarter, you end up with two columns displaying the same exact numbers.
This can be confusing to your end users and just plain ugly from a formatting perspective! Luckily we can use conditional formatting to automatically hide the "To-Date" column when it is needed. Take a look at the below GIF image to see the technique you will learn by the end of this post. This technique is incorporated with a quarterly report distributed every month.
Adding A Conditional Format
The first step you need to take after completely setting up your report (To-Date column included) is to add a conditional format rule. The rule you need to add may be foreign to you as you will need to make a Custom Rule.
First you will want to highlight your To-Date column or the area you don't want showing on quarter-ends.
Next you will need to navigate to the Home tab on the Excel Ribbon and select Conditional Formatting from within the Styles group. In the drop down menu select New Rule.
In the New Formatting Rule dialog box, look in the Select a Rule Type list of options. Select the last one stating Use A Formula To Determine Which Cells To Format.
Creating An Acceptable Conditional Formatting Custom Formula
To use this type of formatting you will need to enter in a Boolean Formula. This means the formula will either output a TRUE or a FALSE value. This in effect acts like a light switch where a TRUE output triggers the conditional formatting to appear and a FALSE prevents it from showing.
For this example you are going to use a little bit of creativity to determine when the conditional formatting with need to be activated. In the example we will only want our QTD column to appear hidden during the months of March (3), June (6), September (9), and December (12). While looking at the numerical value of each of these four months, what to you notice about them? If you are thinking, "Well Chris, all these months are divisible by 3!" you are exactly on the right track! With the divisibility of 3 in mind, we can write a formula testing if the report month is divisible by three and if so, turn on the conditional formatting.
To accomplish this test, we will use a function called MOD.
What the MOD function does is let you know the remainder of one number divided by another. If there is no remainder, then we know the input number is perfectly divisible by your divisor.. Check out the below image to see the MOD function in action in relation to possible report dates.
Notice during our Quarter-ending months, the MOD function returns a zero (or FALSE in binary). On all other months the MOD function returns a positive number (or TRUE in binary). This is very important and will help us with our conditional formatting formula.
The only problem with our MOD formula is it will ouput a TRUE (or turning on our formatting) when the month is a NON-quarter-ending month. However you want the formatting to turn on when a quarter-ending month occurs. So what we can do is use the NOT function to change our boolean (true or false) ouputs to their respective opposites. Look what happens when you wrap a NOT function around the MOD function in the below examples.
Notice because the NOT function only accepts logical inputs, the MOD formula now shows either a TRUE or a FALSE.
Adding a Custom Formula to Conditional Formatting
Now that you know how you want to structure your formula, you can begin to write it in the Rule input box.
Note in the formula Cell D2 (our report month) is an absolute reference (has dollar signs). This is import because as the rule gets applied to each cell in your selected range, it will move the cell reference based on which cell is getting tested. You want your formatting rule to only look at the (same) report month cell (Cell D2) so you must add the dollar signs to make the reference absolute instead of relative.
Setting the Format
For this example, you will want your data to look like it is covered with white paint. So click on the Format button (shown in the above picture) and navigate to the Font, Border, and Fill tabs of the Format Cells dialog box and make sure you format everything white. You can click on the below images to enlarge them.
After you have the formatting just the way you want it, click through every OK button you can find until all the dialog boxes are gone. Test your report by changing your report month date and watch as your To-Date column magically appears and disappears based on which month your are on. If you would like, you can click the below link to download the Excel file I used in this post.
As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.
I am sure you can take this method of conditional formatting and apply it to many more reports and concepts. Leave a comment below with ways you do or can apply this type of conditional formatting with your Excel work. Also, can anyone figure out what the conditional formatting formula would be to handle a month-to-date column? I'll post the solution once someone posts a valid formula in the comments section. I look forward to reading your thoughts and solutions!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris "Macro" Newman :)