Filters in Excel are easy to set up and offer a quick and easy way to view only pertinent data while hiding the other data on the spreadsheet. Before we look at how to filter data in Excel, here are some tips which will make the filter work better:
Tip 1: The first row of the filter will always look for titles that provide information about each of the columns, therefore your spreadsheet should have a header row. This can be anywhere on the sheet, however, you can only create one filter per sheet, so often times this header row is in Row A.
Tip 2: Depending on the amount of data contained in your spreadsheet, it may be useful to freeze the header row so it stays in the display even when scrolling down on the page.
Tip 3: Ideally, remove any blank rows separating the data which should be part of your filter. Below is an example of a sheet which will end up including blank rows in the filter, as they are interspersed with the data to be included.
Tip 4: Review your data for consistency. When using filters, non-matching values are considered separately. Below is an example – “Saveco” and “Save-co” should be written the same way if they refer to the same thing.
Additionally, the format of data within each column should be the same. For example, if there is a cell formatted as Text within a list of dates, the filter options available will be affected.
With the above guidelines in mind, you’re ready to filter your data.
A/ How to filter in Excel
– Step 1: Select your filter range.
There are numerous ways to select the area that should be included as part of the filter. Here they are:
– You can click on the top-left cell of the area you want to filter. In the case of the below example, that cell is A1. Using this option to select data for a filter will let Excel determine the cells to include in the filter.
– Or you can drag your mouse or use the keyboard to highlight the entire range you want to filter. Using this option to select data for a filter will let you specifically choose the cells to include in the filter. This may be required if there is data on the sheet which should not be included in the filter, as with the example below.
– Or you can just click the “Select all” button, which is the button in the top left corner of the spreadsheet (see the illustration to the right).
Using this option to select data for a filter will select all the data on the sheet to include in the filter.
– Step 2: Apply the filter.
In the ribbon, go to the “Data” tab, then in the “Sort & Filter” group, click on “Filter“.
The filter is created based on the selection chosen in Step 1. You can tell that a filter is now on, as the “Filter column” button displays next to each header column.
– Step 3: Use filter options in the filter column dropdown.
Note: Options in the filter dropdown will vary depending on the data type in the column being filtered:
– When filtering on a Date formatted column: sort options allow to “sort from oldest to newest” and “from newest to oldest” and the checkboxes are organized into a tree allowing more specification in the dates or group of dates (by month and year).
– When filtering on a Number formatted column: sort options allow to “sort from smallest to largest” and “from largest to smallest” and the checkboxes are ordered from smallest to largest by default to make scrolling through the list more efficient.
So, let’s take a look at all the options available in the filter column dropdown, for a text formatted column.
This dropdown menu offers many options for filtering the data shown, and it also allows the data to be sorted in various ways (see the screenshot to the right):
- Sort alphabetically, options for “A to Z” and “Z to A”.
- Sort by color.
- Text filters:
- Text equals…
- Text does not equal…
- Text begins with…
- Text ends with…
- Text contains…
- Text does not contain…
- Custom filter…
The “Sort by color” and “Text Filters” options bring up the Custom AutoFilter dialog box, which lets you select one or more criteria to display.
- Below the “Text Filters” option, you have the “Search” area which allows you to type part or all of the data within the fields you want to filter and displays the results in the list below.
- Checkboxes allow you to select or unselect certain data values, or select/unselect all the values.
Once you have selected the data you want to filter, click OK to view the filter on your spreadsheet.
Note that the row headers are now blue, which indicates that not all rows are now being displayed on the sheet.
Also note that the filter column button next to the column which was filtered on, shows now a different symbol, which indicates there is currently a filter on this field.
Multiple columns can be filtered at the same time. In the below example, we are showing only certain Vendors (Column B) where the Expenditure column (Column C) also only displays certain values.
B/ How to clear a filter in Excel
Clearing a filter is different from removing it!
When you clear a filter, you just get back the data to its original state (non-filtered), but the filter option is still available in the columns headers so you can reapply it whenever you want.
– Clear the filter of all columns
If you want to clear the filter of all the columns in the spreadsheet, just go to the “Data” tab, and in the “Sort & Filter” group, click on “Clear“; All data will then be shown in their original and non-filtered state.
– Clear the filter of a specific column
If you want to clear the filter of a specific column to display all its data (non-filtered) and let the other columns filtered, just click the filter column button on this specific column header and choose the option “Clear Filter From…” to clear the filter of that column only.
C/ How to remove the filter from the spreadsheet
If you don’t want the filter anymore on your spreadsheet, you can remove it altogether by going to the “Data” tab, and in the “Sort & Filter” group, click on “Filter” to unselect it. The filter is deactivated (no filter symbol on columns headers) and all records of the spreadsheet are shown.
I hope you’ll find this tutorial on how to filter in Excel very useful; the AutoFilter options described along all this article should allow you to filter and review specific data on your spreadsheet with many different options.
For more complicated filtering, consider using other Excel features, like the Advanced Filter or Pivot Table.