How to sort and filter data in Excel
To sort data in Excel, click a cell in the column, then choose Data > Sort A to Z or Sort Z to A. To filter, click Data > Filter, open the arrow on a column header and tick the values you want to see. Both change the sheet you are looking at. In Microsoft 365, the SORT and FILTER functions do the same in a new place and update when the data changes.
This guide covers the buttons first, then the formulas, then how to avoid the usual mistakes. The lessons at the end let you practice.
1. Sort with the ribbon
Click any cell in the column you want to sort by, then use Data > Sort A to Z or Z to A. Excel moves whole rows together, as long as the data is a solid block with a header row. For more than one level, such as region and then sales, choose Data > Sort, add a level for each column and set the order for each.
2. Filter with the arrows
Click a cell in the data and press Ctrl+Shift+L to switch on the filter arrows. Open an arrow to tick values, choose a text filter such as Contains, or a number filter such as Greater Than. Rows that do not match are hidden, not deleted, and the row numbers turn blue. The same shortcut switches the arrows off and shows every row again.
3. Sort with a formula
SORT takes the range, the column number to sort by and 1 for ascending or -1 for descending. It spills the result into the cells below and to the right, so those cells must be empty. The original data stays untouched.
Example: Sort by score, highest first
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Score | Sorted name | Sorted score | |
| 2 | Ana | 30 | Cy | 40 | |
| 3 | Ben | 10 | Ana | 30 | |
| 4 | Cy | 40 | Di | 20 | |
| 5 | Di | 20 | Ben | 10 |
The rows are ordered by the second column, from the highest score to the lowest.
4. Filter with a formula
FILTER takes the range to return and a test that gives TRUE or FALSE for each row. Only the rows with TRUE are returned. Combine two tests by multiplying them for AND, or adding them for OR.
Example: Names with a score of 20 or more
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Score | Passed | |
| 2 | Ana | 30 | Ana | |
| 3 | Ben | 10 | Cy | |
| 4 | Cy | 40 | Di | |
| 5 | Di | 20 |
Ana, Cy and Di have 20 or more, in their original order. Ben has 10 and is left out.
5. Put the two together
Nest one inside the other to filter and sort in one step: =SORT(FILTER(A2:B5,B2:B5>=20),2,-1) returns the rows with 20 or more, ordered from the highest. This is the usual way to build a live top list that needs no manual work.
Common mistakes
Sorting only one column. If you select a single column and choose to continue with the selection, the other columns stay in place and the data no longer matches.
Fix: Click one cell inside the data and let Excel select the whole block, or select all the columns.
Blank rows inside the data. Excel treats them as the end of the block and only sorts part of it.
Fix: Remove the blank rows, or turn the data into a table with Ctrl+T.
A #SPILL! error. Something sits in the cells where SORT or FILTER wants to put its result.
Fix: Clear the cells below and to the right of the formula.
Frequently asked questions
Does a filter delete the other rows?
No. They are only hidden, and clearing the filter shows them again. Be careful when you copy and delete visible rows, because that can touch hidden ones.
How do I sort by two columns?
Use Data > Sort and add a level for each column. With a formula, pass an array constant with the two column numbers to SORT.
What if my Excel does not have SORT and FILTER?
They need Microsoft 365 or Excel 2021 or later. In older versions, use the buttons, or an Advanced Filter.
Related functions
- SORTSORT returns a sorted copy of a range that updates when the data changes, ascending or descending, by any column.
- FILTERFILTER returns the rows of a range that meet a condition, and spills them into the cells below.
- UNIQUEUNIQUE returns each value of a list once, removing duplicates with a formula that updates by itself.
- LARGELARGE returns the k-th largest value in a list and SMALL the k-th smallest.
More on sorting and filtering
The guide covers the buttons and the SORT and FILTER functions. This piece adds custom sort orders, how to filter by color or by a rule, and how to copy only the visible results without the hidden rows.
A custom sort order
Alphabetical order is wrong for months, weekdays and levels such as Low, Medium and High. In Data > Sort, choose the column, then pick Custom List in the Order box. Excel already knows the months and the weekdays, and you can type your own list. The sort then follows the list, not the alphabet.
Another way is a helper column with a number for each level, sorted from smallest to largest. It works in every version of Excel, and it also works with a formula such as =SORT(A2:B20,2,1).
Filter by color or by a rule
A filter arrow can also filter by cell color or font color, which is useful when someone has highlighted rows by hand. Number filters accept Top 10, Above Average and Between, and text filters accept Begins With and Contains. Several columns can be filtered at once, and the result shows the rows that satisfy every filter, which is AND logic.
=FILTER(A2:C20,(B2:B20="North")*(C2:C20>=20))
=FILTER(A2:C20,(B2:B20="North")+(B2:B20="South"))Copy only what you see
When you copy a filtered range, Excel copies only the visible rows, which is usually what you want. Be careful with deleting: deleting the visible rows of a filtered range removes only those, but sorting or editing with hidden rows can surprise you. Clear the filter and check the row count before any deletion.
For a list of unique values from a filtered range, see UNIQUE. To go further, open [the sort and filter lesson](/learn/sort-and-filter) or [the dynamic arrays lesson](/learn/dynamic-arrays).