Reference a table

Referencing a table is a little different to referencing cells, so it is worth taking a look.

First off, if you are unfamiliar with tables, then learn more about them here.

When you write a formula inside a table, referencing other columns in the table, then Excel will use the column name, rather than the cell reference. The @ symbol, means that the reference is to the same row that the formula is in. If you select a cell in the table from a different row, then Excel will convert back to normal cell references.

If you use a formula outside the table to reference the table, you can still use the @ to access the same row. Notice that you now have the table name as part of the reference.

If you don’t include the @ symbol then you can reference the whole table column. Notice the blue line around the numbers. This is showing that the formula is spilling down into all of those cells. The formula is only in one cell, but the result of the formula spills down.

If you want to have an absolute reference to a table column then the way to do that is a bit clunky, but it is to repeat the column name. See the picture below to learn the layout required.

You can also reference the headings or total row of a table.

=TablePlayerList[[#Totals],[Account]] Will reference the value in the total row for the specified column.

=TablePlayerList[[#Headers],[Account]] Will return the column header value.

Tables are great and being able to refer to them properly makes them even more useful. Remember that by using a table, when more data is added, the table will grow automatically and if you have referenced the table, then your formula will also update to include the new data.