Advanced filter in Excel. How to apply, how to cancel advanced filtering

The Excel program has a wide range of functions. Information filtering is a common spreadsheet function. Most users use filtering when working with large amounts of data, but not everyone knows that there is an advanced filter that adds new features. From the article, you will find out all the features of advanced filtering and learn how to use this convenient feature.

What is data filtering in Excel

Data filtering is a useful feature that allows you to sort information according to specified conditions, and hide unnecessary lines.

Using the Advanced Filter in Excel

Let’s say we have a table with information that needs to be filtered.

Detailed instructions look like this:

1. Initially, we create the 2nd additional table, which will contain the filtering conditions. We make a copy of the header of the first table and paste it into the second. In order to better understand the example, let’s place an auxiliary plate a little higher than the original one. Additionally, fill the new one with a different shade. It is worth noting that the second table can be placed anywhere not only on the worksheet, but on the entire book as a whole.

2. At the next stage, we will fill in the additional plate with the information necessary for further work. We need indicators from the source table, by which we will filter information. In this example, we need to filter by female gender and a sport such as tennis.

3. After filling out the additional plate, we proceed to the next step. We point the mouse pointer at absolutely any cell of the source or additional tables. In the upper part of the spreadsheet editor interface, we find the “Data” section and click on it with LMB. We find a block of commands called “Filter” and select the “Advanced” element.

4. A small special window appeared on the screen, called “Advanced Filter”. Here you can make various settings for advanced filtering.

5. This tool has two uses. The first option is “Copy results to another location” and the second option is “Filter the list in place”. These functions implement various output of filtered data. The 1st variation displays the filtered information in another place in the book, pre-specified by the user. The 2nd variation displays the filtered information in the main plate. We select the required element. In our specific example, we put a checkmark next to the inscription “Filter the list in place.” Let’s move on to the next step.

6. In the line “List range” you need to enter the address of the plate along with the headings. There are two ways to implement this simple procedure. The first way is to write the coordinates of the plate using the keyboard. Second – after clicking on the icon next to the line for entering the range, you need to select the plate by holding down the left mouse button. In the line “Range of conditions” in a similar way, we drive in the address of an additional plate along with headings and lines with conditions. Click on “OK” to confirm the changes made.

Important! When selecting, be careful not to include any empty cells in the selected area. If an empty cell falls into the selection area, then the filtering procedure will not be performed. An error will occur.

7. Upon completion of the procedure, only the information we need will remain in the main plate.

8. Let’s go back a few steps. If the user selects the “Copy results to another location” option, then the final indicator will be displayed in the location specified by him, and the main plate will not change in any way. In the line “Place result in range” you will need to drive in the address of the place where the result will be displayed. Here you can enter one field, which in the end will become the origin for the new additional plate. In our particular example, this is the cell with the address A42.

9. By clicking on “OK”, a new additional plate with the specified filtering settings will be inserted into cell A42 and stretched to the adjacent area.

Cancel advanced filtering in Excel

There are two methods to cancel advanced filtering. Let’s consider each method in more detail. The first method to override advanced filtering:

1. We move to the section called “Home”.
2. We find the block of commands “Filter”.
3. Click on the “Clear” button.

The second method, which cancels advanced filtering:

1. We move to the section called “Home”.
2. Click the left mouse button on the element “Editing”
3. At the next stage, we open a small list of “Sort and Filter”.
4. In the context menu that appears, click LMB on the element called “Clear”.

Important! If an additional label with advanced filtering is located in a new location, then the procedure implemented through the “Clean” element will not help. All manipulations will need to be done manually.

Conclusion and conclusions about the advanced filter procedure

In the article, we have examined in stages several methods for applying an advanced information filter in the Excel spreadsheet editor. To implement this simple procedure, only a new additional plate is needed, in which the filter conditions will be located. Of course, this method is a little more difficult to use than standard filtering, but it implements simultaneous filtering on several criteria. This method allows you to quickly and efficiently work with large amounts of tabular information.