1.5 Tables, sorting and filtering

What you'll be able to do

Turn a plain range into a real Excel Table. Sort it on more than one column. Answer questions about the data by filtering it, without putting the data underneath at risk.

Before you start

Open the formatting practice workbook, the same file as the formatting topic. Its range is ready to become a Table.

The work

1. Same either way: select any cell inside the data and press Ctrl+T. The Create Table dialog opens. Check that "My table has headers" is ticked and press Enter. The range is now a Table, with striped rows, a filter menu on every heading, and formulas that grow with the data.
2. Same either way: type a formula in the first empty column beside the Table and press Enter. It fills the whole column at once, and rows you add later work themselves out. A reference like [@Amount] names the column instead of a cell address, which is why it survives sorting and new rows.
3. Same either way: to sort by one column, go to its heading and press Alt+Down Arrow to open the heading's menu. Choose Sort A to Z, or Sort Smallest to Largest. The wording follows the kind of data in the column.
4. With the mouse: go to Data → Sort and add a level for each column: state, then city, then amount. With the keyboard: press Alt+A, then S, then S. The same Sort dialog opens, and each level is a Sort by row inside it.
5. Same either way: to filter, press Alt+Down Arrow on a column heading. Move down to the list of values, clear the ones you do not want with Spacebar, and press Enter to apply. Rows that fail the filter are hidden, not deleted. Clear the filter from the same menu and they all come back.
6. Same either way: a plain range can have filters too. Select it and press Ctrl+Shift+L, which switches the filter menus on or off for the first row.
7. No documented keyboard shortcut for this — use the menu: to add a total row, go to Table Design → Total Row. Each cell of the new bottom row offers its own summary from a dropdown: sum, average or count.

Try it

Convert the practice workbook's range to a Table with Ctrl+T. Sort it by two columns using the Sort dialog. Filter one column down to two values and read what is left. Then clear the filter and add a total row showing the sum of the amount column.

Check yourself