You think you know what an Excel Table is, but you don’t

Have you ever used Excel Tables? ‘Of course I have’, you may think to yourself, but I’m here to prove you wrong. You probably believe an ‘Excel Table’ is, well, a table in Excel, like… a spreadsheet. But there’s a actually a specific tool in Excel called ‘Table’, and it is magnificently usable.

This is what an Excel Table looks like:

It has filtered columns by default and a little marker in the bottom-right corner, and when you select any cell in it, a special menu becomes available.

The way to turn any bunch of data into a Table is to select all of it and type Ctrl+T. You can assign your table a name – which will handily be used to refer to cells in your Table – and a desired colour scheme (which can be just black and white, no lines, as in the above screenshot). It doesn’t look like much, but it has a host of features that make life easier if you work in Excel a lot. And a couple of annoyances, too.

Tables make changing and updating your data easy

The first benefit of using Excel Tables over just entering data into the sheet willy-nilly is that when you inevitably change your mind about things, you can do it with a couple of clicks and not disturb the rest of your sheet. For example, if you want to add a column between existing columns, you would normally have to add a column to the entire sheet, which means everything below your table will get a new column too. Conversely, with a Table, nothing else moves!

Here I have a sheet with two Tables – one about groceries and one about colours. Notice I picked the default Table theme for the second Table.

I now want to add a column to the groceries table. I right-click on one of the existing columns and select Insert > Table Columns…

And here we are – notice that the second Table is unchanged:

I can also easily drag-and-drop columns by selecting the whole column. This can be done by hovering over the desired heading until a downwards arrow appears, and clicking once, then grabbing onto the selected column and dragging:

So satisfying. This is mostly just added ease, but there is another feature related to updating Tables that could make the difference between getting the right number and the wrong number. Say I am collecting data on ICOs to find out how much money in total has been raised through them. I’m using the sum formula which looks something like:

=SUM(A1:A100)

If I then discover that there is a new ICO that I should include, I have to remember to update the formula to say that it should now take cell A101 into account, too. But not with Tables! With a Table, to add more data, you simply select the bottom-right-most cell, and hit Tab. That will make a new row appear, and the little marker will move to the corner of the new row:

What about calculating the total? With a Table, my SUM formula now looks like this – clean, descriptive and self-updating:

Because the formula doesn’t reference specific cells, but the entire column in the Table, it will automatically update when the Table changes. Here you can see what happens when I add the new ICO row:

Formatting, cleansing and totals in one go

Formatting data can also be a pain if you are still adding to it – you have to manually copy the formatting from the previous row and paste it on. This is another aspect of working in Excel that Tables automate. Most usefully, they will paste a formula from the first row into all following rows automatically. For example, if I decided that I wanted my cryptocurrencies Table to display the funds raised in GBP as well as USD, I could create a new column for the GBP amounts (column D below) and enter the formula into the first cell. Notice the handy non-cell-specific reference again:

When I hit enter in cell D3, the rest of the column will be filled in as if by magic:

Lastly, Tables have a couple of nifty shortcuts to things you could also do without Tables. One is the ability to remove duplicates with one click. The other is adding a final row to your table that can show a number of different statistics – total sum, average, median, count and so on. They appear at the bottom, if you choose to include a final row, and can be changed using a drop-down menu. In this example I’m thinking it would be neat to see what the average sum raised through an ICO is:

Drawbacks

The drawbacks I have found so far when working with Tables are few, but pretty annoying. I still much prefer using Tables to not using them, but it’s good to be aware of these issues:

1. The default colour scheme is always applied and it is cumbersome to change. There is a way to set a different colour scheme as default, but it only applies to that worksheet, so you have to do it every time you create a new file.
2. Sometimes features are randomly disabled when there are multiple Tables in the same sheet. This does not seem to be a consistent issue, but I have got my share of error messages when trying to manipulate Tables that are placed next to other Tables. For example, I have had Tables where I could only add a row below or under an existing row (not both), or not at all. I find this a particularly unhelpful error message when that happens:

You would quite like to know which Table is causing the issue, but that information is apparently on a need-to-know basis. This problem seems to arise more with sheets that were originally created without Tables and where data was later converted into a Table, but I can’t be sure.

In summary: if you work in Excel a lot, do yourself a favour and try out Tables!

I discovered the life-changing magic of Excel Tables through Macquarie University‘s Excel Skills for Business: Intermediate II course on Coursera.