How to highlight specific days, like holidays, in Excel
Conditional formatting
With the conditional formatting tool, you can change the color of your cells automatically.
You can use the default setting but you can also create your own rule with formulas.
- Change the color of the weekends
- Highlight Birthdays
- ...
List of public holidays
In the following document, you have the list of public holidays for the United States
Formula to find a public holidays
In the following calendar we have the days of July 2019. The colors of the weekends are managed by a conditional formatting rule.
The idea is to count if each day in the calendar is in the list of the public holiday. And the function that count items in a list it's the function COUNTIF or COUNTIFS.
For instance, for the 3 July, the formula is the following
=COUNTIF(Holiday!$A$2:$A$12,D1)
And for the 4 July
=COUNTIF(Holiday!$A$2:$A$12,E1)
Convert the formula to a test
As you see, the result expected is 1. So it's easy to write our test like this. Yes, the reference here is B1, the first cell of the calendar
=COUNTIF(Holiday!$A$2:$A$12,B1)=1
Create the conditional formatting rule
We have seen in this article how to create your own conditional formatting rule with formulas.
We write the test in the custom rule textbox.
It is very important to lock only the reference of the row (B$1). Like that, your test will always check the values of the first row ; the date 😉
And finally, we adjust the range of cells where to apply the rules
Impact on the calendar
And then, automatically, when a date is detected in the public holiday table, the rule will change the color in the calendar.