How to use Advanced Filter in Excel – Excelchat

Excel’s Advanced Filters are used to create a unique list of items, or for extracting specified items onto a different worksheet.

When we use Advanced Filters Excel, we must first input the criteria on our worksheet – which is very useful for finding data which meets several complex criteria like filtering rows that correspond with items on another list, extracting differences and matches between two columns, locating exact matches including lowercase and uppercase characters, and much more.

Figure 1. of Advanced Filters in Excel

How to use Advanced Filter in Excel

We will now demonstrate how to create an Advanced Filter in Excel using the following simple steps.

Let’s assume that the data we want to filter will require complex criteria – “Date”,  “Items”, “Sales Rep” – we can utilize the “Advanced Filter” dialog box option.

1. Organize our source data in the excel worksheet; add a unique heading for each column  – duplicate headings will only cause confusion when you are performing an Advanced Filter.

Ensure there aren’t any blank rows within our data set.

Figure 2. of Source Data for Advanced Filter in Excel

2. Click on the “Advanced Filter” dialog box to open it, and then click on “Data” > “Advanced”:

Figure 3. of Advanced Filter in Excel

In our worksheet example, we can decide to create advanced filter criteria for the list we have in place

Advanced Filter Excel will detect our list range automatically. If not, we can manually select the cell range on our worksheet.

In this case, we can use the Excel AdvancedFilter tool to quickly highlight any unique records;

Figure 4. of Advanced Filter in Excel

Based on our column headers, we have successfully identified the sales records for each sales rep in the year.

If we are required to copy filtered data to a different location or Excel sheet, use Advanced Filter Criteria Range to quickly obtain a list of any unique records for copying to a different worksheet (so that our original data will remain intact). We begin by selecting the starting cell for our copy;

4. Open a new worksheet 2 and select an empty cell – D2 in our worksheet example:

Figure 5. of Extracting Data to Another Worksheet

5. Click on “Advanced” on our Excel sheet 2 Data tab, and select the “Copy to another location” option.

6. Click in the box tagged “List Range”

7. Choode Sheet1, and then select the database

8. Set the criteria range (optional)

9. Click on the “Copy to” box

10. On Sheet2, Select the exact cell for Excel to begin transferring the results, or just select the headings which we typed earlier on Sheet2.

11. Click “Ok”

Figure 6. of Advanced Filter Criteria Range in Excel