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.

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:

"tblSummary" data table example
“tblSummary” data table example

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.

For example

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


Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.


Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.