Course / Tables

Formulas that read a table by name

Write formulas with column names instead of addresses: [@Qty] for this row, Orders[Total] for a whole column. They keep up when the table grows. Intermediate · 7 min · Structured reference, [@Column], Table[Column], Calculated column

Inside a table, formulas can use the headings instead of addresses. These are called structured references, and there are two you will use all the time:

A heading with a space in it takes its own brackets after the @: [@[Unit price]].

Two things make them worth learning. A formula such as =[@Qty]*[@[Unit price]] can be read without looking anything up. And =SUM(Orders[Total]) has no last row in it, so it is still right after rows are added.

You do not have to type them. Click a cell of the table while writing a formula and Excel writes the reference for you.

Steps

  1. The orders are already a table called Orders. In E2, work out the first total: type =[@Qty][@[Unit price]] and press Enter. Or type =, click C2, type , click D2.

  2. Now total the column from outside the table. In H1, type =SUM(Orders[Total]).

  3. Structured references work in every function. In H2, total the chairs only: =SUMIFS(Orders[Total], Orders[Item], "Chair").

  4. One more: in H3, find the largest order with =MAX(Orders[Total]).

  5. Now the reward. Add an order under the table: SO-5008 in A9, Chair in B9, 10 in C9 and 89 in D9. Watch E9 and the three summaries.

Remember

In the function reference: MAX, SUM, SUMIFS

Previous: Turn a range into a table · Next: The total row