What filtering does and when to use it

A filter in Excel hides rows that don't match what you're looking for, so you see only the data that matters. If you have a spreadsheet with 500 customer records and you want to see only those from California, a filter shows you just those rows and hides the rest. The hidden rows are still there — filtering doesn't delete anything — but they're out of your way.

Filtering is different from sorting. Sorting rearranges your rows in a new order (alphabetical, by date, by size). Filtering removes rows from view based on what's in a specific column. You'll use filtering when you need to focus on a subset of your data without changing the original spreadsheet, or when you're looking for patterns in one part of your data.

Excel's filter feature works on any rectangular block of data with a header row — the first row that names each column. Once you turn on filtering, a small dropdown arrow appears in each header cell, and you click those arrows to choose which values to show or hide.

Key Takeaways

  • Click any cell in your data, then go to the Data menu and select AutoFilter to turn filtering on — dropdown arrows will appear in your header row.
  • Click the dropdown arrow in any column header to see all the unique values in that column and check or uncheck the ones you want to see.
  • You can filter on multiple columns at once, and Excel will show only rows that match all your filters together.
  • To remove a filter and see all rows again, click the dropdown arrow and select "Clear Filter" or turn off AutoFilter entirely from the Data menu.
  • Filtered data can be copied, printed, or moved — Excel copies only the visible rows, not the hidden ones.

Turning on AutoFilter in three steps

Start by clicking any single cell inside your data table. It doesn't matter which cell you pick — Excel will recognize the entire connected block of data. If your data has gaps (empty rows or columns in the middle), Excel might not recognize all of it, so fill those in first or select your data manually.

Go to the Data menu at the top of the screen. In Excel on Windows, you'll see it between "Insert" and "Review". On a Mac, it's in the same menu bar. Click Data, and a dropdown menu will open.

Click AutoFilter (or Filter on some versions of Excel). when ready, small dropdown arrows will appear in every cell of your header row — the first row of your data. These arrows are your filter controls. If you don't see arrows, your header row might not be formatted as text, or you might have clicked a cell outside your data. Try again with a cell that's clearly inside your table.

Filtering a single column to show only what you want

Click the dropdown arrow in the column header you want to filter. A menu will pop up showing every unique value in that column. For example, if your column contains state names, you'll see a list of all the states in your data. At the top of this menu is a search box and checkboxes next to each value.

By default, all values are checked, meaning all rows are visible. To hide rows, uncheck the values you don't want to see. If you want to see only California, uncheck every state except California. If you want to hide just one or two values, it's faster to uncheck those specific ones and leave the rest checked.

Click OK at the bottom of the menu. Excel will now hide every row that doesn't match your choice. The rows are still in your spreadsheet — they're just hidden. Notice that the dropdown arrow in that column header now shows a different color or icon, signaling that a filter is active on that column.

Filtering on multiple columns at the same time

You can explore filters to more than one column, and Excel will show only rows that match all of your filters together. For example, you could filter to show only California customers who made a purchase in 2023. Each filter narrows the results further.

Click the dropdown arrow in a second column header and uncheck the values you don't want. Then click OK. Now your spreadsheet shows only rows that match both the first filter and the second filter. You can add a third, fourth, or fifth filter the same way — each one further narrows what you see.

If you filter on too many columns, you might end up with zero rows showing, which means no data matches all your criteria. That's not an error — it just means your filters are too strict. Click any filter dropdown and adjust your choices to loosen the restrictions.

Removing filters to see all your data again

To remove a single filter and show all values in one column again, click that column's dropdown arrow and select Clear Filter From [Column Name]. The rows hidden by that filter will reappear, but any other filters you've set will stay active.

To remove all filters at once and see your entire spreadsheet, go back to the Data menu and click AutoFilter again. This turns off filtering completely, and the dropdown arrows disappear from your header row. All hidden rows come back into view.

What happens when you copy or print filtered data

When you copy rows from a filtered spreadsheet, Excel copies only the visible rows — not the hidden ones. This is useful when you want to send a colleague only the California customers, or paste a subset of your data into a report. Select the cells you want to copy (or press Ctrl+A to select all visible data), copy as usual, and paste elsewhere. The hidden rows stay hidden in the original spreadsheet and don't get copied.

Printing works the same way. If you have a filter active and you print, Excel prints only the visible rows. This prevents you from accidentally printing 500 rows when you meant to print 50. Always check your filters before printing to make sure you're printing what you intend.

Common problems and how to fix them

If the dropdown arrows don't appear after you click AutoFilter, your data might not have a proper header row. Excel expects the first row to contain labels, not data. If your first row contains actual data (like a customer record), add a blank row above it and type column names, then try AutoFilter again.

If a filter dropdown shows strange or unexpected values, you might have blank cells or extra spaces in that column. A cell with just a space looks empty but counts as a separate value. Clean up your data by removing extra spaces, then reapply the filter.

If you filter and see no rows at all, your filters are contradictory — no data matches all of them together. Click each filter dropdown and check your choices. For example, if you filtered for "California" in one column and "Texas" in another, nothing will match, so you'll see zero rows. Change one of the filters to fix this.

Frequently Asked Questions

Can I filter by a range of numbers, like showing only sales over $1,000?

Yes. Click the dropdown arrow in a column with numbers, and you'll see an option called Number Filters or Standard Filter. This opens a dialog where you can set conditions like "greater than 1000" or "between 500 and 2000". This is more powerful than checking individual values and is the fastest way to filter numeric data.

If I filter my data and then add new rows, will the filter still work?

Usually yes, but it depends. If you add rows at the bottom of your existing data, Excel often recognizes them as part of the same table and includes them in the filter. If you add rows in the middle or far away, Excel might not include them. To be safe, turn off AutoFilter, add your new rows, then turn AutoFilter back on.

Does filtering change my original data?

No. Filtering only hides rows — it doesn't delete or move anything. Your original data is completely unchanged. If you close the spreadsheet without saving, the filter disappears but your data stays exactly as it was. If you save while a filter is active, the filter settings are saved too, and the next time you open the file, the same filter will be active.

Can I filter by color or by the way a cell looks?

Yes, if your cells are colored. Click the dropdown arrow and look for an option like Filter by Color. This lets you show only cells with a specific background color or text color. This is useful if you've color-coded your data to mark different categories or statuses.

What's the difference between AutoFilter and Standard Filter?

AutoFilter is the straightforward dropdown method described here — you click arrows and check boxes. Standard Filter (also called Advanced Filter) opens a dialog where you write conditions like "State equals California AND Sales greater than 5000". Standard Filter is more powerful but takes longer to set up. Start with AutoFilter; switch to Standard Filter only if you need complex rules.