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:
[@Qty]is the Qty of this row. The @ means "this row".Orders[Qty]is the whole Qty column of the table called Orders.
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
The orders are already a table called Orders. In
E2, work out the first total: type=[@Qty][@[Unit price]]and press Enter. Or type=, clickC2, type, clickD2.Now total the column from outside the table. In
H1, type=SUM(Orders[Total]).Structured references work in every function. In
H2, total the chairs only:=SUMIFS(Orders[Total], Orders[Item], "Chair").One more: in
H3, find the largest order with=MAX(Orders[Total]).Now the reward. Add an order under the table:
SO-5008inA9,ChairinB9,10inC9and89inD9. Watch E9 and the three summaries.
Remember
[@Qty]is this row's Qty;[@[Unit price]]when the heading has a space.Orders[Total]is the whole column, however long it gets.- A formula typed into a table column fills the whole column.
- Click cells of a table while writing a formula and the references are written for you.
Orders[#All]includes the headings and totals;Orders[[#Headers],[Qty]]is one heading.
In the function reference: MAX, SUM, SUMIFS
Previous: Turn a range into a table · Next: The total row