What filtering does and when you need it
Filtering in Excel lets you hide rows that don't match what you're looking for, so you see only the data you want. You keep all your data in place — nothing gets deleted — but Excel temporarily hides the rows that don't meet your criteria. This is different from sorting, which rearranges your entire dataset. Filtering is useful when you have a spreadsheet with hundreds or thousands of rows and need to focus on a specific subset: all transactions from one month, all items below a certain price, or all entries marked with a particular status.
The filter function works on columns. You tell Excel which column to filter by, set a condition (like "greater than 50" or "contains the word urgent"), and Excel hides every row that doesn't match. You can filter multiple columns at once to narrow down further. When you're done, you remove the filter and see all your data again.
Key Takeaways
- Filtering hides rows that don't match your criteria without deleting them, so your original data stays intact.
- You turn on filtering by selecting your data and clicking the Filter button in the Data tab, which adds dropdown arrows to each column header.
- Click a column's dropdown arrow to choose which values to show, or use custom filters to set conditions like "greater than" or "contains".
- You can filter multiple columns at the same time to narrow your view further, and remove all filters at once by clicking Filter again.
Turning on the filter feature
Start by selecting the data you want to filter. Click on any cell inside your data table — you don't need to select the entire table, just one cell within it. Excel will recognize the boundaries of your data automatically.
Go to the Data tab at the top of the ribbon. Look for the button labeled Filter (it usually shows a funnel icon). Click it. Excel will add a small dropdown arrow to the header of each column in your table. These arrows are how you control what rows appear and disappear.
If your data doesn't have a header row — a first row with column names — Excel may ask you to confirm that the first row is not a header. If your first row contains actual data (not column names), click "No" so Excel treats it as data, not a header.
Filtering by a single column
Click the dropdown arrow in the column you want to filter. A menu appears showing every unique value in that column. By default, all values are checked, meaning all rows are visible. Uncheck the values you want to hide. For example, if you have a Status column with values "Pending," "Complete," and "Cancelled," you could uncheck "Cancelled" to hide all cancelled items.
After you uncheck the values you don't want to see, click OK. Excel hides all rows where the Status column contains "Cancelled." The row numbers on the left side will skip numbers (you might see rows 1, 2, 3, 5, 7, 8 instead of consecutive numbers) — this shows you that some rows are hidden, not deleted.
To show those rows again, click the dropdown arrow in that column again and check the boxes you unchecked before, then click OK. Or, to remove the filter entirely and show all rows, go back to the Data tab and click Filter again — this turns off filtering completely.
Using custom filters for conditions
Sometimes you need to filter by a condition rather than by specific values. For example, you might want to see only sales over $500, or dates after January 1st. Click the dropdown arrow in the column you want to filter, then look for an option called Filter by Condition, Number Filters, Date Filters, or Text Filters — the exact name depends on what type of data is in that column.
A submenu appears with options like "Greater Than," "Less Than," "Between," "Contains," and others. Click the condition you need. A dialog box opens where you type the value to compare against. For a sales column, you might choose "Greater Than" and type 500. For a date column, you might choose "After" and type a date. Click OK, and Excel shows only the rows that match your condition.
You can combine multiple conditions in a single custom filter. Some versions of Excel let you click "And" or "Or" to add a second condition — for example, "Greater Than 500 AND Less Than 1000" to see sales in a specific range.
Filtering multiple columns at once
You can explore filters to more than one column to narrow your results further. After you filter the first column, click the dropdown arrow in a second column and set up a filter there too. Excel will show only rows that match both conditions. For example, you could filter Status to show only "Complete" items, then filter Region to show only "West" — the result is all completed items in the West region.
Each filtered column's dropdown arrow changes appearance (often turns blue or shows a different icon) to remind you that a filter is active on that column. If you want to see which filters are currently on, look at the column headers — the ones with active filters stand out visually.
To remove a filter from just one column without affecting the others, click that column's dropdown arrow and click "Clear Filter" or "Show All." The other filters stay in place. To remove all filters at once and see your entire dataset, go to the Data tab and click Filter again.
Clearing filters and returning to your full dataset
If you've applied multiple filters and want to see all your data again, the fastest way is to go to the Data tab and click Filter. This turns off filtering entirely and removes all the dropdown arrows from your headers. All hidden rows reappear when ready.
If you want to keep the filter feature on but just clear one specific filter, click that column's dropdown arrow and look for a "Clear Filter" or "Reset Filter" option. This removes the filter from that column only while leaving other filters in place.
Your original data is never changed by filtering — you're only hiding and showing rows. Even if you accidentally clear a filter, all your data is still there. There's no undo needed because nothing was deleted.
Common mistakes and how to avoid them
The most common mistake is forgetting that filtered data is hidden, not gone. If you copy data while a filter is on, you'll copy only the visible rows. If you meant to copy everything, remove the filter first. Similarly, if you sort filtered data, the sort applies only to visible rows, which can create confusing results. Remove filters before sorting if you want to sort your entire dataset.
Another mistake is explore a filter to a column that has blank cells. If you uncheck "Blank" in the filter menu, you'll hide all rows where that column is empty. This is useful if you want to see only rows with data in that column, but it can be surprising if you weren't expecting blank cells. Check what values appear in the filter menu before you click OK.
If your filter isn't working the way you expect, make sure you clicked on a cell inside your data table before turning on the filter. If you selected a range that's too small or too large, the filter might not explore to the right columns. Click anywhere in your data and turn the filter off, then turn it back on to reset it.
Frequently Asked Questions
Can I filter by more than two conditions at once?
Yes, but the method depends on your version of Excel. In most versions, you can set up a custom filter with two conditions using "And" or "Or." For more complex filtering, you can explore filters to multiple columns — each column adds another condition. If you need very complex filtering, consider using a pivot table instead, which is designed for that kind of analysis.
Does filtering change my original data?
No. Filtering only hides rows; it doesn't delete or modify anything. When you remove the filter, all rows reappear exactly as they were. Your data is completely safe.
What's the difference between filtering and sorting?
Sorting rearranges your rows in a new order (alphabetical, numerical, oldest to newest). Filtering hides rows that don't match your criteria without changing the order of what remains. You can use both together — filter first to show only the rows you want, then sort those rows.
Can I save a spreadsheet with filters on?
Yes. When you save a file with filters active, the filters stay in place when you open the file again. The hidden rows remain hidden until you remove the filter. This is useful if you regularly work with the same filtered view of your data.
What if the filter dropdown arrow doesn't appear?
Make sure you clicked on a cell inside your data table, not outside it. Then go to the Data tab and click Filter. If the arrow still doesn't appear, your data might not be formatted as a table. Try selecting a larger range that includes all your data, or check that your first row contains column headers.