Named Excel Tables: A very good idea

Excel has this construct they call and “Excel Table” which is about as helpful as a bicycle maker naming their bike model, “Mountain Bike.” But the Excel folks named these things “Excel Table” so in order to prevent confusion (as if) I try to always capitalize the T in “Table” to make it clear we’re talking about a specific thing, not just any table of data in Excel.

Excel Tables are the things we get when we click on “Format as Table” in Excel:

Format as an Excel Table

Figure 1: Format as an Excel Table

Although “Format as Table” is as misleading as the name because putting your data in an Excel Table does more than just change the formatting. It puts the data in an object that will help you considerably in the future, make your formulas easier to read, and keep you from shooting your toes off. So, despite the poor name choice using Excel Tables is a “Very Good Idea”.

Let’s look at a few of the benefits:

Easy to Read Formulas:

When you use Excel Tables you can build formulas that refer to other columns in the Table by name:
Excel Table

Figure 2: Excel Table

This is considerably easier to read and debug than cell references.

Auto Scaling References:

Not sure if “auto scaling” is the right phrase, but when you reference an entire column using a Table formula, that reference stays valid even if the table shrinks or grows later:
Excel Table Formula Column Ref

Figure 3: Excel Table Formula Column Ref

In addition to being easier to read, this is a HUGE maintenance benefit when you update your source data later. I’ve seen spreadsheets in the wild that used explicitly coded references to cells and the data was updated, adding more rows, and the formula only picked up the original number of cells. This produces wrong results but without any error or information to let you know there’s a problem. Excel Table formulas help prevent this.

Prettier Output:

I think many users initially adopt Excel Tables simply because they are more aesthetically pleasing to look at. While totally true, the visual benefits are, in my not-so-humble opinion outweighed by the functional benefits.

 
comments powered by Disqus