In this tutorial, you will learn how to filter data in Excel in different ways: how to create filters for text values, numbers and dates, how to use filter with search, and how to filter by color or by selected cell's value. You will also learn how to remove filters, and how to fix Excel AutoFilter not working.
If working with large data sets, it can be a challenge not only to calculate data, but also to find the relevant information. Luckily, Microsoft Excel makes it easy for you to narrow down the search with a simple yet powerful Filter tool. To learn more about filtering in Excel, please click on the links below.
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.
Apart from numerous filtering options, Excel AutoFilter provides the Sort options relevant to a given column:
The difference between sorting and filtering in Excel is as follows:
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.
Ctrl+Shift+L
Whatever method you use, the drop-down arrows will appear in each of the header cells:
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:
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:
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:
Tip. To make the Excel Filter window wider and/or longer, hover over the grip handle at the bottom, and as soon as the double-headed arrow appears, drag it down or to the right.
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.
Notes:
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.
Notes:
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.
When you want to filter a text column for something very specific, you can leverage a number of advanced options provided by Excel Text Filters such as:
As soon as you add a filter to a column containing text values, Text Filters will appear automatically in the AutoFilter menu:
For instance, to filter out rows containing Bananas, do the following:
As the result, all of the Bananas rows, including Green bananas and Goldfinger bananas, will be hidden.
To filter data in Excel with two text criteria, perform the above steps to configure the first criteria, and then do the following:
For example, this is how you can filter rows that contain either Bananas or Lemons:
If you don't remember exact search or want to filter rows with similar information, you can create a filter with one the following wildcard characters:
Wildcard character Description Example ? (question mark) Matches any single character Gr?y finds "grey" and "gray" * (asterisk) Matches any sequence of characters Mid* finds "Mideast" and "Midwest" ~ (tilde) followed by *, ?, or ~ Allows filtering cells that contain a real question mark, asterisk, or tilde. What~? finds "what?"Tip. In many cases, you can use the Contains operator instead of wildcards. For example, to filter cells containing all sorts of Bananas, you can either select the Equals operator and type *bananas*, or use the Contains operator and simply type bananas.
Excel's Number Filters allow you to manipulate numeric data in a variety of ways, including:
The following screenshot shows the whole list of number filters available in Excel.
For example, to create a filter that displays only orders between $250 and $300, proceed with these steps:
As the result, only orders between $250 and $300 are visible:
Excel Date Filters provide the greatest variety of choices that let you filter records for a certain time period quickly and easily.
By default, Excel AutoFilter groups all dates in a given column by a hierarchy of years, months, and days. You can expand or collapse different levels by clicking the plus or minus signs next to a given group. Selecting or clearing a higher level group selects or clears data in all nested levels. For instance, if you clear the box next to 2016, all dates within the year 2016 will be hidden.
In addition, Date Filters allow you to display or hide data for a particular day, week, month, quarter, year, before or after a specified date, or between two dates. The screenshot below demonstrates all available date filters:
In most cases, Excel filter by date works in a single click. For instance, to filter rows containing records for the current week, you simply point to Date Filters and click This Week.
If you select the Equals, Before, After, Between operator or Custom Filter, the already familiar Custom AutoFilter dialog window will show up, where you specify the desired criteria.
For example, to display all items for the first 10 days of April 2016, click Between… and configure the filter in this way:
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:
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:
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.
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!
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:
In this example, we are filtering data by the selected cell's icon:
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:
The fastest way to copy a filtered data range to another worksheet or workbook is by using the following 3 shortcuts.
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.
Ctrl + C
to copy the selected data.Ctrl+V
to paste the filtered data.Note. Usually, when you copy the filtered data elsewhere, filtered-out rows are omitted. In some rare cases, mostly on very large workbooks, Excel may copy hidden rows in addition to visible rows. To prevent this from happening, select a range of filtered cells, and press Alt + ; to select only visible cells ignoring hidden rows. If you're not accustomed to using keyboard shortcuts, you can utilize the Go To Special feature instead (Home tab > Editing group > Find & Select > Go to Special... > Visible Cells only).
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>:
To remove all filters in a worksheet, do one of the following:
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.
Basically, this is how you add, apply and use filter in Excel. But there is much more to it! In the next tutorial, we will explore and capabilities of Advanced Filter and see how to filter data with multiple sets of criteria. Please stay tuned!