Why aren’t you using Excel Tables?

Excel Tables (capital T) have been part of Excel since the 2007 edition. Given it is now well into adolescence, why is it still common to see spreadsheets holding and calculating data but not using Tables?

A quick internet search will reveal tonnes of articles about why you should use Excel tables.

Here is a really good one, for example.

However, in this post, I thought I would explore this from a different angle, to try to understand people’s reluctance to use this amazingly useful tool.

Reason 1: I don’t know what they are

I’d be willing to bet the most common reason that people do not use Excel Tables is because they’ve never been taught what they are. Most of us are to a large degree self-taught in Excel, and we learn from what we see other people using. Even when we do see someone using a Table, the advantages may not be immediately obvious.

It really doesn’t help that the control is to “Format as Table”, on the Home tab. You may be forgiven for thinking that this was a formatting function, rather than a way to organise your data.

Using an Excel Table is a way to tell Excel to link and structure the data that it sees. Excel does this in the following ways;

• creating a named range (by default “Table1”), which you can refer to in formulas.
• automatically expanding the Table to the right and to the bottom if you add more data.
• formatting your table to make it clear where the headers, start and end are.
• encouraging consistent formula use (although this can be overwritten) – if you enter a formula in one cell in a table it will copy it to the other rows in that column.

Turning your data into a Table allows you to do to a lot more fancy things, including outside of Excel. For example, you can combine with other data in Power Pivot or Power Query, or publish your table direct to Sharepoint so that others can view or edit.

The thing about Excel Tables is that you can do a lot of things in Excel without ever having to create a Table. So it’s possible to be a very active Excel user without having felt the need to use them. But in most cases, you won’t be doing them as safely and efficiently as you would be if you were using a Table.

Reason 2: I’m just doing something quick and it’s too much hassle

It really isn’t. If you have a cell highlighted, hit “Format as Table”. You will get a quick dialogue window that checks your table area, asks if you want headers and THAT’S IT.

If you don’t want your data to ever expand, then perhaps it isn’t worth the 2 seconds it takes to do this. But given all the other advantages of tables you might as well. Certainly, if you’re drawing graphs or creating pivot tables from the data, always turn it into a Table. That way, the graph or pivot table will automatically expand to take in new data.

Finally – if the Table is really upsetting you, it is possible to reverse it.

Reason 3: I don’t like the formatting of Excel Tables

This, combined with the previous point, was probably my main reason for not using Excel Tables initially. Some people love the banding (alternate stripes). Others find it a bit much. And while automatically adding filters can be cool, isn’t it annoying when the filter icon hides your headers?

Fear not – everything is customisable. Create a table in any format and then go into Table Design. (This is a new tab which appears whenever you are working inside a Table).

You can deselect the autofilter, and choose the plainest format (to the left of the one selected). The only way you’d know you’re in a Table is because of the small blue arrow in the bottom right.

Plus the Table Design Tab appears, of course.

The Table Design Tab is also where you can rename your Table, add Slicers and do all sorts of funky things. It’s well worth an explore.

Reason 4: Excel Tables make my formulas look weird / not behave properly

If you use formulas with data from a Table then you’ll see that your formulas look different.

In this example, if you select cell B3, type “*” and then cell C3, you get a “structured reference” (two to be precise, @number and @price) instead of =B3*C3.

Initially I found this off-putting, which is silly really. After all, if you have labelled your header row well, it is actually easier to read.

It gets a little bit more fiddly if you are writing formulas that reference a table.

In this somewhat artificial example, you have a Table called “Products”. You want to summarise the data underneath by month and sales rep, using SUMIFS. (Yes, I know, you could do this with slicers, or pivot tables!)

I use my standard way of creating a SUMIFS, that is to highlight the data in column C, then Criteria 1 is the data in column A, and then select cell A11. You can see that Excel defaults to Structured References instead of cell ranges. You can also see that the Structured Reference looks different from my first example. This is because the formula is outside of the Table, so it needs to reference the Table name. The syntax to reference a column is therefore (TableName[ColumnName]).

But what happens if I want to copy this formula across? I have made the last column reference absolute by including a $ sign before the A, and this means the formula will copy down into C12 accurately.

However, if I want to copy the formula across, this happens:

At first it looks good, because the formula reference “Apr” has become “May”. However, the next argument has also moved along to the right and “Sales Rep” has become “Product”, and is therefore returning nil. You could manually amend it back to “Sales Rep” but that will become time consuming.

You can’t fix Table references in the same way as cell references, by using the $ sign. However, there is a specific syntax to use, which is this:

This tells Excel that the first Structured Reference (Products[Apr]) should move along, but the second one (Products[Sales Rep]) should remain constant.

That isn’t particularly intuitive, I’ll admit.

However, the point is it can be done, it just requires understanding a different way to write references. In fact, you don’t even really need to understand the syntax. Just google what you’re trying to do and you’ll find someone else has already done it.

Want to know more?

I train teams in Excel and Power Query. Contact me to find out more.