Excel Tables — Overlooked, Yet Awesome

Tables in Microsoft Excel are one of those features that you can be totally unaware of and get along without just fine. But, once you stumble across them, you wonder where they’ve been all of your R1C1 life! In a nutshell, they take some of the niftier aspects of named ranges and pivot tables and make the Excel user’s life a lot easier in a number of situations.

Chandoo wrote a great post several years ago that explained the basics of Excel tables and provided a number of tips and tricks related to them. I’m going to try to not be overly redundant with his post, but there are a few other points and references worth making, so here we go!

What Is an Excel Table?

Unlike a pivot table, a straight-up table doesn’t “pivot” any of the data. It’s just a flat set of rows and columns. Imagine we have a simple table of data:

If we click anywhere in this table of data and then select Insert » Table, the data is converted to an Excel table:

Whup-dee-do, right? It now has banded columns. Well, yes, and, as you might expect, you can change the style of the table, whether or not you want banded columns, etc.. That’s all covered in Chandoo’s post.

More importantly, though, that range of cells has become a named entity that has some very nifty capabilities. Onto the niftiness…

In the Name of a Table…

In the non-table set of data — the first image above — we certainly could have defined the range of cells A1:D7 as a named range and then used that named range in various formulas. By making the set of cells a table, though, this range of cells automatically became addressable by name.

In the Table Tools » Design » Properties ribbon, you can see the table was automatically named Table1.

Unlike with named ranges, where you have to open the Name Manager to change the name of a range, you can simply update the table name right there in the box (you can also rename it in the Name Manager). Let’s do that and call it “Fruit_Table:”

If you’re a heavy user of named cells and named ranges, you will know how convenient and useful this is. If not…well, trust me!

Calculated Cells that Auto-Extend to be Calculated Columns

In the non-table set of data — the first image in this post — we calculated the Total Fruit value as a two-step process. First, we entered the following formula in cell D2:

=B2+C2

Then, as a second step, we double-clicked on the little box at the lower right of cell D2 to auto-copy that formula down to the other rows in the data set. Two steps.

With a table, the same formula looks a lot messier, but the messiness gets put in by Excel if you click on the different cells as you build the formula:

=Fruit_Table[[#This Row],[Apples]]+Fruit_Table[[#This Row],[Oranges]]

Here’s the key, though: you can build this formula in any of the rows in Column D, and it will automatically fill in to all of the other rows. That may not seem all that handy in this simplistic example, but it saves scrolling and checking when you’ve got a table that has several thousand rows. And, it comes in very handy if you have multiple calculated columns and then get to the auto-expanding of the table, which, conveniently, is the next thing we’ll cover in this post.

Auto-Expansion

It’s really common to need to update an Excel spreadsheet with new data. In my world, that’s generally because time has passed, and I need to add data for the dates since the last time I used the spreadsheet.

In our example, suppose I had a separate file with some more recent data:

I copied the highlighted portion and pasted it directly below the table I had created — in cell A8. When I pasted it, the table automatically expanded to include the new rows in the table and went ahead and extended the Total Fruit cell calculation. The image belows shows the table immediately following the Paste action:

Very convenient with large tables and large data additions!

Referencing Tables and Parts of Tables

I’m not going to go into great depth with all of the ways tables can be “looked into” from outside the table, but the possibilities are fairly endless.

I wrote a post over a year ago on how to “do Excel dropdowns right” using data validation. That post needs to be completely overhauled (and shortened) thanks to a comment that Alex Lush made pointing out that Excel tables would work well to address the issues I was trying to address. In the example used in this post, I could make a dropdown that always had the list of all of the date values in the data table using this data validation formula (the need to use “INDIRECT” is a little quirk of Excel and data validation — typically, you can refer to sub-ranges of data in a table without the need for that):

I could make a separate dropdown of all of the header values just as easily:

Let’s actually create those dropdowns, name the cells where they exist, and then show how some clever (but really quite straightforward) use of INDEX, MATCH, and tables nomenclature yields an interactive lookup tool:

The shaded cells are dropdowns based on the data validation configurations described earlier. The formula in the “result” cell is this:

=INDEX(Fruit_Table,MATCH(DateSelect,Fruit_Table[Date],0),MATCH(ValueSelect,Fruit_Table[#Headers],0))

I know it looks a little intimidating, but it is really pretty straightforward. In pseudo-formula terms:

1. We’re going to get the value in an array of data (that’s what the INDEX formula does)
2. Start by looking at the main table of data: Fruit_Table
3. Find which row in the table has the date that has been selected: MATCH(DateSelect,Fruit_Table[Date],0)
4. Then, go over to the column that contains the specific value that has been selected from the Value dropdown: MATCH(ValueSelect,Fruit_Table[#Headers],0)
5. Return that value

Pretty neat, huh? I could endlessly add new data for apples and oranges to the table over time. I could even add another column — for, say, peaches. Both the Date and Value dropdowns would automatically update with the full set of available values. And the Result cell would continue to return the appropriate value from the table. You can download a copy of the spreadsheet with this example here and play around with it.

This is a simple example, but you can imagine how it can be expanded to be ranges of values that get charted — similar to what is described in the most popular post on this blog: Excel Dynamic Named Ranges = Never Manually Updating Your Charts. But, that’s another blog post overhaul for another day!

The Trick for the Table-Referencing Syntax

There is no great trick for remembering the specifics of how to reference different aspects of a table. 🙂

One approach is to reference the Microsoft documentation on the subject. As their documentation is wont to be, it manages to be a bit unclear, somewhat useful, and organized only semi-logically. But, it’s there.

You can also sniff out how Excel references table components by starting to enter a formula in a cell with an “=” and then pointing to the entire column, row, header, etc. that you are trying to reference. I got the following value populated by hovering just above the word “Apple” until a down arrow appeared. When I clicked on it, the entire column lit up, and the value in the cell showed me how to reference that column:

This works for selecting other aspects of the table as well. You don’t actually need to return/enter the formula — just use the value populated in the larger formula you are building out.

Endless Possibilities

While perhaps not quite as life-changing as the discovery of pivot tables (I never claimed to have much of a life), Excel tables are intriguing, fun, and useful! Try them out!