1.5 Tables, sorting and filtering

Work through these

• Ctrl+T: what a Table gives you that a range does not

  Converting a range into a real Table gives you automatic formatting, filters, a name, and formulas that extend themselves as rows arrive. It is one keystroke of setup for benefits that last the life of the file.

• Structured references and formulas that grow with the data

  Formulas that refer to a Table use column names rather than letters, which makes them readable and makes them grow with the data. This is the feature that removes most range-maintenance work.

• Sorting on several keys; sorting a subset safely

  Sorting on several columns at once, and sorting part of a sheet without scrambling the rest, are the two operations that go wrong most often. The second one deserves particular care.

• Filters, slicers and the total row

  Filters hide rows that do not match, slicers make the same choice visible as buttons, and the total row summarises whatever remains visible. Together they turn a table into something you can interrogate.

Practice file

• xl-02-formatting-and-tables.xlsx

  An ugly, unformatted range to format, convert to a Table, sort and filter.

Opens in Excel. Every name, number and address in these files is made up. No account needed to download.