Many employees of various organizations who have to work with Mircosot Excel in any way, whether they are ordinary accountants or analysts, often face the need to select a number of values from a huge data array. To simplify this task, a filtering system was created.
Normal and advanced filter
Excel presents the simplest filter, which is launched from the “Data” - “Filter” tab (Data - Filter in the English version of the program) or by using a shortcut on the toolbar that looks like a cone-shaped funnel for pouring liquid in a bottle with a narrow neck.
For most cases, this filter is the best option. But, if you need to select by a large number of conditions (and even by several columns, rows and cells), many are wondering how to make an advanced filter in Excel. In the English version is called Advanced filter.
First use of the advanced filter
In Excel, most of the work goes with tables. Firstly, 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 top of which is left for the header, the conditions of the advanced Excel filter will fit into these rows. In the future, the filter will probably be changed: if more conditions are needed, you can always insert another line in the right place. But it is desirable that between the cells of the range of conditions and the cells of the source data there should be one unused row.
How to use the advanced filter in Excel, examples, we will consider below.
| A | B | C | D | E | F |
| 1 | Products | Name | Month | Day of the week | Town | Customer |
| 2 | vegetables | | | | Krasnodar | Auchan |
| 3 | | | | | | |
| 4 | Products | Name | Month | Day of the week | Town | Customer |
| 5 | fruit | peach | January | Monday | Moscow | "Pyaterochka" |
| 6 | vegetables | a tomato | February | Monday | Krasnodar | Auchan |
| 7 | vegetables | cucumber | March | Monday | Rostov-on-Don | "Magnet" |
| 8 | vegetables | eggplant | April | Monday | Kazan | "Magnet" |
| 9 | vegetables | beet | May | Wednesday | Novorossiysk | "Magnet" |
| 10 | fruit | Apple | June | Thursday | Krasnodar | "Bakal" |
| eleven | greens | dill | July | Thursday | Krasnodar | "Pyaterochka" |
| 12 | greens | parsley | August | Friday | Krasnodar | Auchan |
Filter application
In the above table, lines 1 and 2 are for a range of conditions, lines 4 to 7 are for a range of source data.
First, enter the appropriate values in line 2, from which the advanced filter in Excel will repel.
The filter is launched by selecting the cells of the source data, after which it is necessary to select the “Data” tab and click the “Advanced” button (Data - Advanced, respectively).
In the window that opens, a range of selected cells will be displayed in the "Initial range" field. According to the above example, the string takes the value "$ A $ 4: $ F $ 12".
The Condition Range field should be populated with the values $ A $ 1: $ F $ 2.
The window also contains two conditions:
- filter the list in place;
- copy the result to another place.
The first condition allows you to generate the result in the place reserved for 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 "Put the result in a range" field. The user selects a convenient option, for example, the first one, the "Advanced Filter" window in Excel closes.
Based on the entered data, the filter will form the following table.
| A | B | C | D | E | F |
| 1 | Products | Name | Month | Day of the week | Town | Customer |
| 2 | vegetables | | | | Krasnodar | Auchan |
| 3 | | | | | | |
| 4 | Products | Name | Month | Day of the week | Town | Customer |
| 5 | vegetables | a tomato | February | Monday | Krasnodar | Auchan |
When using the “Copy the result to another location” condition, values from 4 and 5 lines will be displayed in the user-defined range. The original range will remain unchanged.
Ease of use
The described method is not entirely convenient, because for improvement they usually use the VBA programming language, with the help of which they compose macros that 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. When changing the values of the cells of line 2 allocated for Excel with an advanced filter, 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 set values, the advanced filter in Excel is also able to handle complex queries. These are the entered data, where part of the characters are replaced by wildcards.
The symbol table for complex queries is shown below.
| Request example | Result |
| 1 | P* | returns all words starting with the letter P: - peach, tomato, parsley (if you enter in cell B2);
- Pyaterochka (if you enter in cell F2).
|
| 2 | = | the result will be the removal of all empty cells, if any, within the specified range. It can be very useful to resort to this command in order to edit the source data, because tables can change over time, the contents of some cells are deleted as unnecessary or irrelevant. The use of this command will reveal empty cells for their subsequent filling, or table restructuring. |
| 3 | <> | all non-empty cells are displayed. |
| 4 | * ju * | all meanings where the letter combination is “ju”: June, July. |
| 5 | = ????? | All cells in a column that have four characters. Symbols are considered to be letters, numbers and a space character. |
It is worth knowing that the * symbol can mean any number of characters. That is, with the entered value "p *" all values will be returned, regardless of the number of characters after the letter "p".
The sign "?" implies only one character.
Bundles OR and AND
You should be aware that the information specified by one line in the “Range of conditions” is regarded as being written in a bunch by the logical operator (AND). This means that several conditions are fulfilled simultaneously.
If the data is written in one column, the advanced filter in Excel recognizes them by the associated logical operator (OR).
The table of values will take the following form:
| A | B | C | D | E | F |
| 1 | Products | Name | Month | Day of the week | Town | Customer |
| 2 | fruit | | | | | |
| 3 | vegetables | | | | | |
| 4 | | | | | | |
| 5 | Products | Name | Month | Day of the week | Town | Customer |
| 6 | fruit | peach | January | Monday | Moscow | "Pyaterochka" |
| 7 | vegetables | a tomato | February | Monday | Krasnodar | Auchan |
| 8 | vegetables | cucumber | March | Monday | Rostov-on-Don | "Magnet" |
| 9 | vegetables | eggplant | April | Monday | Kazan | "Magnet" |
| 10 | vegetables | beet | May | Wednesday | Novorossiysk | "Magnet" |
| eleven | fruit | Apple | June | Thursday | Krasnodar | "Bakal" |
Summary tables
Another way of filtering data is carried out using the command "Insert - Table - Pivot Table" (Insert - Table - PivotTable in the English version).
The mentioned tables likewise work with a pre-selected data range and select unique values in order to further analyze them. In fact, it looks like working with a drop-down list of unique fields (for example, the names of the company employee) and the range of values that are displayed when a unique field is selected.
The disadvantage of using pivot tables is the need to manually adjust the source data when changing it.
Conclusion
In conclusion, it should be noted that the scope of filters in Microsoft Excel is very wide and diverse. It is enough to apply imagination and develop your own knowledge, skills.
The filter itself is easy to use and learn, it’s easy to figure out how to use the advanced filter in Excel, but it is designed for cases where you need to filter out information for a small number of times for further processing. As a rule, it does not provide for working with large amounts of information due to the usual human factor. Here, more thoughtful and advanced information processing technologies in Microsoft Excel come to the rescue.
The macros compiled in the VBA language are very popular. They allow you to run a significant number of filters that contribute to the selection of values and display them in the appropriate ranges.
Macros successfully replace the many hours of work on compiling consolidated, periodic and other reports, replacing the long analysis time of huge arrays with just a one-second click.
Using macros is justified and inconvenient. Anyone who has encountered the need for application will always find, if desired, enough material to develop their knowledge and find answers to questions of interest.