Mastering formulas in data tables

Quick Tip

Using formulas in data table cells

Confused about cell references in data tables?

Cell references have a different syntax and structure in data tables, compared to ordinary cells.

Here’s how they work:

Structured references in tables

Cell references in tables use structured references*. This applies when referring to cells that are in a defined table, whether the cell containing the reference is either also within that table or outside it.

There are several benefits to using structured references, including:

• References automatically update when new rows are inserted into the table
• Can make it easier to read and troubleshoot where data comes from
• Can be faster to input or troubleshoot formulas containing structured references

See the tip Save time with data tables for more information about how, why and when to use data tables in Excel.

* You can also use normal (“explicit”) references when working with data in tables, but it is usually better to use structured references.

How structured references work

Structured references use a specific syntax that refers to data by the name of the column it is in, and the name of the table.

Remember, all data tables have column names, even if the column headings are hidden.

Structured references are best illustrated using examples.

In the following examples, we will use a data table that looks like this:

This example table is called tblSummary, and it has the following column headings:

• Name
• Office
• Location
• Reports
• Hours
• Average RPH

Reference a cell in the same row and table

This formula is used when you are performing a calculation somewhere within the same table as the original data.

Use the column name preceded by @, like this:

Don’t forget to surround the reference in square brackets.

Reference a cell in a specific table

This formula is used when you are performing a calculation outside a table in the same row, using a column somewhere within a table.

Precede the reference with the name of the table, like this:

Reference the total of all rows in a table column

You can use any aggregate function (e.g. COUNT, AVERAGE etc.).

Omit the @ from the reference, like this:

More syntax for references to tables

You can specify an aggregate function to use any of the following:

• Data rows only (the default if omitted, as above)
• Header row and data rows
• Data rows and total row
• All rows (header, data and total)

Header row and data rows

Data rows and total row

Header row, data rows and total row