Advanced filters

Advanced filtering is a technique that is used to narrow the data set in the table based on values ​​from another table. In this case, we can consider a second table as a filter containing one or more criteria, each assigned as a separate row in the table, where the values ​​in the columns are filter criteria. Once created advanced filters can also be used to filter multiple tables.

In order to have something to filter, you first need to have a data table. Then, at the other location, the header of this table should be rewritten, so that we can understand the criteria more easily, and enter the values ​​of filtering criteria in rows. If there are more criteria, each criterion should be entered in a separate line. For example, if we want to show in the table all employees with salaries greater than 550 € the criterion in the salary column should be “> 550”. So, we enter the criterion only in the column “VISINA PLATE (height of a wage)”. If we want to add some more criteria, for example, the name “Jovan”, the criterion is entered in the column “JOVAN” but in the row below …

By clicking Advanced, which is in the Sort & Filter group, in the Data ribbon, we open the Advanced Filter dialog box. First of all, we need to determine whether we want to filter the table where it is located (Filter the list, in-place), or we want the filtered data to be copied to an alternative location (Copy to another location). In case we have selected second option in the Copy To field we need to enter the location where the data is being copied. The List Range field is the place where the data table range should be entered, and the Criteria Range is the field where the range of the table where we enter the criteria. We can enter these ranges manually, e.g. “$A$1:$D$6” or by clicking on the field, and then mark the range in the table. In the dialog box, there is also the optional field Unique Records Only. If this field is marked, and there are more than one identical record in the table, only one record that matches the given criterion will be displayed. Confirmation of all selected options is performed using advanced table filtering technique.

Why is this technique convenient? First of all, because the filtering criteria contained in a single table can be applied to several tables that can be in different worksheets. Also, due to the ability to copy data that meets the filtering criteria, this technique can be used to fill tables with data located on another location or another worksheet in relation to the existing table.