Level 6 · Quiz: what an Excel Table does when a row arrives

A plain range of cells doesn’t know it’s a dataset. An Excel Table does: it knows its headers, its columns and its size, and everything pointed at it follows when it grows. For a log that gets new rows every day, that’s the difference between a report that updates and one that silently stops counting.

Making a Table

Click any cell in the data and press Ctrl+T (or Insert → Table). Check the range, keep My table has headers ticked, OK. Then, on the Table Design tab, give it a name in Table Name: Orders, not Table1. What you get:

• headers that replace the column letters when you scroll down, and filter buttons on every header;
• banded rows that stay banded after you sort or add rows;
• AutoExpansion: type in the row right under the table, or the column right next to it, and the table grows;
• calculated columns: write a formula once in a column and Excel fills the whole column, new rows included;
• a Total Row (Table Design → Total Row) with a drop-down for Sum, Average, Count, and so on. It uses SUBTOTAL, so it totals only the rows a filter leaves visible.

Structured references

Inside and outside the table, formulas can point at columns by name:

Reference	Means
Orders[amount]	the amount column, all data rows, whatever the size
[@amount]	the amount on this row (inside the table)
Orders[[#Totals],[amount]]	the Total Row cell of amount
Orders[#Headers]	the header row

=SUM(Orders[amount]) reads like a sentence and never needs stretching. Click a column while writing a formula and Excel inserts the structured reference for you. The browser grid in this course doesn’t support Tables or structured references, so the exercises use ordinary ranges. Everything else you learn here carries over.

Named ranges

For a single input cell or a fixed block, a named range does a similar job. Select K1, type TaxRate into the Name Box (left of the formula bar) and press Enter, or use Formulas → Define Name. Now =C2*(1+TaxRate) works anywhere in the workbook and never needs $: a name is always absolute. Formulas → Name Manager lists and edits them. Names are great for inputs (TaxRate, FreeShipFrom); for data that grows, a Table column is better, because a name defined as E2:E41 stays E2:E41.

Slicers

A slicer is a panel of buttons that filters a Table or PivotTable: Table Design → Insert Slicer, tick channel and category, and you get two button panels. Click web and the table shows only web rows; Ctrl+click (or the multi-select button) picks several; the clear-filter icon resets it. Several slicers combine with AND, like filters on different columns. Slicers are quicker to read than filter drop-downs, which is why they’re popular on simple dashboards, and one slicer can drive several PivotTables at once (Report Connections).

Common mistakes

• Leaving Table1 as the name: formulas read =SUM(Table1[amount]) and nobody knows what it is.
• A blank row before new data: the table doesn’t grow, and the new rows are outside every total.
• Overwriting one cell of a calculated column with a typed value. Excel flags the inconsistency with a green triangle; the column no longer means one thing.
• Mixing a fixed range like E2:E41 with a table that grows, as in Samuel’s sheet.