Have you ever wanted to grab every column header of a dataset in another part of your Excel workbook? Let’s take a look at what I mean, and how to do this:
Notice that the data has been loaded to an Excel table. This is necessary for the next steps to work.
Example: the FILTER() function
To demonstrate a use case here, let’s use the new FILTER() dynamic array function. Check out this post for a further tutorial.
I will write a formula here to get all the bills for the dinner timeslot:
=FILTER(tips, tips[time] = "Dinner")
Where are the column labels?
Now, this is a super-handy Excel function. But it’s a little confusing, because our filtered results aren’t actually labeled… so how can we even know what each column is measuring?
We could just copy-and-paste those labels above our FILTER()’d results, but what if a column is added or removed from the original data source? No, our solution should be more extensible than that.
This is where using tables is key.
Pulling column headers with structured table references
Perhaps you noticed something a little different in the above FILTER() function: rather than referring to cell locations, we referred to the parts of the table:
=FILTER(tips, tips[time] = "Dinner")There’s no A1:B23, etc. here. What gives?
This is known as a structured reference in Excel, and it’s one of the major reasons I love Excel tables. With structured references, we can pull any element of a table dynamically, including the headers:
=tips[#Headers]
This reference along with the results of FILTER() is totally dynamic; if we delete columns in tips everything adjusts:
I hope you see this is so much more powerful than hard-coding, and ultimately easier in the long run too.
Have you used structured references before? Where are you hard-coding in your workbook that you’d like to get rid of? Let me know in the comments.
Leave a Reply