How to filter in Excel

What is filter in Excel?

Excel Filter, aka AutoFilter, is a quick way to display only the information relevant at a given time and remove all other data from view. You can filter rows in Excel worksheets by value, by format and by criteria. After applying a filter, you can copy, edit, chart or print only visible rows without rearranging the entire list.

How to add filter in Excel

For Excel AutoFilter to work correctly, your data set should include a header row with the column names like shown in the screenshot below:

Once the column headings are in pace, select any cell within your dataset, and use one of the following methods to insert filter.

How to apply filter in Excel

A drop-down arrow in the column heading means that filtering is added, but not applied yet. When you hover over the arrow, a screen tip displays (Showing All).

To filter data in Excel, do the following:

1. Click the drop-down arrow for the column you want to filter.
2. Uncheck the Select All box to quickly deselect all data.
3. Check the boxes next to the data you want to display, and click OK.

For example, this is how we can filter data in the Region column to view sales only for East and North:

Done! The filter is applied to column A, temporarily hiding any regions other than East and North.

The drop-down arrow in the filtered column changes to the Filter button , and hovering over that button displays a screen tip indicating which filters are applied:

Filter multiple columns

To apply Excel filter to multiple columns, just repeat the above steps for as many columns as you want.

For example, we can narrow down our results to only show Apples for the East and North regions. When you apply multiple filters in Excel, the filter button appears in each of the filtered columns:

Filter blank / non-blank cells

To filter data in Excel skipping blanks or non-blanks, do one of the following:

To filter out blanks, i.e. display non-blank cell, click the auto-filter arrow, make sure the (Select All) box is checked, and then clear (Blanks) at the bottom of the list. This will display only those rows that have any value in a given column.

To filter out non-blanks, i.e. display only empty cells, clear (Select All), and then select (Blanks). This will display only the rows with an empty cell in a given column.

How to use filter in Excel

Apart from basic filtering options discussed above, AutoFilter in Excel provides a number of advanced tools that can help you filter specific data types such as text, numbers and dates exactly the way you want.

And now, let's have a closer look at each option and see how you can create a filter most suited for your data type.

How to filter by color in Excel

If the data in your worksheet is formatted manually or through conditional formatting, you can also filter that data by color.

Clicking the autofilter drop-down arrow will display Filter by Color with one or more options, depending on which formatting is applied to a column:

• Filter by cell color
• Filter by font color
• Filter by cell icon

For example, if you formatted cells in a given column with 3 different background colors (green, red and orange) and you want to display only orange cells, you can get it done in this way:

1. Click the filter arrow in the header cell, and point to Filter by Color.
2. Click the desired color - orange in this example.
   

Voila! Only values formatted with the orange font color are visible and all other rows are temporarily hidden:

For more information, please see How to filter and sort by cell color in Excel.

How to filter in Excel with search

Beginning with Excel 2010, the Filter interface includes a search box that facilitates navigation in large data sets enabling you to swiftly filter rows containing an exact text, number, or date.

Suppose you want to view the records for all "east" regions. Just click the autofilter dropdown, and start typing the word "east" in the search box. Excel Filter will immediately show you all items that match the search. To display only those rows, either click OK in the Excel AutoFilter menu, or press the Enter key on your keyboard.

To filter multiple searches, apply a filter according to your first search term as demonstrated above, then type the second term, and as soon as the search results appear, select the Add current selection to filter box, and click OK. In this example, we are adding "west" records to the already filtered "east" items:

That was pretty fast, wasn't it? Only three mouse clicks!

Filter by selected cell value or format

One more way to filter data in Excel is to create a filter with the criteria equal to the contents or formats of the selected cell. Here's how:

1. Right click a cell containing the value, color, or icon you want to filter your data by.
2. In the context menu, point to Filter.
3. Select the desired option: filter by selected cell's value, color, font color, or icon.

In this example, we are filtering data by the selected cell's icon:

Re-apply a filter after changing data

When you edit or delete data in filtered cells, Excel AutoFilter does not update automatically to reflect the changes. To re-apply the filter, click any cell within your dataset, and then either:

1. Click Reapply on the Data tab, in the Sort & Filter group.
   
2. Click Sort & Filter > Reapply on the Home tab, in the Editing group.
   

How to copy filtered data in Excel

The fastest way to copy a filtered data range to another worksheet or workbook is by using the following 3 shortcuts.

1. Select any filtered cell, and then press Ctrl + A to select all filtered data including column headers.

   To select filtered data excluding column headers, select the first (upper-left) cell with data, and press Ctrl + Shift + End to extend the selection to the last cell.

2. Press Ctrl + C to copy the selected data.
3. Switch to another sheet/workbook, select the upper-left cell of the destination range, and press Ctrl+V to paste the filtered data.

How to clear filter

After applying a filter to a certain column, you may want to clear it to make all information visible again or filter your data in a different way.

To clear a filter in a certain column, click the filter button in the column's header, and then click Clear Filter from <Column name>:

How to remove filter in Excel

To remove all filters in a worksheet, do one of the following:

• Go to the Data tab > Sort & Filter group, and click Clear.
• Go to the Home tab > Editing group, and click Sort & Filter > Clear.

Filter not working in Excel

If Excel's AutoFilter stopped working partway down a worksheet, most likely it's because some new data has been entered outside the range of filtered cells. To fix this, simply re-apply filter. If that does not help and your Excel filters are still not working, clear all filters in a spreadsheet, and then apply them anew. If your dataset contains any blank rows, manually select the entire range using the mouse, and then apply autofilter. As soon as you do this, the new data will be added to the range of filtered cells.