What filtering does and when you need it
Filtering lets you hide rows in a spreadsheet so only the data matching your criteria shows on screen. You keep all your data — nothing is deleted — but you see only what matters for the task at hand. If you have a list of 500 customers and need to see only those in California, filtering shows you those rows and hides the rest. When you clear the filter, all 500 rows come back.
Filtering is different from sorting, which rearranges rows. Filtering is also different from deleting, which removes data permanently. You use filtering when you need to focus on a subset of your data without changing the spreadsheet itself.
The most common reason to filter is to find patterns or check specific entries. You might filter to see only orders over $1,000, or only tasks marked "not started", or only entries from the last month. Filtering works best when your data is organized in a table with column headers — one row at the top that names what each column contains.
Key Takeaways
- Filtering hides rows that don't match your criteria, so you see only the data you need without deleting anything.
- Your data must have a header row — one row at the top with column names — before you can turn on filtering.
- Click the Data menu, then AutoFilter, to add filter buttons to your header row.
- Click the dropdown arrow in any column header to choose which values to show or hide.
- You can filter multiple columns at once, and Excel shows only rows that match all your filters together.
Setting up your data so filtering works
Before you filter, make sure your spreadsheet has a header row — the first row of your table, with names for each column. The header row tells Excel which column is which. If row 1 contains actual data instead of column names, filtering will treat that data as a header and hide it from your results.
Click anywhere inside your table of data. You do not need to select the entire table; Excel finds the boundaries automatically. Then open the Data menu at the top of the screen. Look for the option called AutoFilter and click it. Excel adds a small dropdown arrow to the right side of each header cell. These arrows are your filter controls.
If you do not see the Data menu, you may be in a view that hides it. Check that you are working in a normal spreadsheet view, not Page Preview or another mode. If AutoFilter is grayed out, select at least one cell in your data table and try again.
Filtering a single column
Click the dropdown arrow in the column header you want to filter. A menu appears with a list of every unique value in that column. Next to each value is a checkbox. Unchecked boxes mean those rows are hidden; checked boxes mean those rows show.
By default, all boxes are checked. To hide rows with a specific value, uncheck the box next to that value. For example, if the column contains city names and you uncheck "Denver", all rows with Denver in that column disappear. To show only one or two values, first uncheck "All" at the top of the menu — this unchecks everything — then check only the values you want to see.
Click OK at the bottom of the menu when you are done. The spreadsheet now shows only rows matching your choice. Notice that the dropdown arrow in the filtered column turns blue, a signal that a filter is active on that column. Row numbers on the left side also turn blue and skip numbers, showing you that some rows are hidden.
Using text and number filters for ranges and patterns
The checkbox method works well when you have a short list of values. When you have hundreds of different entries, or when you need to find values within a range, use the filter menu's search and comparison options instead.
Click the dropdown arrow in your column header. At the top of the menu, you see options like Text Filters or Number Filters depending on what type of data is in the column. Click the one that matches your data. A submenu appears with choices like "Greater Than", "Less Than", "Between", "Contains", and "Does Not Contain".
Choose the comparison you need. A dialog box opens where you type the value or range you want. For example, to show only sales over $5,000, click Number Filters, choose "Greater Than", and type 5000. To show only entries containing the word "urgent", click Text Filters, choose "Contains", and type urgent. Click OK to explore the filter.
Filtering multiple columns at once
You can add filters to more than one column, and Excel shows only rows that match all your filters together. This is called an AND filter — the row must satisfy the first filter AND the second filter AND any others you add.
Set your first filter as described above. Then click the dropdown arrow in a second column header and set another filter. The spreadsheet updates to show only rows that pass both tests. You can add a third, fourth, or more filters the same way. Each new filter narrows the results further.
For example, you might filter the State column to show only California, then filter the Status column to show only "Pending". The result is all pending orders from California. If you then filter the Amount column to show only values over $1,000, you see only pending orders from California over $1,000.
Clearing filters and seeing all your data again
To remove a single filter and show all values in one column again, click its dropdown arrow and check "All" at the top of the menu. The rows hidden by that filter reappear, but other filters stay active.
To remove all filters at once and see your entire spreadsheet, open the Data menu and click AutoFilter again. This turns off filtering completely and removes the dropdown arrows from your headers. All rows come back. Your data is unchanged; you are just switching the filter feature off.
If you want to turn filtering back on later, click Data and AutoFilter again. The dropdown arrows return, but your previous filter choices are gone — you start fresh.
Troubleshooting common filtering problems
If the filter menu shows blank entries or seems to be missing values, your data may have extra spaces or inconsistent formatting. For example, "California" and "California " (with a space at the end) are treated as different values. Check a few cells in that column to see if spacing or capitalization is inconsistent. Clean up the data before filtering, or use the "Contains" text filter instead of checking individual boxes.
If filtering seems to have no effect, make sure you clicked OK to explore the filter. Also check that you are looking at the right column — it is straightforward to click the wrong dropdown arrow. If the entire spreadsheet disappears, you may have filtered out all rows by accident. Click the dropdown arrow in the filtered column and check "All" to bring them back.
If the Data menu or AutoFilter option is not visible, you may be in a protected spreadsheet or a view that does not support filtering. Try clicking a cell in the middle of your data table and try again. If it still does not work, the spreadsheet owner may have restricted filtering on purpose.
Frequently Asked Questions
Does filtering delete my data?
No. Filtering only hides rows; it does not remove or change anything. When you clear the filter, all rows come back exactly as they were. If you want to permanently remove rows, you must delete them separately.
Can I filter by date?
Yes. Click the dropdown arrow in a date column and choose Date Filters from the menu. You can then pick options like "Before", "After", "Between", or specific months and years. This is faster than scrolling through a long list of individual dates.
What if I want to show rows that match either one value or another, not both?
Filtering in Excel works with AND logic — a row must match all active filters. To show rows matching value A OR value B in the same column, use the checkbox method: uncheck "All", then check only the boxes for the values you want to see.
Can I save a spreadsheet with filters turned on?
Yes. When you save the file, the filter settings are saved with it. The next time you open the spreadsheet, the filters are still there and still active. If you want to share the file with someone else, remember that they will see only the filtered view unless they clear the filters themselves.
How do I filter by color or formatting?
Click the dropdown arrow in the column header and look for an option like "Filter by Color" or "By Cell Color". This option appears only if cells in that column actually have background colors or text colors applied. Click it to show only cells with a specific color.