Back in Excel 2007, Tables were added. That simple name hides a quite different and powerful Excel option that, in our view, Microsoft hasn’t explained very well.
Excel is often used for lists and Excel Tables make managing and extending those lists a lot easier. In fact, ‘Excel Lists’ was the name of the Tables predecessor in Excel. Word and PowerPoint have a ‘Tables’ feature but that’s quite different from Excel Tables.
Versions
Excel Tables are in Excel 2007, 2010, 2013 and 2016 for Windows, though the exact features will have changed over the decade.
Tables are also in Excel 2016 for Mac and seem much the same as Office 2016 for Windows.
Format as Table
You’ve probably seen Tables because they have a prominent place on the Home tab.
The label ‘Format as Table’ and the pull-down gallery gives the notion that Excel Tables are all about appearance/formatting. Microsoft also gives this impression in their description of Tables.
More than just appearances
In this article we’ll show you what’s useful in Excel Tables. We’ll leave aside the styles and galleries to show you the real benefits of tables.
Before Excel Tables
To understand Tables, let’s look at Excel life before they existed. Lists in Excel were a simple set of columns and rows.
You could add some structure to the list by clicking the Home | Sort and Filter | Filter button.
That made Row 1 into headers and little pull-down menus with sort and filter options.
The big problem with this was naming and keeping the data together.
If you added new rows to the list, you had to change any references to include the new rows.
For example, any formulas or refences with range A1:D5 would have to be changed to A1:D6 when you added a row.
Defining a named range helped, but you still needed to redefine the range each time the list was changed.
Excel Tables largely solve the naming and expansion issues. They make list sorting and filtering better plus a whole lot more.
What makes Excel Tables different
Tables are automatically Excel objects that you can refer to in formulas etc. They are automatically given names, starting with ‘Table1’, shown on the left of the Table Tools | Design ribbon.
But you can and should change the name to something more obvious.
Right next to the Table Name is the important Resize Table option but you may not ever need it because of the vital Excel Table trick, Automatic Resizing.
Automatic Resizing
Excel Tables automatically expand when you add new rows to the bottom! It’s hard to underestimate the importance and usefulness of this seemingly simple addition.
Here’s a simple example. A small table that we’ve formatted so the boundaries of the table are in blue outline.
Notice that the cells below the third entry (Vulcan) are NOT part of the table.
Now we start typing on the next line below the table and Lo! the table expands another row (see the blue boundary has dropped down a row). No change to the table settings is required, just typing another row is enough.
The same thing happens if you paste more rows to the bottom of the table.
Adding a column/field to a table just as simple. Here we’ve typed a new heading to the right of the existing headers.
As soon as Enter is pressed to complete the cell text, it becomes part of the Table.
Table Columns have names
Each column in a table has, in effect, a named range that you can use in formulas. They are officially called structured references because they have more options than normal Excel named ranges.
Use square brackets to signify the structured reference/range. Here’s the total, as a formula with [Kms Travelled] as the range to be totaled.
AutoComplete knows about these references, type an opening square bracket in a table formula and the list of possibles will appear.
Excel is pretty smart about using these names. If you select the data cells in a column, Excel will automatically use the name instead. Just like the table itself, the names will adjust when the table expands or contracts.
The names can be used outside the table itself, all you need to do is add the table name before the column name as in Munchausen[Kms Travelled]
Real Excel nerds can go crazy with the advanced naming options. You can use qualifiers like [#Headers] and [#All] to specify what you want use in the formula.
TableExample[[#Headers],[#Data],[Numbers]] will select both the Header and Data cells from the Numbers column in the table TableExample.
Confused?
Microsoft’s own explanation of an Excel Table is confusing, in fact quite circular.
“A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. ”
In other words, a table is a set of rows/columns formatted as a table. That doesn’t tell you anything. To put it another way:
“A house typically contains related rooms in a series of walls and floors that have been formatted as a house. “. Now you know that a house has rooms, walls and floors, but nothing more.
