The fastest way to filter a column

To filter a column in Excel, select any cell in the column you want to filter, then click the Data tab at the top and select AutoFilter. Excel adds dropdown arrows to the header row of your data. Click the dropdown arrow in the column you want to filter, uncheck the items you don't want to see, and click OK. The spreadsheet now shows only the rows that match your choices.

This works on any column in a table or range. If your data doesn't have a header row, Excel treats the first row as data, so add a header before filtering if you can. Once you turn on AutoFilter, you can filter multiple columns at once — each dropdown arrow works independently.

Key Takeaways

  • AutoFilter adds dropdown arrows to your header row and lets you hide rows that don't match your choices.
  • You can filter by text, numbers, dates, or custom conditions like "greater than" or "contains".
  • Filtering hides rows but does not delete them — turning off the filter shows all rows again.
  • You can filter multiple columns at the same time, and each filter stacks on top of the others.

Turning AutoFilter on and off

Click any cell in your data range, then go to the Data tab and click AutoFilter. Dropdown arrows appear in the header row. Click AutoFilter again to turn it off and remove the arrows. Your hidden rows stay hidden until you turn the filter off — turning off AutoFilter does not undo your filtering choices, it just removes the dropdown controls.

If you have multiple tables on the same sheet, AutoFilter turns on for whichever table your cursor is in. You can have AutoFilter active on one table and off on another at the same time.

Filtering by text or numbers

Click the dropdown arrow in the column header. A list appears showing every unique value in that column. Uncheck the boxes next to the values you want to hide, then click OK. Only rows with the checked values show up. To show all rows again, click the dropdown and select All.

For text columns, you can also use Text Filters to search for partial matches. Click the dropdown arrow, hover over Text Filters, and choose options like Contains, Begins With, or Ends With. Type the text you're looking for and click OK. For numbers, Number Filters offers options like Greater Than, Less Than, or Between.

Filtering by date

Click the dropdown arrow in a date column. Excel shows Date Filters as an option. Click it to see choices like Equals, Before, After, or Between. Select the option you need and enter the date or date range. This is faster than unchecking individual dates when you have hundreds of rows.

You can also filter by relative dates like Today, This Week, This Month, or This Year. These update automatically as time passes, so a filter set to This Month always shows the current month's data.

Stacking filters on multiple columns

Filter one column, then filter another. The filters work together — only rows that match both conditions show up. For example, you can filter a Region column to show only "West" and a Status column to show only "Completed". The result shows only completed orders from the West region.

Each filter is independent. Changing one filter does not affect the others. To clear a single filter, click its dropdown arrow and select All. To clear all filters at once, go to the Data tab and click Clear.

Filtering with custom conditions

Click the dropdown arrow and select Standard Filter (or More Filters depending on your Excel version) to build complex rules. You can set up conditions like "show rows where Sales is greater than 1000 AND Region equals West". Use the dropdown menus to pick your column, condition, and value, then click OK.

Standard Filter lets you combine multiple conditions with AND or OR logic. AND means all conditions must be true. OR means any condition can be true. This is useful when you need to show rows that meet one of several different criteria.

What happens to your data when you filter

Filtering hides rows — it does not delete them. Row numbers on the left side turn blue when a filter is active, showing that some rows are hidden. If you copy filtered data, Excel copies only the visible rows. Formulas that reference the entire column still include hidden rows, so your totals and averages may not match what you see on screen.

To work with only the visible data in a formula, use SUBTOTAL instead of SUM or AVERAGE. SUBTOTAL ignores hidden rows by default. For example, =SUBTOTAL(9,A1:A100) sums only the visible cells in that range.

Frequently Asked Questions

Can I filter by color or formatting?

Yes. Click the dropdown arrow, hover over Filter by Color, and choose the color you want to show or hide. This works for cell background color, font color, or cell icon. It is useful when you have color-coded data but the colors are not stored as text values.

What if I filter a column and then add new rows below?

New rows added below your filtered data are not included in the filter automatically. To include them, turn off AutoFilter and turn it back on, or expand your data range. Select your data including the new rows, then click AutoFilter again.

Can I save a filter with my spreadsheet?

Yes. When you save the file, Excel saves the current filter state. The next time you open the file, the same rows will be hidden. If you want to reset the view, click the dropdown arrow and select All, or go to Data and click Clear.

How do I filter to show only blank cells?

Click the dropdown arrow in the column. At the bottom of the list, you will see a (Blanks) option. Uncheck all other boxes and check only (Blanks), then click OK. This shows only rows where that column is empty.