How to use Advanced Filter to filter data on Excel

To filter the information in a given data sheet in Excel with specific conditions, the Advanced Filter feature will help users a lot. This feature will be based on the conditions that users set to filter the data. After that, we will get a new data sheet full of information like the original. In the case of having to work with multiple data tables, the Advanced Filter will help users a lot. You do not need to manually search for values ​​that match the conditions set. The following article will guide you how to use the Advanced Filter feature on Excel.

1. How to delete data, duplicate content in Excel
2. How to create duplicate data entry notifications on Excel
3. How to insert a Word document into an Excel file

How to use Advanced Filter to filter data on Excel

To use the Advanced Filter, users first need to make requests.

1. Data table headers use only 1 line.
2. In the data table, don't filter any cells.
3. There are at least 3 blank lines on the same data table.

We will do so with the data table below provided that the Score is greater than or equal to 20 .

How to use Advanced Filter to filter data on Excel Picture 1

Step 1:

First we need to create a condition table to filter the data. Select the Point header in the table and then click Copy .

How to use Advanced Filter to filter data on Excel Picture 2

Step 2:

Select any cell in Excel and then click Paste or Ctrl + V. Next enter the condition to filter the data.

If the OR condition data is filtered, arrange vertically. If the AND condition is in a horizontal row. So if you need to filter 2 AND conditions in the same criteria, you must use 1 title for 2 cells.

With only 1 condition, just enter the condition under the title box of the filter condition table.

How to use Advanced Filter to filter data on Excel Picture 3

Step 3:

Then click on the main data table and then on the Data tab select Advanced .

How to use Advanced Filter to filter data on Excel Picture 4

Step 4:

The Advanced Filter dialog box appears with different custom items. In the Action section, there are 2 options:

1. Filter the list, in-place: filter and return results at the filter data table itself.
2. Copy to another location: filter and return the filter results in another location, chosen by you.

In the List Range section is the main data area you want to filter. When you place your mouse on the main board and select the Advanced Filter function, Excel will automatically select the data table, by surrounding it with a flashing line.

If Excel selects the wrong data area, click the button located in the List Range box and reselect the data area.

How to use Advanced Filter to filter data on Excel Picture 5

Step 5:

Continuing in this small dialog box in the Criteria range section, select the newly created sub-table to filter the data.

How to use Advanced Filter to filter data on Excel Picture 6

Then localize the data table as shown.

How to use Advanced Filter to filter data on Excel Picture 7

Next, because we created the filter result in another location , click the Copy to another location button , then click on a blank cell outside the data area . Finally click OK .

How to use Advanced Filter to filter data on Excel Picture 8

We will immediately get the results as shown below. All headings of the result column will remain the same as the main table. The result will be candidates with scores greater than or equal to 20.

How to use Advanced Filter to filter data on Excel Picture 9

Filtering data with Advanced Filter on Excel is also not difficult, but you need to set data filtering conditions, AND or OR conditions. Thus the Advanced Filter can read the conditions for data filtering.

See more:

1. Instructions for searching and replacing in Excel tables
2. Summary of expensive shortcuts in Microsoft Excel
3. 10 ways to recover corrupted Excel files

I wish you all success!