Creation Process

1. The Objective

Most people have used the predefined conditional formatting (such as highlighting cells greater than X). As usual however, most of the interesting features need a custom solution. For a new Excel user custom conditional formatting is extremely frustrating and confusing, I find this is mainly caused by incorrect absolute reference formulas.

For this project I am going to highlight the current day of the week, which makes a dashboard and table easier to read. I typically use this to in tables that have weeks 1 to 52, and I want to automatically highlight out the current week for easy viewing.

2. Building The Sheet

1. Design your layout.
When creating dashboard I usually start by filling the entire sheet white to remove the grid lines. That allows me to use future colours/borders to point out the data I want people to focus on. 

The week number automatically updates as is the following formula - =ISOWEEKNUM(TODAY())
I almost always want my weeks starting on Monday, so use the Isoweeknum function. Just remove the ISO if you don't want this.

This formula is very versatile and another modification I do when comparing multiple years is add the following - =ISOWEEKNUM(TODAY())+1700 this makes the result 1734. Which is the year and week number! 

The only other formula in the dashboard is the variance, which is =IFERROR(C4-D4,0) The IFERROR is there to allow me to write in "-" in empty cells, else it will throw back an error. 

2. Create custom rule
When creating custom rules in conditional formatting, it is best to figure them out in the spreadsheet before using the conditional formatting tool. Custom rules are very simple to setup - you want your formula to display TRUE when you want the conditional formatting to work. 

In any cell write =2>1 the result will display TRUE which will trigger conditional formatting. 

For out dashboard, we first want to get text of the current day. =TEXT(TODAY(),"dddd") This takes the current date and formats it to just show the day of the week. If you want the dashboard to highlight yesterday/tomorrow, this is where you change it. =TEXT(TODAY()+1,"dddd") or =TEXT(TODAY()-1,"dddd")

That was the hard part! Now we simply want to check if the first row of our dashboard equals our formula. 
=TEXT(TODAY(),"dddd")=$B4 The dollar symbol in front of the B is very important. We are going to drag the conditional formatting across the columns to the left, and I always want it checking the formula against what is in column B. We don't want it changing! However we do want the row number changing, as we drag the formatting down. 

That is our custom rule all done!

3. Applying conditional formatting
Select the range of cells you want formatted, in this case B4:E10. Then Conditional Formatting - New Rule. 
Select the bottom rule type "Use a formula to determine which cells to format"
Enter in our formula =TEXT(TODAY(),"dddd")=$B4

In our selected range, Excel is looking to match our formula up to the text in column B. When it finds TRUE, it will apply the formating to the entire row. 

Click the format button and apply any formatting you want. I recommend a Fill of yellow and bold font. 

Click OK and you now have the dashboard highlighting the current day! Check back tomorrow and it will automatically update. 

It is now very easy to modify it to make it highlight a day you specify. Write the day in a cell (say G3) and use the following formula as our custom rule - =$G$3=$B4

3. Summary

This is a very simple touch to put on your dashboards/tables to make it easier to look at. It is great on dashboards to show your current week/day while still having previous weeks/days displaying, it helps reduce the cluttered data look you would otherwise have. 

I also use it in meetings to talk about specific days/weeks. I use the manual system to highlight the rows I want to focus on for the current conversation.

4. Download

More templates