How to Filter in Excel: Arrows, Criteria and the FILTER Formula

A filter is the difference between scrolling through hundreds of rows and seeing exactly the ones you need. Only orders from one month, only leads marked "hot", only posts on one platform. Excel hides everything else for a moment, and nothing gets deleted or moved.

I'll cover the AutoFilter arrows most people mean when they say "filter", the extra power of number, text and date filters, how to clear it all again, and the FILTER formula that copies matching rows somewhere else. There's also one Find quirk that's worth knowing before you trust a filtered sheet.

Turning on filters in an Excel range

This is Microsoft's basic method, and it works on any list with headings in the top row.

1. Click any cell inside your data.
2. Go to the Data tab and select Filter.
3. Small arrows appear in each heading cell.
4. Select the arrow in the column you want to filter.
5. Untick (Select All), tick only the values you want to see, and select OK.

The rows that don't match are hidden, and the row numbers turn blue so you can tell a filter is on. Microsoft's shortcuts list adds a handy keyboard option: with a heading cell selected, Alt + Down arrow opens the filter menu.

Filtering an Excel table instead of a plain range

If your data is formatted as an Excel table, you can skip the Data tab. Microsoft says filter controls are added to the table headers automatically, so the arrows are already waiting for you.

Tables also expand as you add rows, so a filter keeps covering new entries. If you haven't set one up yet, my guide to turning a list into an Excel table takes a couple of minutes.

Using number, text and date filters in Excel

Ticking boxes is fine for a few values, but criteria filters are where filtering gets useful. In the filter menu you'll see Number Filters, Text Filters or Date Filters, depending on what the column holds.

1. Select the arrow in the column heading.
2. Point to Number Filters, Text Filters or Date Filters.
3. Choose a comparison, such as Between, Greater Than, Contains or Begins With.
4. Enter your criteria and select OK.

Column holds	Filter menu shows	Example use
Numbers	Number Filters	Orders over a certain quantity
Words and names	Text Filters	Names that contain "Ltd"
Dates	Date Filters	Everything from last week

Microsoft warns against mixing data types in one column. Excel only offers one type of filter per column, based on whichever type appears most. Its example: a column with three numbers and four text entries shows Text Filters.

Filtering by more than one column

You can filter several columns at once, and Microsoft describes filters as additive. Each new filter works on the rows already showing, so the list narrows step by step. Filter the Platform column to Instagram, then the Status column to Published, and you see only published Instagram posts.

One thing to note: once a column is filtered, the options in the other columns only show values from the rows still visible.

Clearing and reapplying filters in Excel

• One column: select the Filter button in that heading and choose Clear Filter From followed by the column name.
• Everything: click any cell in the data and select Filter on the Data tab again. Microsoft says this removes the filters from all columns and shows all your data.
• Refresh: if you've added, changed or deleted rows since filtering, reapply the filter to get up-to-date results.

You can tell which columns are filtered by the icon. A plain drop-down arrow means filtering is on but not applied, while a funnel-style Filter button means that column is filtered.

The FILTER function: copying matching rows with a formula

AutoFilter hides rows in place. The FILTER function does something different: it pulls matching rows into a new spot and updates when the data changes.

The pattern is =FILTER(array,include,[if_empty]). Microsoft's example is =FILTER(A5:D20,C5:C20=H2,""), which returns every row in A5:D20 where column C matches whatever is typed in H2, and shows nothing if there's no match.

• The results spill into the cells below and to the right, so leave that space empty.
• Microsoft says leaving out the if_empty part means an empty result shows a #CALC! error.
• It's handy for a summary sheet that always shows, say, open jobs for one client.

A filtering trap: Find only searches visible rows

This catches people out. Microsoft's help says that when you use the Find box on filtered data, only the rows on display are searched. If you can't find a customer you know is there, check for a filter and clear it before searching again.

The same goes for copying. When you copy filtered data, you're working with the visible subset, which is great for sending someone just their rows, but not if you meant to copy the whole list.

Filter ideas for small business spreadsheets

• Show only open jobs on a client tracker by filtering the Status column.
• Show one month of orders with a Date Filter.
• Show posts for one platform on a content calendar.
• Show leads from one source to see where enquiries come from.

If you want totals for each group rather than a filtered list, a PivotTable summary does that in a few clicks.

If Excel filters aren't working as expected

• Some rows never filter: there's a blank row in your data, so Excel thinks the list ended there. Delete the gap or select the full range before choosing Filter.
• Values missing from the list: Microsoft says only the first 10,000 unique entries appear in the filter window. Use a criteria filter instead.
• Wrong filter type offered: the column mixes numbers and text.
• Filter greyed out: the sheet may be protected, or you're editing a cell. Press Esc and try again.

Frequently asked questions

How do I add a filter in Excel?

Click any cell in your data, go to the Data tab and select Filter. Arrows appear in the heading row, and you choose what to show from each arrow.

How do I remove all filters in Excel?

Click a cell inside the data and select Filter on the Data tab again. Microsoft says this clears the filters from every column and shows all the data.

Can I filter by more than one column?

Yes. Microsoft describes filters as additive, so each extra column you filter narrows the rows that are already showing.

Why can't Excel find a value in my filtered sheet?

Microsoft says Find only searches the data that's displayed. Clear the filters, then search again.

What's the difference between a filter and the FILTER function?

A filter hides rows in place. The FILTER function, such as =FILTER(A5:D20,C5:C20=H2,""), copies matching rows into a new spot and updates as the data changes.