Sort and filter in Excel, with practice exercises
Sorting puts a list in order, filtering hides every row you do not need right now. For example ranking sales from highest to lowest, or showing only the staff of one city.
In short: click anywhere in your table and press Ctrl+Shift+L. A filter arrow ▾ appears in every header. From there you can sort the whole table by that column or show only the values you pick.
Try it now
The sheet shows the sales of a small team. The filter arrows are already on. Click ▾ in the right header and pick the right sort order.
Task Sort the list so that the highest sales are at the top.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Region | Sales $ |
| 2 | Jana Weber | North | 4200 |
| 3 | Murat Kaya | South | 7800 |
| 4 | Lena Hoffmann | West | 5100 |
| 5 | Tom Schulz | East | 2900 |
| 6 | Sara Klein | North | 6400 |
| 7 | Ali Demir | South | 3500 |
| 8 | Mia Wagner | West | 5600 |
Where to find sort and filter
| Task | Where in Excel | Shortcut |
|---|---|---|
| Turn filter arrows on or off | Home > Sort & Filter > Filter, or Data > Filter | Ctrl+Shift+L |
| Sort by one column | ▾ in the header > Sort Smallest to Largest or Sort A to Z | |
| Sort by several columns | Home > Sort & Filter > Custom Sort | |
| Remove all filters | Home > Sort & Filter > Clear |
The filter arrows tell Excel where your table starts and ends. For that to work, the list needs one header row and no completely empty rows or columns in the middle. Excel then picks up the whole block by itself, and you only have to click one cell inside it.
One more: show a single city
This time you filter. Only the rows of one city should stay visible.
Task Filter the list so that only the employees from Hamburg are shown.
| A | B | C | |
|---|---|---|---|
| 1 | Name | City | Sales $ |
| 2 | Jana Weber | Berlin | 4200 |
| 3 | Murat Kaya | Hamburg | 7800 |
| 4 | Lena Hoffmann | Leipzig | 5100 |
| 5 | Tom Schulz | Hamburg | 2900 |
| 6 | Sara Klein | Berlin | 6400 |
| 7 | Ali Demir | Leipzig | 3500 |
| 8 | Mia Wagner | Hamburg | 5600 |
Filtered rows are hidden, not deleted. Excel shows that a filter is on in two ways: the arrow turns into a funnel icon, and the row numbers turn blue. The status bar at the bottom also tells you how many records were found.
Sorting by several levels
One sort key is often not enough. Say you want the cities from A to Z, and within each city the highest sales first. Open Home > Sort & Filter > Custom Sort. Under "Sort by" choose the City column, click Add Level, and under "Then by" choose Sales with the order "Largest to Smallest". Make sure My data has headers is ticked, otherwise the header row gets sorted into the data.
The top level always wins. A lower level only decides when two rows are equal on the levels above it.
Number filters and text filters
The filter menu holds more options above the list of values. Which ones you see depends on what the column contains.
- Number Filters: for example Greater Than, Between, Above Average or Top 10. Top 10 lets you choose top or bottom and how many, so a top 3 works too.
- Text Filters: for example Begins With, Contains or Equals. Handy for finding every customer with "Ltd" in the name.
- Date Filters: for example Last Month or This Year, without typing any dates.
Filters on several columns combine. Filter City to Hamburg and Sales to greater than 5000, and only Hamburg rows above 5000 remain.
Common mistakes
Sorting a single column
This one can wreck a file. If you select only the sales column and sort it, the numbers end up next to the wrong names. Excel catches this with a Sort Warning dialog. Always choose Expand the selection. The safest habit is to click a single cell in the table or to use the filter arrows.
An empty row inside the table
A blank row in the middle makes Excel treat everything below it as a separate table, so those rows are neither sorted nor filtered. Delete the blank row before you sort.
A forgotten filter
You work with a list that is still filtered and wonder where rows went. Watch for blue row numbers. Clear Filter From "City" in the filter menu removes the filter of one column, Home > Sort & Filter > Clear removes all of them.
Numbers stored as text
If numbers sort alphabetically, with 100 before 20, they are stored as text. A small green triangle in the cell corner gives it away. Click the warning icon next to it and choose Convert to Number.
Sort and filter in German Excel
| English | German |
|---|---|
| Home > Sort & Filter | Start > Sortieren und Filtern |
| Custom Sort | Benutzerdefiniertes Sortieren |
| Sort Smallest to Largest | Nach Größe sortieren (aufsteigend) |
| Sort A to Z | Von A bis Z sortieren |
| Number Filters / Text Filters | Zahlenfilter / Textfilter |
| Ctrl+Shift+L | Strg+Umschalt+L |
Looking ahead: the SORT and FILTER functions
Excel 2021 and Microsoft 365 added functions that return a sorted or filtered copy of your data somewhere else. The source table stays untouched and the result updates by itself. =SORT(A2:C8,3,-1) sorts by the third column, largest first, and =FILTER(A2:C8,B2:B8="Hamburg") returns only the Hamburg rows. In German Excel they are called SORTIEREN and FILTER. Excel 2019 and older do not have them, so there you stick with the filter arrows.
Frequently asked questions
Does SUM include filtered rows?
Yes. SUM adds hidden rows too. To add only the visible rows, use =SUBTOTAL(9,C2:C8). That is also what AutoSum inserts below a filtered list.
How do I get the original order back?
Right away with Ctrl+Z. Later it only works if the table has a column with a running number you can sort by again. Add one before you reorder a large list.
What is the shortcut on a Mac?
In Excel for Mac, Cmd+Shift+F turns the filter arrows on and off. The menus have the same names as on Windows.