How to Add a Filter in Excel and Narrow Down Any List

A filter is the fastest way to answer questions like which orders are still pending or which clients are in a particular town, without copying anything into a new sheet. You switch it on once, and every column heading gets a little arrow you can use to narrow the list. Here's how to add one, and how to stop it hiding things you didn't expect.

Turning on filter arrows for your list

1. Make sure your list has a header row, such as Client, Status and Order date, with no completely blank rows in the middle.
2. Click any single cell inside the list.
3. Go to the Data tab and select Filter.
4. Arrows appear in every header cell. Click the arrow on the Status column.
5. Untick (Select All), tick Pending, and select OK.

Only the pending orders stay on screen. The row numbers turn blue to remind you that some rows are hidden, and the arrow on that column changes to a funnel-style Filter icon.

Filtering by text, numbers or dates

Ticking boxes is fine for short lists, but the comparison filters do more. Microsoft describes choosing Text Filters or Number Filters from the arrow, then a comparison such as Between, and entering your criteria.

Column type	Filter menu	Example use
Names and words	Text Filters, Contains	Every client with Ltd in the name
Numbers	Number Filters, Greater Than	Orders with more than 5 items
Dates	Untick (Select All), tick the dates	Deliveries on chosen days
Any	Filter by Color	Rows you've highlighted yellow

Filters stack. Microsoft calls them additive: filter Status to Pending, then filter Town to Leeds, and you see only pending Leeds orders. Each new filter works on what's already showing.

Using a table instead of a plain range

If you press Ctrl+T and turn your list into an Excel table, Microsoft says the filter arrows are added to the headers automatically. Tables also grow when you add new rows, so the filter keeps covering the whole list. For anything you'll keep adding to, like an orders log, I'd always use a table.

The mixed data trap in filters

This is the catch. Microsoft warns against mixing data types, such as numbers and text, in one column, because Excel only offers one type of filter per column. If a column has three numbers and four entries stored as text, you'll only get Text Filters, and Greater Than disappears from the menu.

It happens all the time with quantity columns where someone has typed TBC or 3 boxes. Keep notes in a separate column and the numbers will filter as numbers. Dates behave the same way: if some are stored as text, they won't show up under Date Filters at all.

Find only searches what you can see

Another one Microsoft points out: when a filter is on, the Find dialog only searches the rows that are showing. If you search for a client name and Excel says it can't find it, check whether a filter is hiding that row. Clear all filters first and search again.

Clearing or removing a filter

• Clear one column: click that column's Filter icon and choose Clear Filter.
• Clear every filter but keep the arrows: Data tab, Clear.
• Remove the arrows completely: Data tab, select Filter again to switch it off.

When you add new rows or change values, the filter doesn't refresh on its own. Microsoft says reapplying the filter gives updated results, so use Reapply on the Data tab after you've edited the list.

Filtered rows versus hidden rows

Filtering hides rows temporarily; it doesn't delete anything. That matters when you total a column, because a normal SUM still counts the hidden rows. If you want totals that respect the filter, Microsoft says the SUBTOTAL function ignores any rows that aren't in the result of a filter. If you're splitting those totals between team members or orders, my guide to dividing in Excel covers the next step. And if you haven't converted your list yet, my guide to making a table in Excel explains why tables make filtering easier.

Copying or printing just the filtered rows

One of the handiest things about a filter is what you can do with the result. Microsoft says that once data is filtered, you can copy, edit, format, chart and print the subset without rearranging or moving it. So filter your orders to one courier, select the visible rows, copy and paste them into an email or a new sheet, and only the rows you can see come across. Print works the same way, which makes a quick daily pick list for whoever is packing boxes.

If you want to keep a filtered view for later, save a copy of the workbook or note which filters you used. Filters don't save as named views in a normal workbook, so whoever opens it next sees whatever was left switched on.

What I'd do

Turn your list into a table with Ctrl+T, then use the arrows to filter. Keep one type of data per column so the right filter options appear. And whenever a number or a search result looks wrong, glance at the row numbers: if they're blue, a filter is still on.

Frequently asked questions

What is the shortcut to add a filter in Excel?

Turning your list into a table with Ctrl+T adds filter arrows automatically. You can also use the Data tab, Filter.

Why are some filter options missing in Excel?

The column probably mixes numbers and text. Excel shows only one type of filter per column.

Does filtering delete rows in Excel?

No. It hides rows that don't match until you clear the filter.

How do I filter by more than one column?

Apply a filter to each column. Filters are additive, so each one narrows what's already showing.

Why doesn't my filter include new rows?

Reapply the filter from the Data tab, or use an Excel table so the filter range grows automatically.