Excel Formatted Named Tables: Love Them or Hate Them?

Do you love the formatted, named tables in Excel, and use them as much as possible? Or, do you hate tables, and avoid them at any cost? Here are some of the great things about Excel tables, and a few reasons to think twice about using them in your Excel files.

Create an Excel Table

First, if you’re not familiar with Excel tables, watch this short video that shows how to build one.

♥ Table is a Reliable Container

A formatted table is a perfect container for data.

It’s a solid range of cells, with one row of headings, data rows, and as many columns as you need.
Nothing can spill out of the sides, or fall off the bottom, or accidentally get left behind if you move the table.
And, if you add or delete rows or columns, the table automatically adjusts its size.

You can read all about Excel tables on my Contextures site, and that page has sample files to download.

♥ Each Table Has a Unique Name

Excel recognizes each formatted table as a unique, named object in your workbook.

You can use a table name in formulas and macros, or as the source for a pivot table.
Excel automatically knows where that table is, and how many rows and columns it has.
The unique table names make it easy to refer to your data, with confidence that it will all be included!

♥ Built-In Sort and Filter

Another great feature in formatted Excel tables is the drop down arrow in each heading cell.

Those drop down lists have commands that make it easy to sort and filter the table data.
You can even sort a column’s items by colour, if you need to!

♥ Formulas and Totals

Table make it easy to work with formulas. (assuming there’s a common formula in each column)

Make a change to one cell’s formula, and all the other formulas in that column update automatically.
Or, add a formula in any cell in a blank column, and all the other cells in that column get the same formula.

There’s also an built-in Totals row in each table.

You can show or hide that row by clicking a check box.
In the Totals row, create your own formulas, or use the built-in summary functions, like Sum, Average and Count.

This video shows how to work with the Totals row in a named Excel table.

♥ Tables Have Style

Built-in table styles make it easy to format each table’s appearance.

A default table style is applied automatically
Choose a different style, to give the table a completely different look.
Create custom table styles too, that use your brand’s colours and preferred fonts.

Or maybe you’d like your table dressed in your favourite tartan!

This video shows how to create an Excel table with a specific formatting style.

♥ Other Table Benefits

Those were a few of my favourite named Excel table benefits – things that make me love them, when I’m working on an Excel project.

Are there other table features that make you love working with them?

===================================

X The Dark Side of Excel Tables

Unfortunately, there are a few drawbacks to working with named Excel tables too.

I’ve listed a few of my top Excel table peeves below
What Excel table problems did I miss?

X No Lock for Column References

Here’s one annoying problem with named table – you can’t lock a column in a table reference.

For example, in this normal cell reference, column B is locked (absolute):

=$B4
Copy that formula to the right, and the reference to column B doesn’t change.

You can’t do that for a table column. For example, I’d like to lock the Qty column, and multiply by each year’s column:

=tblOrders[@Qty]*tblOrders[@2020]
If you copy that formula to the right, both column references change: =tblOrders[@2020]*tblOrders[@2021]

So, you have to use workarounds to lock the table references, which takes extra time and effort.

X Protected Sheet Problem

One of the key advantages of using Excel tables is that they expand automatically, when you add new data directly below the table.

That doesn’t work though, if the table is on a protected worksheet.

Even if the cells below the table are unlocked, the table won’t expand to include the new data.

That’s a serious limitation, if you’re sharing the workbook with other people, and you need to protect the sheets.

X No Moving Multiple Sheets

In Excel, you can select multiple sheets, and then move or copy them. That’s a quick way to get things done!

Excel won’t allow that though, if any of those sheets contain a named Excel table.
You’ll have to ungroup the sheets, then move any “table” sheets individually.

And who has time for that?

X No Custom Views

Do you remember Custom Views? That was a handy Excel feature, back in the olden days.

You could apply filters, hide rows and columns, and make other changes to a sheet, and save those settings as a Custom View.
Then, show a saved Custom View, print a quick report, and switch to a different Custom view. Ah, the good old days!

Now, none of that is possible, if there’s even one named Excel table in the workbook. Sigh.

X Heading Cell Text Only

And here’s one more named table limitation – the heading cells are text only.

You can’t use formulas, to create dynamic headings.
- Excel will automatically change your formula to a zero, or a numbered variation of that.
If you type year numbers, or any other number in the headings, those are changed to text numbers, instead of real numbers.
- That makes it harder to do lookups, based on those heading values.

More Excel Named Table Info

For more information on Excel named tables, go to the Excel Tables page on my Contextures site.

There are written steps, videos, and sample files for you to download.

And here are a few more Excel Table articles that I’ve posted, there on the Contextures blog:

— Excel Table Doesn’t Expand For New Data

— Compare Two Tables with a Simple Formula

— Last Row Incorrect With Excel Table

— Create an Excel Table With a Specific Style Applied