Excel tables

Module 11 of 11 Spreadsheet in your browser

Hands-on drill What you'll do

Intro

Turn a plain list into an Excel table, with formulas that fill and grow by themselves.

Make the table, name it, then write formulas with its column names, like [@Price].

Tables

This lesson has you typing formulas, so it works best on a computer.

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

In a bit more detail

What is an Excel table?

A list Excel knows is a list. Click any cell in it and press Ctrl+T (Insert → Table): the first row becomes the header, the rows get stripes, and the whole thing gets a name, like Table1.
Why give the table a name?

Formulas can then use it. Table Design → Table Name: call it Orders, and =SUM(Orders[Total]) reads like a sentence. Table names have no spaces.
What does [@Price] mean?

"The Price in this row." Type = in a table, click the Price cell in the same row, and Excel writes [@Price] for you. =[@Price]*[@Qty] works in every row, so there are no cell names to get wrong and no $ to remember.
Why does the formula fill itself?

In a table, a formula typed into an empty column is copied down the whole column straight away: a calculated column. Change it in one row and Excel offers to change them all.
What happens when I add a row?

Type in the row right under the table and the table grows to take it in. The new row gets the calculated columns, and =SUM(Orders[Total]) includes it, with no ranges to stretch. That's the real reason to use tables.
Where else do tables help?

PivotTables, charts and XLOOKUP that point at a table keep up when it grows too. To turn a table back into plain cells, use Table Design → Convert to Range.

Know someone fighting with a spreadsheet? Send them this page.