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

OK

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.

2 Responses to The INDIRECT trick of using Structured Reference in Conditional Formatting

I believe you’ll also find that simply writing your conditional formula to refer to an entire column from a Table somehow enables that range reference to auto-magically grow as the Table grows without needing to use INDIRECT or other trickery. In fact, if your cursor is on a cell of that Table when you bring up the Conditional Formatting Rules Manager, then the dropdown to “Show formatting rules for:” will contain an option for “This Table” in the pick list.