Mastering formulas in data tables
Use the power of data table formulas to work faster and more efficiently. See how cell references in data tables are structured, and how you can save time when using formulas in data tables.
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:
- 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.
In the example, the last column “Average RPH” (Reports Per Hour), contains this formula:
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
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication