The INDIRECT trick of using Structured Reference in Conditional Formatting

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. excel tips - using table in conditional formatting1

But what if we are moving into May and are ready to input more “PublicHolidays” to the list?

excel tips - using table in conditional formatting2

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)  🙂

excel tips - using table in conditional formatting3

That’s why we want to make the formula used in Conditional Formatting be dynamic!

There are two common ways to do dynamic ranges:

  1. Using OFFSET function
  2. 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

  1. Select the range
  2. Go to Insert tab
  3. Click Table
  4. Check the range is correctly detected; and My table has headers is checked
  5. OK

excel tips - using table in conditional formatting4

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.

excel tips - using table in conditional formatting5

Now, we have created an Excel Table named tb_PH.  Let’s try two things:

First, let’s see what happens when

  1. we select tb_PH from Name Box; and
  2. after new data is added to tb_PH, repeat step 1 and see

excel tips - using table in conditional formatting7

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

excel tips - using table in conditional formatting6

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:

=tb_PH[PublicHolidays]

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:

=COUNTIF(tb_PH[PublicHolidays],$A2)

Unfortunately, it did not work….

excel tips - using table in conditional formatting6

To test the formula, copy it and paste it to K2 and see:

excel tips - using table in conditional formatting7

It works.  Somehow it does not work in conditional formatting…

To fix this, we need an INDIRECT approach in the formula set:

=COUNTIF(INDIRECT("tb_PH[PublicHolidays]"),$A2)

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:

excel tips - using table in conditional formatting8

WOW… it works like a charm!  🙂

Tip: As there is only one column in the Excel Table, we may shorten the formula to:

=COUNTIF(INDIRECT("tb_PH"),$A2)

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)>.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , , , . Bookmark the permalink.

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

  1. David N says:

    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.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.