Excel Tables and structured references

Once data is clean, turn it into an Excel Table. Tables are one of the most useful and least used features in Excel. They make data easier to format, filter, extend and reference in formulas.

Creating a table

Click any cell in your data and press Ctrl and T on Windows, or use Insert, Table on any platform. Confirm the range and tick My table has headers. Excel applies banded formatting and adds filter buttons to the headers.

Then give the table a meaningful name. With a cell selected, go to the Table Design tab and change the name from Table1 to Sales. Names make formulas readable and are worth the few seconds.

Why tables help

• They grow automatically. Add a row directly below the table, and it becomes part of the table. Formulas, charts and PivotTables using the table include the new row.
• Formulas fill down by themselves. Type a formula in a new column, and Excel copies it to every row as a calculated column.
• Headers stay visible when you scroll, replacing column letters at the top.
• A total row is one tick away on the Table Design tab, with a drop down for sum, average, count and more for each column.
• Filtering and sorting are built in.

Structured references

In a table, formulas can refer to columns by name instead of cell ranges. In a new column called Net, type:

=[@Amount]-[@Discount]

[@Amount] means "the Amount value in this row". Outside the table, refer to whole columns like this:

=SUM(Sales[Amount])
=COUNTIFS(Sales[Region], "North")

Compare =SUM(Sales[Amount]) with =SUM(D2:D1048). The first tells you exactly what it adds and keeps working when rows are added. The second does not say what it adds and silently misses new rows beyond row 1048.

Things to know

• A table cannot contain merged cells or have two header rows, which is good for data quality.
• Spilling dynamic array formulas, covered later, cannot spill inside a table. Put them on a separate sheet or outside the table.
• If you need a plain range again, use Convert to Range on the Table Design tab.

Practice

Convert your cleaned sales data into a table named Sales, add a calculated column for net amount, turn on the total row and write a formula outside the table that totals net sales for the North region using structured references.