Filter data in a range or table

Applies to: Excel for Office 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Starter 2010.

Use AutoFilter or automatic comparison operators including “greater than” and “top 10” in Excel to reveal your preferred data and conceal those left over. After you’ve filtered data in a range of cells or table, you can choose to reapply a filter to receive the newest results, or clear a filter to present the original dataset.


Apply filters to briefly exclude some of the data in a table, allowing you to prioritise the data you must investigate.

Filter a range of data

  1. Choose any cell within the range.
  1. Pick Data > Filter.
Filter button

  1. Pick the column header arrow.
  1. Choose Text Filters or Number Filters, and then pick a comparison, like Between.
Number Filters Between
  1. State the filter criteria and press OK.
Custom AutoFilter dialog box

Filter data in a table

When you add your data in a table, filter controls are instantly inserted to the table headers.

An Excel table showing built-in filters
© Microsoft 2020
  1. Pick the column header arrow for the column you want to filter.
  1. Uncheck (Select All)  and confirm the boxes you want to display.
Filter Gallery

  1. Click OK. The column header arrow switches to a Filter icon. Choose this icon to edit or clear the filter.

Related Topics

Excel Training: Filter data in a table

Guidelines and examples for sorting and filtering data by colour

Filter data in a PivotTable

Filter by using advanced criteria

Remove a filter

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Leave a Reply

%d bloggers like this: