In the Home Ribbon, between the Conditional Formatting and Cells, you can find an often misunderstood button. It’s called Format as Table:
It’s not hard to assume that this button changes the range only visually. This illusion is often due to the button’s name itself: the word “Format” implies that the change is only visual.
The reality is very different. Once you format a set of cells (called a Range in Excel language) as a table, many things change, in addition to their appearance.
Creating a table
Start by creating a table and you will notice immediately that every column will have a header above it. Also, if you had duplicate column names, they will change automatically to be unique, as I illustrate in the screenshots below:
When you create a table, it also gets a name. Every table, just like every worksheet, must have a name. You can see it, and change it if you want, by clicking on the Table Design ribbon which appears once you create the table:
Let’s now start to see tables in action. The first important change is when composing formulas.
Formulas inside tables
Excel is very much about writing a formula once and applying it to many inputs. Challenges start when you need to go back and read your formulas again. To change them, to debug them, whatever. Then, suddenly, that A2*G2, which was obvious when you first entered the formula, is not so obvious anymore. The more complex a formula is, the worse it gets.
Tables change that completely. They enable easy references to columns using their headers (titles). And that’s the reason Excel was forcing them to be unique. This is a game changer because, as soon as you choose meaningful headers for your tables, your formulas become readable. Let’s see an example:
On the left, you have a simple formula calculating the total cost from the unit cost and the quantity. It’s an easy formula, but if the cells B2 and C3 didn’t happen to be right next to it, it could be hard to find them when writing the formula in the first place, and even harder to read the formula after some time.
Compose the same formula inside a Table and, instead of the usual coordinates, you get references using the table’s headers. This helps already, but it gets even better.
While typing a formula inside the table, start by typing =[@ and you will get a list of the table’s headers, from which you can choose the one you need:
Start typing and the list includes only the headers starting from what you type, which makes it even easier to select the right one:
References within tables
Let’s now see how we refer to other cells inside a table.
In the examples above, you probably noticed the @ sign. This has a special meaning inside a Table: it means you refer to a cell in the same row as the cell you are editing. Keep in mind that this extends even outside of the specific table you are editing, which can sometimes be handy, but can be dangerous too.
There are several ways to reference cells inside a table, and the main ones are:
- Cell references in the same row: they include the @ character in front of the header name. For example, the Unit Cost in the same row as the cell you are editing would be [@[Unit Cost]]
- Entire column references: they are the same as above, but they don’t include the @ sign. They refer to the the entire table column, excluding the header. Such a reference in the example above would be [Unit Cost], referring to the entire column under the header Unit Cost.
- Header references: their format is <table name>[[#Headers];[<header name>]]. In the example above, reference to the Quantity header cell would be Table1[[#Headers];[Quantity]]
- Entire table “body” references: simply the table name, like Table1 in the example above.
You can always refer to other cells or ranges by simply using the coordinates, of course then you start to lose the benefits of tables.
References to tables from ranges outside of them
There are a few other ways to refer to ranges in a table, which appear when you refer to a table from a range outside that table. To trigger that, you start by referring to the table name first as you type a formula. Excel helps you after the equal sign, by giving you valid choices of functions or tables:
To make it easier to choose from tables only, it is a good idea to prefix all your table names by an easy to remember character sequence. I usually prefix them by “tbl”.
After you select the table, Excel lets you select from valid ranges within the table. It will even provide you quick instructions to help you choose:
Column formulas
If you are not using tables yet, you are probably used to writing a formula once, then filling down or right. When using tables, filling down is the way to go, because each column has a title. But in reality, you don’t even need to fill down.
When you type a formula in an blank table column, Excel will automatically fill the entire column. Not only that, but if you then edit the formula in a single cell, it will apply the formula to all cells. You can override this if you need to but I highly recommend taking advantage of it. It may not always be what you wanted, but it will help you in the long run if your workbooks respect the “one column-one formula” rule.
Tables and Pivot Tables
If you happen to be a Pivot Table user who is not using Tables for your source data yet, you are missing a lot. If the source data for a Pivot Table is inside the table, you get quite a few benefits:
- The Table headers are immediately available as Pivot Table fields.
- Whenever you add rows or columns to the Table, the Table resizes automatically to include them (you can also disable this behaviour). And once this happens, next time you refresh the Pivot Table, it will include the additional data at once.
Tables and in-cell dropdowns (data validation)
In-cell dropdown lists are a very powerful Excel feature. When the valid value list is inside the table, you can get the list to adapt its size automatically when you add or remove data elements. You can read more about this use case here.
Tips when using tables
You will find Tables extremely helpful if you want to do complex calculations inside Excel. In this section I will present a few tips for such cases.
Tables for constants
Quite often, you want to apply certain constants to your formulas. This is the type of constants where you would otherwise use absolute references (dollar sign before both row and column reference). For such cases, I recommend using a table with two rows in total: the header row and a data row. Each column header is the name of the constant and underneath it the constant’s value:
When you refer to these values from other cells, make sure you use the full column reference (without the @ sign): for example table1[constant1]. In most cases you will get this type of reference by simply clicking on the value cell when editing a formula:
But be careful: if you edit a cell in the same row as the constant value, you can get a same-row reference, i.e. with an @ sign:
If this happens you can run into problems later. The formula will try to use the rows underneath, which no longer contain the constant value. You can prevent this by manually deleting the @ sign when such a condition occurs.
Lookups inside tables
Tables are simply amazing when you want to look up data inside them. The lookup formulas become much easier to compose and edit.
In this post I will concentrate in one of the most powerful ways to look up data and show you how to use it in a Table: the combination of the INDEX and MATCH formulas. Let’s try an example. In the following tables we store names and ID numbers. We start by looking up a name to get the ID number:
We start by specifying in the INDEX function from which column we want the data to be returned: Table2[ID]
Then we specify, inside MATCH, which value to search for (N4=Sue) and where to search for the value: Table2[Name]
The last parameter, zero, means that we want an exact match.
The beauty of this formula is that with one look you know where you will get the values from (ID) and what you are trying to match (Name). Sure you could use VLOOKUP as well, but it is much more obvious with the INDEX/MATCH combination. And it gets better. Check this example:
Now we do the inverse: we are looking up an ID to find the name. Notice how the table with our data remained exactly the same. This is simply not doable with VLOOKUP, because with VLOOKUP you must always search in the first column of a table.
The downside of Tables
Until now we demonstrated the goodness of tables. As with all good things, it also has its limitations, which we will examine in this last section.
The first limitation is that, because you can use each column title as a reference, they must all be unique within the same table. However, this is something you should probably be doing already, to distinguish between your data. So even though it is a limitation, it’s a mostly welcome one.
Another limitation is that you can never merge cells inside a table. But merged cells is another “feature” that you should avoid using as much as possible, because it breaks the continuity of cell references and sooner or later it will get in your way. Use merged cells only for “presentation” sections of your spreadsheets, sections which only need to be human-friendly. And in such cases you don’t really need to use Tables. Remember, Tables are not really about visual formatting.
There is only one real drawback of Tables, and that is speed. Because of all these good things that Tables bring, they add overhead to operations within the worksheet. Dealing with small tables is not an issue, but as your tables grow you can start noticing the performance penalty. Adding data at the bottom of a table, or adding a row, will start taking a few seconds, until the Table expands to include it. If you are into macros (VBA), you will find that whenever you deal with tables your code will run slower, even more so when you are writing to them. There are workarounds to that, but that will be the subject of another post.
Conclusion
Tables can be a life saver as your spreadsheets grow in complexity. They make your formulas easier to compose and edit and help you avoid errors with complex calculations. Next time you start building a spreadsheet in Excel, give it a try!