What are Excel Tables and why you should use them

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

img 5a53b7a8151bb - What are Excel Tables and why you should use them

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.

img 5a53b7cdd046f - What are Excel Tables and why you should use them

You could add some structure to the list by clicking the Home | Sort and Filter | Filter button.

img 5a53b7da5ac40 - What are Excel Tables and why you should use them

That made Row 1 into headers and little pull-down menus with sort and filter options.

img 5a53b7e9aeaef - What are Excel Tables and why you should use them

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.

img 5a53b8073e586 - What are Excel Tables and why you should use them

But you can and should change the name to something more obvious.

what are excel tables and why you should use them microsoft excel 16299 - What are Excel Tables and why you should use them

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.

img 5a53b8b6d4d5f - What are Excel Tables and why you should use them

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.

img 5a53b8cacf1db - What are Excel Tables and why you should use them

The same thing happens if you paste more rows to the bottom of the table.

img 5a53b8d95b110 - What are Excel Tables and why you should use them

Adding a column/field to a table just as simple.  Here we’ve typed a new heading to the right of the existing headers.

what are excel tables and why you should use them 16294 - What are Excel Tables and why you should use them

As soon as Enter is pressed to complete the cell text, it becomes part of the Table.

img 5a53b9039b904 - What are Excel Tables and why you should use them

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.

img 5a53b92d3a237 - What are Excel Tables and why you should use them

AutoComplete knows about these references, type an opening square bracket in a table formula and the list of possibles will appear.

img 5a53b9555a0b6 - What are Excel Tables and why you should use them

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.

subs profile e1563205311409 - What are Excel Tables and why you should use them
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address