Home » Our Blog » Filter data in Excel

Filter data in Excel

Sharyn Baines

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.

Excel 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.

Excel Filter Text

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.

Excel Filter Search box

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.

Excel Filter Number

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.

Excel Custom AutoFilter
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.

Excel Filter Dates

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.

Excel Custom AutoFilter 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.

 

Sharyn is an expert trainer.  She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.

She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

If you enjoyed this you may also enjoy...

Find, modify and break links to an Excel workbook

  Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance to help you SAVE TIME, WORK SMARTER

​Read More

Link cells in the same or different Excel worksheets

  Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips

​Read More

Insert an Excel function into your workbook

Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.  Join the

​Read More

Insert subtotal rows into sorted data

Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at

​Read More