What filtering does and when to use it

Filtering in Excel hides rows that don't match what you're looking for, so you can focus on the data that matters. If you have a spreadsheet with 500 customer records and you only want to see orders from January, a filter lets you show just those rows without deleting anything. The hidden rows are still there — filtering is temporary and reversible.

Filtering works best when your data is organized in a table with headers (column titles) at the top. Each column becomes a filter point. You can filter by text (show only "New York"), by number (show only amounts over $500), or by date (show only entries after March 1). You can also filter by multiple columns at once — for example, show only January orders from New York.

Filtering is different from sorting. Sorting rearranges your rows in a new order (alphabetical, smallest to largest). Filtering hides rows temporarily. You can do both on the same data.

Key Takeaways

  • Click any cell in your data table, then click the Data menu and select AutoFilter to turn on filtering.
  • Once filtering is on, dropdown arrows appear in the header row — click an arrow to choose which rows to show or hide.
  • You can filter by text, number, or date, and combine multiple filters on different columns at the same time.
  • Filtering hides rows but does not delete them; turn off AutoFilter to show all rows again.
  • If your data does not have a header row, Excel may misidentify which row is the header — add one or tell Excel which row to use.

Turning on AutoFilter in three steps

Start by clicking any cell inside your data table — it does not have to be the first cell. Then go to the Data menu at the top of the screen and click AutoFilter. Excel will add a dropdown arrow to the right side of each header cell in your top row.

If no dropdown arrows appear, Excel may not have recognized your header row. Go back to the Data menu, click AutoFilter again to turn it off, then make sure your top row contains column titles (like "Name", "Date", "Amount"). Add a header row if you don't have one, then turn AutoFilter back on.

Once the arrows are visible, you're ready to filter. The arrows stay there until you turn off AutoFilter again — they don't affect printing or sharing the file.

Filtering by text or category

Click the dropdown arrow in any column header. A menu appears with a list of every unique value in that column. Uncheck the box next to any value you want to hide, then click OK. Only rows with the checked values will show.

For example, if your column contains city names (New York, Boston, Philadelphia), you can uncheck Boston and Philadelphia to show only New York rows. To show all rows again, click the arrow and check all boxes, or click Reset Filter if that option appears.

If your column has many values, use the search box at the top of the filter menu to find what you're looking for. Type "New" and the list narrows to show only items containing "New".

Filtering by number or amount

Click the dropdown arrow in a column with numbers. Instead of a straightforward checkbox list, you'll see options like Equals, Greater Than, Less Than, and Between. Choose the condition that matches what you want to see.

If you want to show only orders over $500, click Greater Than, type 500 in the box, and click OK. If you want to show amounts between $100 and $500, click Between, enter both numbers, and click OK. The spreadsheet then displays only the rows that meet your condition.

Filtering by date

Click the dropdown arrow in a date column. You'll see date-specific options like Equals, Before, After, and Between. You can also expand the menu to see a calendar or a list of dates grouped by year and month.

To show only entries from January 2024, click the arrow next to the year 2024 to expand it, then click the arrow next to January, and check only the specific dates you want. Or use After to show all dates on or after January 1, 2024, and Before to show all dates before February 1, 2024.

Using multiple filters at once

You can filter on more than one column simultaneously. For example, filter the city column to show only New York, then filter the date column to show only January. The spreadsheet will display only rows that match both conditions — New York orders from January.

Each filter you add narrows the results further. If you filter three columns, all three conditions must be true for a row to show. To remove one filter and keep the others, click the dropdown arrow in that column and click Reset Filter or Clear Filter.

Turning off the filter and showing all rows again

Go to the Data menu and click AutoFilter again. The dropdown arrows disappear, and all hidden rows reappear. Your data is unchanged — filtering never deletes anything.

If you want to keep the filter arrows visible but show all rows temporarily, click any filter dropdown and select Reset Filter or Clear Filter. This removes the current filter condition but leaves the arrows in place so you can filter again later.

Frequently Asked Questions

Can I filter and sort at the same time?

Yes. You can sort your data first (smallest to largest, A to Z, earliest to latest), then explore a filter. Or filter first, then sort. The filter hides rows, and the sort rearranges the visible ones. Both work together without conflict.

What if I filter by mistake and forget what I filtered?

Click any filter dropdown arrow. If the arrow is blue or a different color, it means a filter is active on that column. The menu shows which values are checked and which are unchecked, so you can see what's hidden. Click Reset Filter to show all values in that column again.

Does filtering change my original data?

No. Filtering only hides rows temporarily. Your data is not deleted, moved, or changed. When you turn off AutoFilter or clear a filter, all rows reappear exactly as they were. You can filter the same spreadsheet as many times as you want.

Can I filter text that contains a specific word?

Yes. Click the filter dropdown and look for an option like Contains or Text Filters. Type the word you're looking for, and Excel shows only rows where that column contains that word anywhere in the cell.

What if my spreadsheet has no header row?

Add one. Click the first row, right-click, and select Insert to add a blank row above your data. Type column titles (Name, Date, Amount, etc.) in the new row. Then turn on AutoFilter. If you can't add a row, go to Data > AutoFilter options and tell Excel which row contains your headers.