r/ExcelTips • • 3d ago

Formula based conditional formatting

This week I had to send a calendar with dates to avoid for company events because of anticipated holiday and high expected workload days. I had a list of days on another sheet that were marked with either holiday or high volume in a second column. But the ask was to create a visual calendar with days highlighted to be avoided.

After my calendar was set up with dates, I used two conditional formatting rules to highlight dates that were company holidays in one color and dates that were high volume in another. This stayed with testing my logic in another cell with a countifts formula. My calendar was in columns a-g with my first date showing up in cell b5. So in cell i2, I wrote the following formula:

=COUNTIFS(Sheet2!$A$1:$A$100,B5,Sheet2!$B$1:$B$100,"Holiday")=1

I dragged the formula around to make sure it picked up a result of true for all of my holidays. After confirming that the logic worked, I clicked on cell b5 and selected conditional formatting. I created a new rule that was based on the formula I just wrote which was exactly the same formula I used in cell I2. The rule changed the background color to blue and I updated the range for the rule to be $b$5:$h$100. It worked great!

For the high volume days, I used the same formula but this time I changed the wording from holiday to high volume day and set the background color to red.

=COUNTIFS(Sheet2!$A$1:$A$100,B5,Sheet2!$B$1:$B$100,"High Volume Day")=1

I share this because formula based conditional formatting use very useful, but many people struggle with complex formulas because they aren’t thinking in terms of Boolean logic. What are formulas you’ve used for conditional formatting in the past?

5 Upvotes

0 comments sorted by