Filter data in Excel

Learning how to filter data in Excel enables you to find and work with just a subset of a larger list or database.

Filtering displays only the rows that contain a certain value or that meet a set criteria, while hiding other rows that don’t. Once you have filtered your data you can then work with just the visible set of data.

For example you might like to filter a list to find all sales made to one customer and then print only that data. Filter will allow you to do this in just a few clicks.

Applying the Excel Filter to your data

Before applying Filter to your data list make sure your list has field names (column headings).

1. Select the columns and rows that you want to filter, including the column headings.

Note: it is extremely important you make sure you have all of the data selected that you want included in the filter but avoid selecting the entire worksheet as this may slow down your filtering and you want to try to avoid getting the “whirly-wheel” on your screen. Only select the columns and rows that will be included in the filter.

2. On the Data tab in the Sort & Filter group click Filter.

3. Excel inserts a drop-down arrow next to each column label in your list. Click the arrow in the column that contains the data you want to filter.

4. A list of values will be displayed below the Search box.  If a value is checked it will be displayed.  Remove the check mark from any values you do not want displayed.  Click OK.

The list will be filtered to only display records that meet your selection. A filter icon will be displayed on the drop-down arrow of the field(s) by which the list is filtered. If you place your mouse over the icon the filter setting will be displayed. Continue to add filters to as many columns as required.

Using the Search box to filter

Type keywords or phrases into the Search box to quickly locate list items. In the example below “top” has been typed into the Search box to find any list items containing the characters ‘top’. Use the Search box to quickly locate items when the list is long. It sure beats scrolling! It also makes sure you haven’t missed an item.

Remember that the Search box will always locate words that contain your search criteria. Wildcards can be used to locate items that begin or end with certain characters. For example a* would filter and display all items beginning with the letter “a” and * shop would find all items that end with the word “shop”.

Use the Add current selection to filter check box to search for multiple items and then append the selected item(s) to your current filter list.

Clearing filters

To remove a single filter click the arrow next to the column and select Clear Filter from the list, or on the Data tab, in the Sort & Filter group click the Clear button  to remove all filters.

To remove Filter entirely from your data list, on the Data tab in the Sort & Filter group click Filter.  Excel will remove the Filter drop-down arrows next to each column label in your list.

Filtering on columns containing numbers

1. When it comes to filtering numbers, avoid checking and un-checking items in the filter list and instead click the Number Filters option on the Filter drop-down menu.

2. Select the option you require or click Custom Filter.

3. In the Custom AutoFilter dialog box, select the appropriate options and set the value(s) you want to use for each. In the example below the And option has been selected to filter the list to display any values that are greater than or equal to 500 AND less than or equal to 2000.

4. Once you have your criteria entered click OK and Excel will filter your list based on your custom settings.

Filtering on columns containing dates

1. When it comes to filtering dates, you can easily check and un-check items in the filter list to include or exclude entire years, months or individual days. However if you would like to filter on a specific range of dates click the Date Filters option on the Filter drop-down menu.

2. Select the option you require or click Custom Filter. BTW, the date ranges for Quarter are from January to December and as far as I know these can’t be changes to suit.

3. In the Custom AutoFilter dialog box, select the appropriate options and set the date(s) you want to use for each. In the example below the the pop-up calendar at the end of each input box has been used to quickly insert the dates.

5. Once you have your criteria entered click OK and Excel will filter your list based on your custom settings.

When your total returns an error

If your total returns an error you may need to fix something within the data. Check our my post 5 Reasons why your Excel filter may not be working.

Working with the filtered data

When you filter data in Excel to display a subset of your data, your worksheet is in filter mode. In filter mode, many Excel commands operate only on the visible cells.

• Use the Copy and the Paste commands to place the copy of the filtered data wherever you want it, even in another workbook.
• Once you have filtered the data set select and then delete the rows to remove them from your data table.
• Select and apply formatting to the filtered data.
• Update the data quickly. For example, if we needed to change Pauls Top Shop to Paul’s Top Shop you can filter to only show Pauls Top Shop, update the row at the top of the list with the correct spelling and then copy it down the list. Remove filter once you are done.