What filtering does and when to use it
A filter in Excel lets you hide rows that don't match what you're looking for, so only the data you want stays visible on screen. If you have a spreadsheet with 500 rows of expenses, a filter can show you only the entries from January, or only the ones over $100, without deleting anything. The hidden rows are still there — you're just not looking at them.
Filtering is different from sorting. Sorting rearranges your rows in a new order. Filtering hides rows temporarily. Both are useful, but filtering is faster when you want to focus on one subset of data without changing the order of everything else.
You'll use filters most when your spreadsheet has a header row (the first row with column names like "Date", "Amount", "Category") and multiple rows of actual data below it. Excel needs that structure to know which column you're filtering by.
Key Takeaways
- Select your data including the header row, then click Data > Filter to turn on filtering for that range.
- Once filtering is on, dropdown arrows appear in each header cell — click an arrow to choose which rows to show.
- You can filter by text (show only "Groceries"), by number (show only amounts over $50), or by date (show only entries from this month).
- Multiple filters stack: you can show only January expenses that are over $100 at the same time.
- Clearing a filter shows all rows again, but doesn't undo any sorting or changes you made to the actual data.
Turning on the filter feature
Start by selecting the data you want to filter. Click on any cell in your data range — it can be the first cell, the last cell, or anywhere in the middle. Excel will figure out where your data starts and ends.
Go to the Data menu at the top of the screen. Click it, and you'll see a button labeled Filter (in some versions of Excel it says "AutoFilter"). Click that button once.
Dropdown arrows now appear in the header row of your data. These arrows are how you'll tell Excel which rows to show and which to hide. If you don't see arrows in the header row, make sure you clicked on a cell that's actually part of your data, then try the Data menu again.
Filtering by a single column
Click the dropdown arrow in the column you want to filter. A menu opens showing all the unique values in that column. For example, if you're filtering a "Category" column, you might see "Groceries", "Gas", "Utilities", and "Other".
By default, all values are checked. Uncheck the boxes next to the values you want to hide. If you want to see only "Groceries" expenses, uncheck everything except "Groceries". Then click OK at the bottom of the menu.
Excel now hides all rows where the Category is not "Groceries". The row numbers on the left side turn blue to show that some rows are hidden. The dropdown arrow in that column also turns blue, a visual reminder that a filter is active there.
To show all rows again in that column, click the dropdown arrow and check "All" at the top of the menu, then click OK.
Filtering by numbers and ranges
When you filter a column with numbers, the dropdown menu works the same way — you can uncheck individual amounts. But you also have a second option: Number Filters, which lets you set a range instead of picking individual values.
Click the dropdown arrow in a number column. At the top of the menu, you'll see options like "Greater Than", "Less Than", "Between", and "Equals". Click Number Filters to see these options.
If you choose "Greater Than", a small dialog box opens asking you to enter a number. Type the number you want — for example, 100 — and click OK. Now Excel shows only rows where that column's value is greater than 100. If you choose "Between", you enter two numbers, and Excel shows only rows in that range.
Filtering by date
Date columns work similarly to number columns. Click the dropdown arrow in your date column, and you'll see options like "Equals", "Before", "After", and "Between".
You can also click Date Filters to see preset options like "This Month", "Last Month", "This Year", and "Last Year". These are shortcuts — if you click "This Month", Excel automatically shows only rows with today's date or any date earlier in the current month, without you having to type specific dates.
If none of the presets match what you need, use "Between" to enter a start date and an end date, and Excel will show only rows that fall in that range.
Using multiple filters at once
You can filter by more than one column simultaneously. For example, filter the Category column to show only "Groceries", then filter the Amount column to show only amounts over $50. Excel will show only rows that match both conditions.
Each active filter turns its dropdown arrow blue. If you want to remove just one filter and see all values in that column again, click its dropdown arrow and select "All", then OK. The other filters stay in place.
To turn off all filters at once and see every row again, go back to the Data menu and click Filter (or AutoFilter) again. This removes the dropdown arrows from the header row and shows all hidden rows. Your data itself doesn't change — you're just turning the filter feature off.
What to do if filtering isn't working
If you click Data > Filter and nothing happens, or the dropdown arrows don't appear, check that you've selected a cell in your actual data range. The data needs a header row in the first row of your selection. If your data starts in row 5 instead of row 1, click on a cell in row 5 or below, then try the Data menu again.
If the dropdown arrows appear but show only blank options, your header row might be empty or formatted differently from the data rows below it. Make sure the first row has actual column names like "Date" or "Amount", not blank cells.
If you filter a column and the results look wrong — for example, you filtered for "Groceries" but you still see other categories — check that you actually clicked OK after unchecking the boxes. Sometimes the menu closes without saving your choices if you click outside the dialog by accident.
Frequently Asked Questions
Can I filter text that contains a specific word?
Yes. Click the dropdown arrow in the text column, then click Text Filters at the top of the menu. Choose "Contains" and type the word you're looking for. Excel will show only rows where that column contains that word anywhere in the text.
If I filter my data and then save the file, will the filter stay on when I open it again?
Yes. Excel remembers which filters you had active and keeps them in place when you reopen the file. All rows are still there — the hidden ones just stay hidden until you clear the filter or open the file on a different computer.
Can I filter by color or formatting?
Yes, if your cells are colored or formatted. Click the dropdown arrow, then look for an option like "Filter by Color" or "By Cell Color". This is less common than filtering by value, but it works the same way — you choose which colors to show and which to hide.
What's the difference between filtering and sorting?
Sorting rearranges all your rows in a new order — alphabetical, smallest to largest, or by date. Filtering hides rows without moving anything. Use sorting when you want to see all your data in a new order. Use filtering when you want to focus on one subset and ignore the rest temporarily.
If I delete a row while a filter is on, does it delete the hidden rows too?
No. When you delete a row, only the visible rows are affected. The hidden rows stay hidden and untouched. This is why it's a good idea to clear all filters before you delete data — that way you can see exactly what you're removing.