Course contents · Sort, filter and tables

Excel Tables

Open the practice in the trainer →

A table (Insert → Table or Home → Format as Table, Ctrl+T) is a range Excel treats as one whole:

Table1[Amount]the whole Amount column (no header) — =SUM(Table1[Amount]) includes new rows too
[@Amount]the Amount value in the same row — =[@Amount]*1.2
Table1all data of the table: =ROWS(Table1)

Rename a table on the Table Design tab → Table Name. Clear names (Orders, Prices) make formulas readable.

Tasks in the trainer

  1. Turn the data A1:D11 into a table.
  2. Add a calculated column: header "With VAT" in E1, formula =[@Amount]*1.2 in E2 — the column fills itself.
  3. Add an order in row 12: 1011, Owen, South, 2000. The table grows and the formula appears in E12 by itself.
  4. In G1 total the table column: =SUM(Table1[Amount]).

Solve the tasks in the trainer →

Check yourself

  1. What does [@Amount] mean in a formula inside a table?
  2. Why is =SUM(Table1[Amount]) better than =SUM(D2:D11)?