Advanced filter in Excel: examples. How to make an advanced filter in Excel and how to use it?

Many employees of all kinds of organizations who have to work in any way with Mircosot Excel, whether they are ordinary accountants or analysts, often face the need to select a number of values from a huge array of data. To simplify the implementation of this task, a filtration system was created.

Normal and Advanced Filter

Excel provides a simple filter that runs from the Data tab in the English version of the program or using a shortcut on a toolbar that looks like a cone-shaped funnel for transfusion of liquid in a container with a narrow neck.

For most cases, this filter is a very optimal option. But, if you need to perform selection for a large number of conditions (and even a few columns, rows and cells), many are wondering how to make the extended filter in Excel. The English version is called Advanced filter.

First use of the advanced filter

In Excel, most of the work is done with tables. First, it is convenient, and secondly, in one file you can save information on several pages (tabs). Above the main table it is desirable to create several rows, the uppermost of which is left for the header, it is in these lines that the conditions of the extended Excel filter will fit. In the future, the filter will surely be changed: if more conditions are required, you can always insert another line in the right place. But it is desirable, that between cells of a range of conditions and cells of initial data there was one not used line.

How to use the advanced filter in Excel, examples, consider below.

Applying a filter

In the table above, lines 1 and 2 are for the range of conditions, lines 4 through 7 are for the range of raw data.

To begin with, enter the corresponding values in line 2, from which the extended filter in Excel will be repelled.

To start the filter, select the cells of the source data, then select the "Data" tab and click the "Advanced" button accordingly.

In the opened window, the range of the selected cells in the "Initial range" field is displayed. According to the example given, the string takes the value "$ A $ 4: $ F $ 12".

The "Condition range" field must be filled with the values "$ A $ 1: $ F $ 2".

The window also contains two conditions:

• Filter the list in place;
• Copy the result to another location.

The first condition allows you to generate the result in the space allocated to the cells of the original range. The second condition allows you to create a list of results in a separate range, which should be specified in the "Place result in the range" field. The user selects a convenient option, for example, the first one, the "Advanced Filter" window in Excel is closed.

Based on the entered data, the filter will generate the following table.

If you use the "Copy result to another location" condition, the values from 4 and 5 lines will be displayed in the user-defined range. The original range remains unchanged.

Ease of use

The described method is not entirely convenient, therefore for improvement usually use the programming language VBA, which is used to make macros that allow to automate the advanced filter in Excel.

If the user has knowledge of VBA, it is recommended to study a number of articles on this topic and successfully implement the plan. If you change the values of cells of line 2, the extended filter assigned to Excel, the range of conditions will change, the settings will be reset, immediately restarted and the necessary information will be generated in the required range.

Complicated queries

In addition to working with precisely defined values, the advanced filter in Excel is able to handle complex queries. These are the entered data, where a part of the characters is replaced by wildcards.

The symbol table for complex queries is shown below.

It's worth knowing that the * symbol can mean any number of characters. That is, with the entered value of "n *" all values will be returned, regardless of the number of characters after the letter "n".

The "?" Sign means only one character.

Bundles of OR and AND

You should be aware that the information specified by one line in the "Condition range" is regarded as logically written (AND) in the binder. This means that several conditions are fulfilled simultaneously.

If the data is written in one column, the extended filter in Excel is recognized by the associated logical operator (OR).

The table of values takes the following form:

Summary tables

Another way to filter data is by using the Insert-Table-PivotTable command in the English-language version.

The tables mentioned above work in the same way with the previously allocated data range and select unique values to be further analyzed. In fact, it looks like working with a drop-down list of unique fields (for example, the names of a company employee) and the range of values that are displayed when a unique field is selected.

The inconvenience of using pivot tables is the need to manually adjust the original data when changing such data.

Conclusion

In conclusion, it should be noted that the scope of the filters in Microsoft Excel is very wide and varied. It is enough to apply imagination and develop your own knowledge, skills and abilities.

The filter itself is easy to use and learn, it's easy to understand how to use the advanced filter in Excel, but it is designed for cases when it is necessary to filter out the information for a small amount of time for further processing. As a rule, it does not provide for work with large arrays of information due to the usual human factor. Here already more thoughtful and advanced technologies for processing information in Microsoft Excel come to the rescue.

Macros written in VBA language are very popular. They allow you to run a significant number of filters that help select values and output them to the appropriate ranges.

Macros successfully replace many hours of work to compile summary, periodic and other reporting, replacing the long analysis time of huge arrays with just a one-second click.

The use of macros is justified and inconvenient. Anyone who has encountered the need to apply, will always find, if desired, enough material to develop their knowledge and search for answers to questions of interest.