Excel Advanced Filters: Quick & Easy Tips to Save Time Today

May 15, 2014 Excel  No comments

Excel offers a wide range of tools and features to help you sort, process and analyze your data and information. One such tool, Excel Advanced filters, offers different allows you to easily filter your data, so only the information you want included remains. Here are some advanced tips from Desktop Training Academy for using Excel filters.


The AutoFilter features allows certain parts of a list to be displayed based on the criteria you provide. To set up an AutoFilter, first you need to highlight the dataset you plan to filter. Then go to the “Data” tab or on the Editing tab, and selected the “Sort & Filter” section. Nest, click on the “Filter” button.  From there, use the drop-down arrows next to each row heading to select which subgroups you want in your chart. Do this by removing the checkmark from the “Select All” check box and then checking the items that you want displayed. To clear the filter, reselect the “Select All” box or click on the “Clear” button to the right of the “Filter” button. A quick way to add a filter is by pressing “Ctrl” + “Shift” + “L”.

Advanced Filters

Advanced filters can be used for more complex and frequent filtering or as a technique for comparing lists and returning unique values. With advanced filter, you can choose to display the filtered data within the database or copy it and move it to another area of the spreadsheet. It also allows you to build calculations into the filtering process and multiple criteria filtered. To use advanced filters, go to the “Data” tab and in the “Sort & Filter” section, click on the “Advanced” button. Create a criteria area above your data with the same headings, so that your filtered data can be copied there. In the Advance filter dialog box, select the “Copy to another location” option. Under the list range, highlight your data range. Under the Criteria range, highlight the criteria that you want met. Finally under copy to, select the criteria area that you set up. Click on the “Ok” button and the data that fits your specified criteria will be copied to the location you specified.

Advanced Filter with Multiple Criteria

A criteria area may consist of two or more rows: a top row of headings and a second row to contain the criteria. If there are multiple entries on the second row, all criteria must be met, because the entries provide both the function and a condition. On the other hand, if there are multiple entries made on additional rows, these provide either the function or a condition. In this case, any database entries matching the criteria will be calculated. You must create separate criteria ranges to display multiple values within a field.

We here at DTA hope you now feel confident in your ability to use Excel advanced filters. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>