Hard-coded range expands automatically with #Excel Table

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:

excel tips - using structure reference in conditional formatting5

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:

excel tips - using structure reference in conditional formatting6

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

excel tip - using struture reference in conditional formatting

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)

excel tips - using structure reference in conditional formatting2

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:

excel tips - using structure reference in conditional formatting3

Well, I am almost convinced!

Wait… How about adding new columns?

excel tips - using structure reference in conditional formatting4

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.

excel tips - using structure reference in conditional formatting7

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.

Power BI Course

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

1 Response to Hard-coded range expands automatically with #Excel Table

  1. David N says:

    You are most welcome!

    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.