Filters let you show only the rows that match what you're looking for

A filter in Excel hides rows that don't meet your criteria, so you see only the data you need. If you have a spreadsheet with 500 rows of sales records, a filter can show you just the ones from March, or just the ones over $1,000, without deleting anything. The hidden rows are still there — you can remove the filter anytime and see everything again.

Filters work on any data range, but they work best when your data has headers — a row at the top that labels each column. Excel uses those headers to build the filter menu.

Key Takeaways

  • Select your data range, click the Data menu, and choose 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 — Excel shows only rows that match all your filters together.
  • Filters hide rows but don't delete them; remove the filter anytime to see all your data again.
  • The Data menu also has a Standard Filter option if you need to filter by conditions like "greater than 50" or "contains the word urgent".

How to turn on AutoFilter in three steps

Start by selecting the data you want to filter. Click the cell in the top-left corner of your data, then drag to the bottom-right corner to highlight the whole range. If your data has headers in the first row, include them in your selection.

Open the Data menu at the top of the screen. Look for the AutoFilter option and click it. Excel will add a small dropdown arrow to each header cell. Those arrows are your filter controls.

If you select just one cell in your data range instead of the whole range, Excel will usually figure out where your data ends and explore the filter to all of it. You don't have to select every single cell.

Filtering by specific values

Click the dropdown arrow in the column header you want to filter. A menu will appear showing every unique value in that column — for example, if your column lists states, you'll see Alabama, Alaska, Arizona, and so on. Each value has a checkbox next to it.

Uncheck the boxes for values you want to hide. If you want to see only California and Texas, uncheck every other state. The checkboxes that are checked will be shown; the unchecked ones will be hidden. Click OK when you're done, and Excel will hide all the rows that don't match your choices.

You can filter by multiple columns at the same time. If you filter the State column to show only California, then filter the Year column to show only 2023, Excel will show only rows where the state is California AND the year is 2023. Every filter you add narrows the results further.

Using Standard Filter for conditions and ranges

AutoFilter works well when you want to show or hide specific values. But sometimes you need to filter by a condition — for example, "show me all sales over $5,000" or "show me all dates after January 1st". That's where Standard Filter comes in.

Open the Data menu and choose Standard Filter (not AutoFilter). A dialog box will open where you can set up conditions. In the first row, choose your column name from the dropdown, then choose a condition like "greater than", "less than", "contains", or "does not contain". Then type or select the value you're comparing to.

You can add multiple conditions by filling in the second and third rows. Use the "And" or "Or" buttons to decide how they work together. "And" means all conditions must be true; "Or" means any one of them can be true. Click OK and Excel will show only the rows that match.

Removing or changing a filter

To remove a filter and see all your data again, open the Data menu and click AutoFilter. This toggles the filter off — the dropdown arrows disappear and all hidden rows come back. Your data is unchanged; nothing was deleted.

To change a filter you've already set, click the dropdown arrow in that column header again. You can check or uncheck different values, or click "Clear Filter" to reset that column. The other filters you've set will stay in place.

If you want to remove all filters at once and start over, go to Data > AutoFilter to turn off the filter feature entirely, then turn it back on again.

Common mistakes and how to avoid them

The most common mistake is forgetting that filtered rows are hidden, not deleted. If you copy data from a filtered spreadsheet, you'll copy only the visible rows. If you meant to copy everything, remove the filter first.

Another mistake is filtering a column that has blank cells. If you uncheck the blank option by accident, you'll hide rows where that column is empty — which might be more rows than you expected. Look carefully at the filter menu to see if "Blanks" is listed as an option.

If your filter menu doesn't show all the values you expect, check whether you included the header row in your selection. If the header row is treated as data, it might appear as a value in the filter menu instead of being recognized as a label.

When to use filters instead of sorting

Sorting rearranges your entire spreadsheet by one column — useful when you want to see your data in a new order. Filtering hides rows without moving anything, which is better when you want to focus on a subset of data while keeping the original order intact.

Use filters when you're exploring data — trying different views to find patterns or answer specific questions. Use sorting when you want to reorganize your spreadsheet permanently or prepare data for printing or sharing.

You can use both together. Filter to show only the rows you care about, then sort those visible rows by another column. When you remove the filter, the sort order stays.

Frequently Asked Questions

Can I filter text that contains a specific word?

Yes, use Standard Filter and choose "contains" as your condition. Type the word you're looking for, and Excel will show only rows where that column contains that word anywhere in the text. For example, filtering for "urgent" will show "urgent", "not urgent", and "very urgent" all together.

What happens to formulas when I filter data?

Formulas that reference filtered data will calculate only the visible rows, not the hidden ones. If you have a SUM formula and you filter to show only March data, the sum will include only March. When you remove the filter, the formula recalculates to include all rows again.

Can I save a spreadsheet with filters turned on?

Yes. When you save the file, the filter settings are saved too. The next time you open it, the same filters will be active and the same rows will be hidden. If someone else opens the file, they'll see it filtered the way you left it.

How do I filter by date ranges?

Use Standard Filter and choose "greater than or equal to" for the start date, then add another condition with "less than or equal to" for the end date, connected with "And". This shows only rows where the date falls between those two dates.

What if I filter by mistake and can't remember what I filtered?

Open the Data menu and look at AutoFilter. If it has a checkmark, filters are active. Click it to turn filters off and see all your data. You can then turn AutoFilter back on and set up the filters you actually want.