Table Objects vs Standard Ranges in Excel

I’m frequently asked why I prefer using table objects instead of standard ranges in Excel. Actually, I’m very much surprised when I see them underused in business environment. Let’s go through the benefits — and drawbacks — of table objects.

It’s very easy to visually distinguish a table object from a range. Take a look at the following screenshot:

A table object is on the left. A standard range is on the right. As they say, feel the difference.

Visual Differences

Let’s start with visual differences of a table object.

The visual benefits of a table object are:

• Automatic formatting of headers and borders
• Automatic filtering (hence, also sorting) buttons on headers
• Automatic row banding (and ability to also use column banding), which remains correct after adding or deleting rows in the table — this is especially important when you have wide tables and need to keep your eye on the same line while scrolling horizontally
• Ability to add automatic total row with several summary functions that can be entered with a single click
• In a long table, Excel’s column headers (A, B, C etc.) are automatically substituted by table object’s headers, so that when scrolling down, header titles are always visible:

There are only three visual drawbacks of a table object:

• You cannot use multi-line headers, which is a limitation for tables with complex structures — but there is a clear reason for that, which I’ll discuss when I come to functional differences
• Even if you have multi-line headers with the top header lines outside the table object, the headers of the table object cannot have identical names. See an example in the screenshot below. When you convert the range on the left to a table, Excel automatically appends numbers to headers to make them unique, as in the table object on the right, which may or may not be what you want (typically, it’s the latter). As a result, you’ll have to rename column headers to e.g. Client 1 ID and Client 1 Name and do the same for the second client, which makes the headers somewhat long

• The total row can appear only in the bottom of a table object. For long table objects, this might be a problem if you want to always see column’s totals

Functional Differences

These are even more important for us. For me, it’s actually the primary reason to use table objects and not standard ranges.

The main functional difference is in the way table contents are referenced. We’re using the so-called structured references in the case of table objects. For table objects, the following types of structured references exist:

	Current Row in Column	Whole Column
Unqualified	[@ColName]	[ColName]
Qualified	TableName[@ColName]	TableName[ColName]

Both unqualified and qualified structured references can be used inside a table object (in this case, unqualified references are, of course, preferable, as they take less space and are easier to read: there’s no real need to reference table name inside the same table). Outside a table, only qualified references can be used (which is logical, as there may be several table objects on a worksheet and Excel has to know somehow from which of these to take data).

This is exactly where table objects become handy. Compare two formulae:

=SUMIFS(A2:A250, B2:B250, F2, C2:C250, “>20”)

with

=SUMIFS([Total Cost], [Client ID], [@[Client ID]], [Volume Delivered], “>20”)

See the difference? The latter formula measures the grand total cost per client for deliveries exceeding 20 in volume. Is it as clear what the former one measures?

In the latter case, we’re referencing table’s column names instead of Excel’s column names (which are A, B, C etc.). It’s especially important for:

• Readability: It’s great if you work with your Excel workbooks alone. But what happens if you send it to someone else or, better, if you leave your job and have to hand over your files to a colleague? It’s much easier to understand what exactly the latter formula does than what the former one does.
• Debugging: Sometimes, we get strange results and have to understand the reasons for that. It’s much easier to see that you mixed Product ID with Client ID in the following example than it would be if you referenced a standard range:

=SUMIFS([Total Cost], [Product ID], [@[Client ID]], [Volume Delivered], “>20”)

By the way, recall the first two visual drawbacks of table objects. These are directly related to structured references. If you have more than one level of table headers, how does Excel know by column title to which header level you’re referring? In the current version, it has no idea. If you have two columns with identical names, how does Excel know which of them you’re trying to reference? Again, it has no idea.

Another important functional difference is in the way column contents are changed. In a standard range, if a cell is changed, it’s changed, nothing happens. If you have a formula, say, =F2 * 2 in cell G2 (and =F3 * 2 in G3 and so on in column G) and you realize that you want to change it to =F2 * 3, then you have to remember to copy the changed formula to all other cells in column G, because otherwise, you’ll have inconsistent formulae in different rows of a column.

In a table object, if all formulae in a column are identical, if the contents of one cell is changed, then the change is immediately propagated to all other cells in the column. The phrase put in italics above is very important: you can override this mechanism by changing the contents of one cell in a column and undoing the automatic propagation. But then, once the formulae in a column become inconsistent, the mechanism of automatic change propagation is stopped and you can restart it only by making the formulae in the column consistent again.

Summing up, use table objects: they are more visually appealing and more functionally advanced.