Sorting and filtering in Excel: how it works

Sorting puts the whole table in a different order, filtering hides rows for a while. Click in your table and use the buttons on the Data tab.

Start practising hereIn the lesson Sorting data, free

What's the difference between sorting and filtering?

Sorting puts the rows of a table in a different order, for example from the highest revenue to the lowest, or alphabetically by name. The whole row moves with it, so everything stays together.

Filtering temporarily hides the rows you don't want to see, without deleting or moving anything. You can tell by the row numbers that skip. If you remove the filter, everything is back.

When do you sort or filter?

You want the customers with the highest revenue at the top: sort by the Revenue column, from largest to smallest.
You want a list in alphabetical order by name: sort from A to Z.
You only want to look at the rows of one region: filter on that region.
You want a total of only what's visible after a filter: use =SUBTOTAL(9,C2:C100) instead of SUM.

Sorting and filtering, step by step

This is how to go about it:

Click one cell in your table. Don't select a single column, or Excel sorts only that column and every name ends up next to the wrong amount.
On the Data tab, choose Sort A to Z or Sort Z to A while you're in the column you want to sort by. Excel sorts the whole table.
If you want to sort by several columns, choose Data > Sort and add a level, such as first by region and then by revenue.
To filter, choose Data > Filter, or press Ctrl + Shift + L, or Cmd + Shift + F on a Mac. An arrow appears at the top of each column.
Click the arrow of a column and tick what you want to see. The other rows disappear for the time being.

The mistake you don't see

If you select just one column and choose 'Continue with the current selection', Excel only sorts that column. Every name then sits next to the wrong amount, without any error. Always choose 'Expand the selection', or simply click a single cell.

Sorting by several columns

Under Data > Sort you add levels, for example first by region and then by revenue. The second level only counts when the values in the first are the same.

Filtering

Ctrl + Shift + L turns the filter buttons on. Filtered rows are only hidden: you can tell from the row numbers that skip. Filter numbers with a number filter, so it stays right when rows are added.

Where it often goes wrong

An empty row in your table splits it in two: Excel then sorts only part of it.
SUM counts hidden rows. Use SUBTOTAL for what's visible.

Want to try it yourself?
Practise this in the lesson Sorting data. It's free.

Start practising here

Frequently asked questions

Why are my numbers in the wrong order?

Then they're stored as text and Excel sorts 10 before 9. Convert them with VALUE.