Automatically Highlight The Column That Is Today - Formula Friday

Hello Excellers and welcome back to some more #formulafun. Todays’ blog post was inspired by an article I read online where a user wanted to insert the word TODAY to be inserted into a cell that moved down a row as the days of the week progressed in their weekly work schedule. I thought it would be a good idea to expand on this idea on my work week schedule. I want TODAY to be displayed at the top of the column, and also the column to be highlighted for the current day, like my illustration below. Each day the word TODAY moves as well as the column highlighting. This should be penned as a formulaFridayy double edition as there are actually two Excel formulas in this calendar solution.

This looks pretty cool, doesn’t it?. Well, let get on with building it.

Step 1. Insert TODAY As Column Header

The first part is to insert TODAY in Row 1 if the date in row 3 in the current or today’s date. We do this by using the IF function in cells B1:I1.

=IF(B3=TODAY(),”TODAY”,””)

The IF Function is a logical one which allows you to make logical comparisons between a value and what you expect. The simple syntax of IF is

=IF(Something is True, then do something, otherwise do something else). In this formula, we are testing to see if the date in row 3 is today’s date. If it is (or if the comparison is TRUE) then we instruct Excel to insert the word “TODAY”. If the comparison is FALSE then Excel is to leave the cell blank. Straightforward isn’t it?.

So, start off with the formula in cell B1, then drag it along your relevant columns. You will see that 25/07/2018 has TODAY above it in cell D1 (as it is 25/07/2018 when I am writing this blog post).

Step 2. Apply The Conditional Formatting To Columns

Now we can move onto applying the conditional formatting, to the columns. If the column header in Row 1 contains the word TODAY then we want to highlight that column of corresponding cells. This is easy to do, using the ‘use a formula to determine which cells to format’ option in Conditional Formatting | New Rule.

Now we use a simple formula to highlight our selected cells that correspond with the cell column header if it contains the work TODAY.

Highlight the cells you want to conditionally format.

Home Tab | Styles Group | Conditional Formatting
New Rule | Use a formula to determine which cells to format.

In the dialogue box type the following formula

=B$1=”TODAY”

Select the type of formatting you want to apply if the condition you are testing for is true. In my example, I want to highlight the column a grey/blue colour.
Hit ok twice to set the formatting and exit the dialogue box.
Job Done