Sorting, filtering and Excel Tables
Sort and filter lists safely, total only what you can see with SUBTOTAL, and turn data into an Excel Table that grows by itself.
In this lesson you will
- Sort without scrambling rows
- Filter data and clear filters
- Use SUBTOTAL for visible rows
- Create an Excel Table
About 14 minutes
Finding the answer in a long list
Imran runs a delivery business and his sheet now has 400 rows: one row per delivery with region, date, driver and amount. His boss asks, ‘Show me only the North region, biggest amounts first.’ Scrolling and squinting would take an hour. Sorting and filtering will take ten seconds. This lesson shows how to use them safely, and how Excel Tables make them even easier.
Sorting
- Click any single cell inside your data (do not select a single column).
- Go to Data, Sort.
- Choose the column to sort by and the order (A to Z, smallest to largest, newest to oldest).
- Tick My data has headers so the header row stays on top.
- Click OK.
Why click just one cell? Excel then sorts the entire connected table and keeps each row together. If you select only one column and sort, Excel may offer to expand the selection. Always choose to expand, or the other columns stay behind and your data gets scrambled, with each amount sitting next to the wrong region.
Sorting by more than one column
In the Sort dialog click Add Level. A multi-level sort first orders by one column and then breaks ties with the next. Sorting five deliveries by region (A to Z) and then by amount (largest first) puts them in this order. The list below was produced by a short program that does the same as Excel’s sort:
| Region | Amount |
|---|---|
| East | 150 |
| North | 200 |
| North | 120 |
| South | 80 |
| South | 60 |
Filtering
- Click inside your data and press Ctrl+Shift+L (or Data, Filter).
- Small arrows appear in the header cells.
- Click the arrow beside Region, untick Select All and tick only North.
- Excel hides every row that does not match. Row numbers turn blue to show a filter is active.
- Click the arrow again and choose Clear Filter to bring everything back.
Filtering hides rows, it does not delete them. You can combine filters on several columns, filter numbers by ‘greater than’, and filter dates by month or year.
Totals that respect the filter
A plain SUM adds every row, even hidden ones. To total only what you can see, use SUBTOTAL. A test sheet with amounts 120, 80, 200, 150 and 60 had three rows hidden. Both formulas were really recalculated:
| Formula | Result | Meaning |
|---|---|---|
| =SUBTOTAL(9,B2:B6) | 610 | Ignores filtered rows, counts rows hidden by hand |
| =SUBTOTAL(109,B2:B6) | 320 | Ignores every hidden row |
Because the three rows here were hidden by hand and not by a filter, SUBTOTAL(9) still counted them and SUBTOTAL(109) did not. With a normal filter, both skip the filtered-out rows. When in doubt, use 109.
Excel Tables
Select your data and press Ctrl+T to turn it into an Excel Table. Tick ‘My table has headers’. You get automatic banded rows, filter arrows on every header, formulas that fill down by themselves, and totals that grow when you add new rows. Give the table a name on the Table Design tab, such as Deliveries. Formulas can then say things like =SUM(Deliveries[Amount]), which reads almost like English. This pattern is shown for explanation; it needs a real Excel Table to run.
Two traps
- Dates stored as text sort wrongly. Sorted as text, the entries 10/1, 2/1 and 15/1 come out as 10/1, 15/1, 2/1, because text sorts character by character. Real dates sort in true date order. Fix with the checks in lesson 2.
- Blank rows inside the data make Excel treat the data as two tables, so sorting and filtering include only one part. Delete blank rows first.
Practice
Create a table of at least ten rows with a region or category, a date and an amount. Sort it by category then by amount, filter to a single category, and use SUBTOTAL(109) to total only the visible rows. Finally turn it into a Table with Ctrl+T and add a new row underneath to see the Table grow.
After adding a row directly under the Table, it should expand and copy its formatting and any column formulas. If it did not expand, there may be a blank row between the table and the new row. Any formula using the Table name will include the new row automatically.
Sorting a single selected column. The other columns do not move with it and rows get mismatched. Click one cell inside the table and let Excel sort the whole range.
Searching inside a long list
Press Ctrl+F to find a word or number anywhere on the sheet, and Ctrl+H to replace it. Use the Options button to search within the sheet or the whole workbook and to match the entire cell only. This is the quickest way to locate one customer or to fix a spelling you typed the wrong way many times. Be careful with Replace All, and try Find All first so you can see every cell it will change.
Removing duplicates safely
Duplicates creep into every list. Copy the sheet or save a backup first, then select the data and use Data, Remove Duplicates. Excel asks which columns must match for a row to count as a duplicate. Choosing too few columns can delete rows that were genuinely different, so think about what makes a row unique. A customer name alone is not unique, but a customer plus a date plus an amount usually is.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is the safest way to sort a table?
- Select one column only
- Delete the headers
- Click one cell inside and use Data, Sort
- Merge the cells
Excel then keeps rows together.
What does a filter do to the rows that do not match?
- Hides them
- Deletes them
- Moves them
- Colours them
The data is still there.
Which function totals only the visible rows?
- SUM
- COUNTA
- SUBTOTAL
- ROUND
109 ignores every hidden row.
What does Ctrl+T do?
- Turns data into an Excel Table
- Opens a new tab
- Types text
- Closes the file
Tables grow and keep formulas filled down.
Why do dates stored as text sort wrongly?
- Dates cannot be sorted
- Text sorts character by character
- Excel is broken
- Months have names
10/1 comes before 2/1 as text.
Summary
- Click one cell inside the data, then sort the whole table.
- Filters hide rows; they do not delete them.
- SUBTOTAL(109) totals visible rows only.
- Ctrl+T turns data into a growing Table.
Questions
Can I undo a sort?
Yes, press Ctrl+Z right away. If you have saved and closed, you cannot, so keep an order column (1, 2, 3 ...) to restore the original order.
Why is my filter missing some rows?
A blank row may split the data. Remove blank rows or select the whole range first.
Are Tables available in Google Sheets?
Newer versions of Sheets have tables too, but the steps and names differ. Filters and sorting work the same way.
