To make reports from the information in a database we need to filter and extract information accurately. In this case, the filters that are placed in the headers are sometimes not enough. For these cases in which you need to filter with complex criteria, the best option is to use the advanced filters in an Excel database.
How does the Advanced command work?
The operation of this command is different from the Filter command that we usually use in Excel.
The criteria for using advanced filters must be written in a range independent of the database you want to filter. When you run this Excel tool it will take the data to filter from the selection you make in the Advanced Filter dialogue box.
In this case, it is not necessary to filter the data over the range of cells or table itself. You can export and copy the filtered data to another Excel sheet in the same book and this is how you must do it to keep the data origin intact.
How to create advanced filters in Excel
To create advanced filters you must use the same headings that contain the range of cells or table you want to filter. So the first step will be to copy the headers and paste them into another Excel sheet.
Under the headings is where you should write the data you want to filter.
In the first example, we do first filtering by Commercial and Client Name.
It is very important that you write the data exactly as they appear in the source data. Otherwise, the filter will not work properly.
In the first example, we only want to filter the sales of the commercial Pablo Álvarez to the client Grupo Portel.
How to execute the Advanced Filters command
To run the Advanced Filters command:
On the Data tab – within the Sort and Filter tool group.
Select the Advanced option.
You must set the following options:
Action You have two options; Filter the list without moving it to another place or Copy to another place. We will always check the second option Copy to another place to extract the filtered data in another Excel sheet. Checking this option automatically activates the Copy to box.
Range of the list. In this field, you must enter the origin of the data. Click on the button to add data. Go to the sheet where the data is located, place the cursor on any cell in the data range or table and press CTRL+E to select all the data. Press ENTER to accept the selection.
Range of criteria. In this field, you must select the filters you wrote in an independent range. Click on the select data button and select both the headers you copied and the data to filter that you typed below and press ENTER.
Copy to. This option allows you to set where you want to copy the filtered data. Select a cell and the data will be placed from that cell down and to the right.
Finally, press OK to run the Advanced Filters command.
Filter multiple criteria for the same category or column.
If you want to filter several criteria of the same column, you must write all the criteria you want to filter under the column.
When running the advanced filter, Excel understands that you want to filter the data of Criterion 1 or Criterion 2.
Filter by several criteria of different categories or columns.
To filter by several criteria of different columns you must write each criterion under the corresponding heading and in the same row.
When you are in the same row, Excel understands that it only has to filter the data containing all the conditions established in the row.
In the example of the image, only the data of the commercial Paula González in the city Alicante would be shown.
Filter numeric values
It is very interesting to use the advanced filters when you want to filter numeric data because you can filter by data greater than or less than.
To do this you only have to place the symbol greater > or less < before the amount for Excel to filter it correctly. According to the criteria of the image, only sales exceeding 2000€ in the North Zone will be shown. Filter numeric values between two values You can also set two filter criteria for the same numerical category. For example, you can filter sales that are above a given quantity and below a given quantity. In this case, you must place the categories and data in parallel so that Excel only shows the data that meets both conditions. Filter by dates Filters on dates work just like filters for numeric data. You can set a search with a date greater than > or less than >. You can also perform an advanced filter between two specific dates.