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.
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
- 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
- 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.
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...
Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
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. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
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.
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
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.