Sort, Filter and Excel Tables

Once you have a list of data, you want to look at it in different ways: largest first, only one region, only this month. Sorting and filtering do this without changing the data itself. A useful partner is the Excel Table, a feature that turns a plain range into a smart list with automatic filters, formatting and formulas that grow as you add rows. Most professional workbooks use tables.

Sorting

1. Click any cell inside your data, for example in the Amount column.
2. Go to Data, Sort A to Z or Sort Z to A, or open Data, Sort for more options.
3. For a custom sort, such as Region and then Amount, open the Sort window, add a level for each column and choose the order.

Excel sorts the whole table together, so rows stay intact, as long as there are no empty columns or rows splitting the data, and the headings are recognised (tick “My data has headers”). The classic disaster is sorting one column alone. If you select a single column and sort only that, the other columns stay behind and the rows no longer match. If Excel asks, “Expand the selection”, choose that. The safest approach is to click inside the data and let Excel find the whole range, or use a Table.

To keep an original order, add a helper column with numbers 1, 2, 3 before you sort. You can always sort by it to return to the original order.

Filtering

Click inside the data and press Ctrl+Shift+L, or choose Data, Filter. Little arrows appear on each heading. Click one to see these options.

• Tick or untick values to show only those rows.
• Text Filters such as Begins with, Contains, Does not contain.
• Number Filters such as Greater Than, Between, Top 10.
• Date Filters such as This Month, Last Week or a range.
• Filter by Colour, if cells are coloured.

Row numbers turn blue, and the status bar says how many rows are found. Use more than one filter to narrow down: Region equals North, and Amount greater than 2000. To remove filters, press Ctrl+Shift+L again, or choose Clear. Filtering only hides rows. It does not delete them. Be careful with a total over a filtered range: SUM still adds the hidden rows. Use SUBTOTAL(109, G2:G11), which adds only visible rows.

Excel Tables

Click inside the sales data and press Ctrl+T. Confirm the range and tick “My table has headers”. The range turns into a Table with banded rows, filter arrows and a Table Design tab. Give it a name on that tab, for example Sales.

Why use a table?

• It grows. Type under the last row, and the table extends and copies the formulas. Charts, pivot tables and formulas that use the table update by themselves.
• Formulas fill down automatically. Type a formula once in a column, and it fills the whole column.
• Readable formulas. You can use column names instead of cell addresses.
• Filter and sort built in, with the headings visible while you scroll.
• Totals row. Table Design, Total Row adds a row with a function of your choice for each column.

Structured references

In a table named Sales, you can write:

=[@Qty]*[@Price]                       the Amount in each row
=SUM(Sales[Amount])                    the total of the Amount column
=SUMIF(Sales[Region], "North", Sales[Amount])

[@Qty] means the Qty in this row. Sales[Amount] means the whole Amount column of the table. The formulas read like English and do not need to change when you add rows. If your formula looks strange when you click cells, it is because Excel turns clicks into these names. This is normal.

Slicers: filter with buttons

With a cell in the table selected, choose Table Design, Insert Slicer and tick Region. A box with a button for each region appears. Click North, and the table shows only North. Hold Ctrl and click to select several. Slicers are easy for managers to use, and they also work with pivot tables (next lesson).

Advanced filter and dynamic arrays

Microsoft 365 and Excel 2021 have newer functions that do the work with a formula: =FILTER(Sales, Sales[Region]="North") returns only North rows, =SORT(...) sorts and =UNIQUE(...) lists the different values. They update automatically. Older versions do not have them, so in shared files check what your colleagues use.

Good habits for lists of data

• One heading row, no merged cells, no blank rows, and one kind of data in each column.
• Do not put totals in the middle of the data. Put them above, or on another sheet.
• Name your tables and sheets clearly.
• Use a Table for data that keeps growing.

Your task

1. Turn your sales data into a Table named Sales using Ctrl+T.
2. Sort by Region and then by Amount, largest first.
3. Filter to show only North rows with an amount of 2000 or more. Check the count in the status bar. It should show 2 of 10 records.
4. Add a total row, and a slicer for Region. Add a new row at the bottom, and watch the table grow and fill its formulas.

How to check it worked

• The table shows banded rows and a name Sales in Table Design.
• The North, 2000 or more filter shows two rows (Backpack 2800 and Pen pack 2400).
• A new row is added to the table and the Amount formula appears by itself.