When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?
First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically.
Let’s look back at the sample we used in the previous two blog posts. In the following screen shot, you see that the formula used refers to a “fixed” range of $I$2:$I$9. It works totally fine given the current static situation.
But what if we are moving into May and are ready to input more “PublicHolidays” to the list?
Oh no… it didn’t work as the formula was hard-coded.
To fix that, we need to go into the formula of Conditional Formatting and revised the hard-coded range. (provided that you remember to do it; and/or your user knows how to do it) 🙂
That’s why we want to make the formula used in Conditional Formatting be dynamic!
There are two common ways to do dynamic ranges:
- Using OFFSET function
- Using Excel Table
Using Excel Table will be the technique discussed in this post. Indeed, whenever possible, I highly recommend you use Excel Table for dynamic range. Excel Table was introduced in Excel 2007. After a decade, it’s still one of the hidden gems to most regular Excel users. We should promote it more as it is really a cool feature of Excel. Indeed there are obvious reasons of using Excel Table over OFFSET for setting up dynamic range. Nevertheless that is not the topic of this post. 😛
To turn a range into Excel Table
- Select the range
- Go to Insert tab
- Click Table
- Check the range is correctly detected; and My table has headers is checked
Tip: Keyboard shortcut for inserting a Table: Ctrl+T
As a good practice, always name your Excel Table to something meaningful to you/users. In our example, it’s named as tb_PH.
Now, we have created an Excel Table named tb_PH. Let’s try two things:
First, let’s see what happens when
- we select tb_PH from Name Box; and
- after new data is added to tb_PH, repeat step 1 and see
wow…. tb_PH accepts new data appended. Now the same table name tb_PH refers to an expanded range. Isn’t it cool?
Now, try also this:
In any blank cell, input = then using mouse to select the range I2:I9
Note: For demonstration purpose, the range before adding data is used. If you have added new data to tb_PH, ensure you have selected the whole range to make Excel to turn the range into a Structure Reference automatically.
See?! Excel automatically turns the range selected into a Structured Reference:
Don’t be afraid of the new formula structure. It simply refers to the Table Name [ColumnHeader]. In other words, it means the (whole) column of PublicHolidays of the table tb_PH.
On the surface, you may know what I am trying to do next – Using the Structure Reference in the formula for conditional formatting:
Unfortunately, it did not work….
To test the formula, copy it and paste it to K2 and see:
It works. Somehow it does not work in conditional formatting…
To fix this, we need an INDIRECT approach in the formula set:
And the trick is… simply wrap the Structured Reference with INDIRECT. (Note: Don’t miss the double quotes)
As simple as this. 🙂
Let’s watch the result in action:
WOW… it works like a charm! 🙂
Tip: As there is only one column in the Excel Table, we may shorten the formula to:
Want to know about what COUNTIF does in the example? Please read the post Highlight weekends and holidays using Conditional Formatting in #Excel.
You may download a sample file to follow along. Remind you to unhide the sheet <Example (3)>.