What filtering does and why you'd use it

A filter in Excel lets you hide rows that don't match what you're looking for, so you see only the data you need. If you have a spreadsheet with 500 sales records, a filter can show you just the ones from January, or just the ones over $1,000, without deleting anything. The hidden rows are still there — you're just looking at a subset.

Filtering is different from sorting. Sorting rearranges your rows in a new order. Filtering temporarily hides rows. You'll use filtering when you want to focus on specific records without changing the order of your data, or when you need to count or work with only certain rows.

Key Takeaways

  • Select any cell in your data table, then click the Data menu and choose AutoFilter to add dropdown arrows to your column headers.
  • Click the dropdown arrow in any column header to choose which values to show or hide.
  • You can filter by text (exact matches or partial text), by number (greater than, less than, ranges), or by date.
  • Multiple filters work together — if you filter two columns, Excel shows only rows that match both conditions.
  • Clearing a filter shows all rows again, but does not undo any sorting or other changes you made before filtering.

Turning on AutoFilter for your data

Start by clicking any cell inside your data table — it doesn't matter which one. Excel will recognize the entire connected block of data as your table. Then go to the Data menu at the top of the screen and click AutoFilter.

You'll see dropdown arrows appear in the header row of each column. These arrows are your filter controls. If you don't see arrows, make sure you clicked a cell that's actually part of your data, not an empty cell below it. If the header row is not the first row of your data, click the cell in the actual header row before turning on AutoFilter.

Filtering by a single column

Click the dropdown arrow in the column you want to filter. A menu will open showing every unique value in that column. By default, all values have a checkmark, meaning all rows are visible.

To show only certain values, uncheck the ones you want to hide. For example, if the column contains months and you only want to see January data, uncheck February, March, and every other month. Then click OK. Excel will now show only the rows where that column contains January.

The dropdown arrow in a filtered column turns blue to remind you that a filter is active on that column. To see all rows again, click the dropdown arrow and check "All".

Using text search and number ranges

For columns with many values, typing is faster than unchecking. In the filter menu, you'll see a search box at the top. Type part of the text you're looking for — Excel will show only the values that contain those letters. This works for product names, cities, or any text column.

For number columns, click the dropdown arrow and select Number Filters (or Text Filters for text columns). You'll see options like "Greater Than", "Less Than", "Between", and "Equals". Choose the condition you need, enter the number, and click OK. For example, to show only sales over $5,000, choose "Greater Than" and type 5000.

Date columns work the same way — click the dropdown, choose Date Filters, and pick a condition like "After", "Before", or "Between". Then enter the date or date range you want.

Combining filters on multiple columns

You can filter more than one column at the same time. Each filter narrows the results further. If you filter the Region column to show only "West" and the Product column to show only "Widget A", Excel displays only rows that are both in the West region AND selling Widget A.

Set up the first filter, then set up the second one the same way. The rows that remain are the ones matching all your conditions. You can add a third or fourth filter if you need to — they all work together. To remove one filter without removing the others, click its dropdown arrow and select "All" again.

Clearing filters and returning to your full data

To show all rows again, go to the Data menu and click Clear Filter. This removes all active filters at once and shows every row. The dropdown arrows stay in place, so you can filter again whenever you need to.

If you want to remove the filter arrows entirely and go back to a normal table, click Data and then AutoFilter again. This turns off filtering completely. Your data stays the same — you're just removing the filter controls.

What happens to sorting when you filter

Filtering and sorting are separate operations. If you sort your data first and then explore a filter, the sort order stays the same — you're just hiding some rows. If you filter first and then sort, Excel sorts only the visible rows, not the hidden ones.

This matters when you're working with filtered data. If you filter to show only January sales and then sort by amount, the sort applies only to those January rows. When you clear the filter and see all months again, the overall sort order may look different because the hidden rows weren't included in the sort.

Frequently Asked Questions

Can I filter by more than one value in the same column?

Yes. In the filter menu, check only the values you want to see. If you want to show January, March, and May, uncheck all the other months. Excel will display rows where that column contains any of the checked values.

What's the difference between a filter and a sort?

A sort rearranges your rows in a new order — alphabetical, smallest to largest, or by date. A filter hides rows that don't match your criteria without changing the order of the rows you see. You can use both at the same time.

If I filter my data and then save the file, will the filter stay on when I reopen it?

Yes. Excel remembers which filters you had active and reapplies them when you open the file again. If you want to save the file with all rows visible, clear the filters before saving.

Can I filter by color or formatting?

Yes, but only if you're using the filter menu. Click the dropdown arrow, select Filter by Color, and choose the cell color or font color you want to show. This is useful if you've color-coded your data.

What if I filter and then accidentally delete a row?

The row is deleted permanently — filtering doesn't protect against deletion. If you delete a hidden row by mistake, use Ctrl+Z (or Cmd+Z on Mac) to undo when ready. Once you close the file, the deletion cannot be undone.