Hiding A To-Date Column When Reporting Month or Quarter End Results
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 you are on.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
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!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
VBA Code To Change All Shape & Chart Placement Properties
What This VBA Macro Does I deal with shapes and charts on a daily basis and one of my (many)...
The VBA Coding Guide For Excel Charts & Graphs
Charts, Charts, & More Charts! Graphical visualizations are arguably the pinnacle of how an analyst shares his/her results and possessing...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.