How to Filter Data in Excel (and Clear Filters Again)

A spreadsheet with thousands of rows is hard to read. Filters let you hide everything you don't need for now, like only showing orders from one customer or sales from this month, without deleting a thing.

1. Turn on filters

1. Click any cell inside your data. Make sure it has a heading row.
2. Press Ctrl + Shift + L, or go to Data > Filter.

A small drop-down arrow appears in every heading. On a Mac, the shortcut is Command + Shift + F.

2. Filter to show certain items

1. Click the arrow in the column you want to filter.
2. Untick (Select All) to clear every box.
3. Tick only the items you want to see.
4. Click OK.

The rows that don't match are hidden. The row numbers turn blue, and the arrow changes to a funnel icon, so you can tell a filter is on.

Tip: with a long list, type in the Search box at the top of the filter menu. It narrows the list as you type.

3. Filter numbers, dates and text

The filter menu changes depending on what's in the column:

• Number Filters: Greater Than, Less Than, Between, Top 10, Above Average.
• Date Filters: Today, This Week, Last Month, Next Quarter, This Year and more.
• Text Filters: Contains, Begins With, Ends With, Does Not Contain.

For example, to show orders over £500: click the arrow on the Amount column, choose Number Filters > Greater Than, type 500 and click OK.

4. Filter by colour

If some cells are highlighted, click the arrow and choose Filter by Colour. Pick a fill or font colour to show only those rows.

5. Filter more than one column

Filters stack. Filter the Region column to North, then filter the Product column to Laptops, and you'll see only laptop sales in the North.

6. Clear filters

• To clear one column, click its funnel icon and choose Clear Filter From "…".
• To clear every filter but keep the arrows, go to Data > Clear.
• To remove filters completely, press Ctrl + Shift + L again.

Remember: filters only hide rows. Nothing is ever deleted.

Add up only the visible rows

A normal SUM adds hidden rows too. To get a total that changes as you filter, use:

=SUBTOTAL(9, C2:C500)

The 9 means "sum". Use 1 for average or 3 to count. If you click AutoSum underneath filtered data, Excel writes a SUBTOTAL for you automatically.

Copy only the filtered rows

Select the visible data and copy it as normal. Excel only copies what you can see. If it ever copies hidden rows too, press Alt + ; before copying to select visible cells only.

Troubleshooting

Some rows aren't included in the filter. There's a blank row in your data, and Excel thinks the data stops there. Remove the blank row, or select the whole range before turning on the filter.

The filter is greyed out. You might be editing a cell (press Esc), the sheet may be protected, or several sheets are grouped. Right-click a sheet tab and choose Ungroup Sheets.

New data isn't being filtered. Reapply the filter with Data > Reapply, or turn your data into a Table so the range grows automatically.

Wrapping up

Ctrl + Shift + L switches filters on and off. Pick what you want to see, use SUBTOTAL for totals that follow your filter, and use Data > Clear to bring everything back.