The INDIRECT trick of using Structured Reference in Conditional Formatting

Is it a feature or a bug?

This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional Formatting, to fix the problem we face demonstrated below:

David N gave a magical tip to handle that DIRECTLY.  Here’s his comment:

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…

p.s. I like the term “auto-magically” a lot! 🙂

You may download sample file to follow along.

Let’s watch it in action:

Key notes:

• Once the range I1:I9 is turned into Excel Table
• The corresponding range set in Conditional Formatting expands automaticallyMAGICALLY

Isn’t it a DIRECT approach to deal with the situation we discussed before?

Simply awesome!

BUT WAIT… let’s explore it a bit more to see what’s happening behind the sense.

In the screenshot below, the range A2:C7 is turned into Excel Table named “tb_data“.

In G2 and G3, two different formula are input.  You can see that the only difference is the use of Structure Reference (tb_data) and normal cell reference ($A$2:$C$7) in the second arguments (table_array) of VLOOKUP.

What would you expect when new data is appended to tb_data?

When new data is added to the Table tb_data and the lookup value is changed to “H”, you may expect the first formula works; while the second formula doesn’t… Right?  It’s our common understanding of using Structure Reference for its capability of auto-expansion; and the limitation of using “hard-coded” range…

Let’s watch:

(Please pay close attention to the changes in the formula)

Both formula works!  Surprisingly, the “hard-coded” range $A$2:$C$7 expands to $A$2:$C$11 automatically.  This behavior blows my mind.

So I change the range of the Table manually to further examine:

Well, I am almost convinced!

Wait… How about adding new columns?

oooops… the column reference does NOT expand as expected, although the row reference moves with it.

One more interesting behavior.  When I shrink the table area from three columns to two columns, the “hard-coded” reference becomes static again.

Somehow, the Magic is gone…

🤔

To recap:

When a range is turned into Excel Table , the range used in formulas previously input will automatically expand when new data is appended vertically, but not horizontally.  In most situations, this magic should work like a charm… but

the magic could be lost when the Table is shrunk horizontally.

That’s why I doubt…

Is it a feature or a bug?

Although it is very tempted to deploy this magical trick, I would strongly suggest we stick to the basic rule: using proper Structure Reference for dynamic ranges.

Thanks David again for the inspiration of this post.