Excel - Advanced filter details Tutorial

In this video, you will learn about advanced filter details in Microsoft 365. The video covers how to use advanced filters to refine your search and find specific documents or information in the Microsoft 365 environment.
This will help you save time and improve efficiency in your searches.

4:13
4129 views

Available in Microsoft365

Objectifs :

This video aims to teach users how to effectively use the Auto Filter and Advanced Filter features in Excel to manage and analyze data. It covers filtering for unique records, applying multiple criteria, and utilizing wildcard characters for more complex filtering.

Chapitres :

Introduction to Filtering in Excel
Filtering data in Excel allows users to view specific information without altering the original dataset. This video introduces the Auto Filter and Advanced Filter functionalities, which provide various options for sorting and filtering data efficiently.
Using the Auto Filter
The Auto Filter feature is user-friendly and offers multiple options for filtering data. To apply the Auto Filter, select the data range and click on the filter icon in the Data tab. This allows you to filter records based on specific criteria easily.
Applying the Advanced Filter
The Advanced Filter provides more powerful filtering options. To filter for unique records, follow these steps: - Click on 'Advanced' in the Data tab. - In the Advanced Filter dialog, select the 'List range' (the data you want to filter). - Choose the 'Unique records only' option. - Click 'OK' to apply the filter. For example, filtering for 'Tea' will display it only once in the data tab.
Removing Duplicates
In addition to filtering, you can use the 'Remove Duplicates' button on the Data tab. This option permanently deletes duplicate records from your dataset, which is useful when you want to clean up your data.
Filtering with Multiple Criteria
To filter data using multiple criteria, you need to create a criteria range. For instance, if you want to filter by 'Drinks' in the category column and a specific date in the last order column, set up your criteria in cells G1 through H2: - Ensure the top cell in each criteria column matches the headers of the columns you want to filter. - Click 'Advanced' in the Data tab, select the list to filter, and set the criteria range to G1:H2. Click 'OK' to apply the filter.
Using Logical Operators
You can also use logical operators like 'OR' in your criteria. To do this, adjust the criteria range by moving the last order date down a row. This will allow you to filter for records where the category is 'Drinks' or the last order date is '3/28/2013'.
Advanced Filtering Techniques
For more complex filtering, you can use operators such as 'Greater than'. To filter for values greater than a specific number, type a greater than sign before the value in your criteria range. Additionally, wildcard characters like asterisks (*) and question marks (?) can be used to refine your search further.
Copying Filtered Results
To copy filtered results to a different location in your worksheet, select 'Copy to another location' in the Advanced Filter dialog. Specify the cell where you want to copy the results and click 'OK'. This allows you to maintain a clean dataset while analyzing specific information.
Conclusion and Further Learning
This video has provided a comprehensive overview of how to sort and filter data in Excel using both the Auto Filter and Advanced Filter features. There is always more to learn, so viewers are encouraged to explore additional resources and practice using Excel to enhance their data management skills.

FAQ :

What is the difference between Auto Filter and Advanced Filter in Excel?

Auto Filter allows for basic filtering of data based on simple criteria, while Advanced Filter provides more complex filtering options, including the ability to filter for unique records and use multiple criteria.

How do I filter for unique records in Excel?

To filter for unique records, use the Advanced Filter option, select the range of data, check the 'Unique records only' box, and click OK. This will display only unique entries in your dataset.

Can I filter data using multiple criteria in Excel?

Yes, you can filter data using multiple criteria by creating a criteria range that specifies the conditions. Use the Advanced Filter option to select your data range and criteria range, then click OK to apply the filter.

What are wildcard characters and how do I use them in filtering?

Wildcard characters are symbols that represent unknown characters in a search. In Excel, the asterisk (*) can be used to represent any number of characters, while the question mark (?) represents a single character. You can use these in your criteria to filter data more flexibly.

How do I remove filters from my data in Excel?

To remove filters, go to the Data tab and click on 'Clear' to remove all applied filters from your dataset.

Quelques cas d'usages :

Data Analysis in Sales Reports

Using the Advanced Filter feature to analyze sales data by filtering for unique product sales, allowing sales teams to identify top-selling products without duplicates.

Inventory Management

Applying filters to an inventory list to quickly find items that meet specific criteria, such as products in the 'Drinks' category or those with a last order date of a specific day, improving inventory tracking efficiency.

Customer Segmentation

Utilizing the criteria range to filter customer data based on purchase history, enabling marketing teams to target specific customer segments for promotions.

Financial Reporting

Employing the Remove Duplicates function to clean financial data before generating reports, ensuring accuracy and clarity in financial statements.

Project Management

Using the Advanced Filter to manage project tasks by filtering for tasks that are overdue or assigned to specific team members, enhancing project tracking and accountability.

Glossaire :

Auto Filter

A feature in Excel that allows users to filter data in a worksheet based on specific criteria, making it easier to analyze large datasets.

Advanced Filter

A more powerful filtering option in Excel that enables users to filter data based on complex criteria, including unique records and multiple conditions.

Unique Records

Records in a dataset that appear only once, without any duplicates.

Remove Duplicates

A function in Excel that deletes duplicate entries from a dataset, rather than just filtering them out.

Criteria Range

A specified range of cells that contains the conditions used to filter data in Excel.

Wildcard Characters

Special characters used in Excel to represent one or more characters in a search. The asterisk (*) represents any number of characters, while the question mark (?) represents a single character.

Greater Than

A comparison operator used in Excel to filter data that is greater than a specified value.

00:00:06

It's easy to use, and has many options.

00:00:10

You can use the Advanced filter to create more powerful filters;

00:00:15

such as filtering for unique records,

00:00:18

and using operators such as OR.

00:00:23

"Tea" in the Product name column twice.

00:00:26

To filter so that "Tea" displays only once,

00:00:29

on the data tab, click Advanced.

00:00:33

in the Advanced filter dialog, click the List range.

00:00:38

On the worksheet, select the column you want to use to filter for unique records;

00:00:43

in this case the Product name column.

00:00:47

Click Unique records only,

00:00:50

click OK, and now "Tea" appears only once.

00:00:55

You can also use the Remove duplicates button on the data tab,

00:00:59

depending on what you want to achieve,

00:01:01

because this will delete duplicate records; not just filter them.

00:01:06

To use the advanced filter, to filter with multiple criteria,

00:01:11

you need to create a criteria range.

00:01:14

In this example, I created the criteria in the cells G1 through H2.

00:01:21

The top cell in each criteria column must

00:01:24

be the same as the headers in the columns you want to filter by.

00:01:29

The criteria in the category column is equal to "Drinks".

00:01:35

And the last order column is equal to 3/28/2013.

00:01:43

Click Advanced on the data tab; in the Advanced filter dialog,

00:01:48

select the list you want to filter.

00:01:52

Click in Criteria range,

00:01:55

select the criteria, in this case, G1 through H2, click OK,

00:02:03

and the range is filtered using the criteria.

00:02:06

To make criteria equal to "Drinks" or equal to "3/28/2013",

00:02:12

move the last order date down a row.

00:02:16

Click Advanced,

00:02:20

reset the Criteria range, click OK,

00:02:26

and the rows where the category column is equal to "Drinks"

00:02:29

or, the last order column is equal to "3/28/2013" are displayed.

00:02:37

To remove the filter, click Clear on the data tab.

00:02:41

You can add more complex criteria to your criteria range;

00:02:46

such as Greater than instead of the default Equal to.

00:02:51

Type a greater than sign before a value,

00:02:56

click Advanced, reset the Criteria range,

00:03:04

click OK, and the range is filtered using the criteria.

00:03:09

You can use the asterisk and question mark wildcard

00:03:12

characters in your criteria.

00:03:15

<>*s

00:03:20

will return rows where the text in the category column does not end in "s".

00:03:27

In the advanced filter dialog, set the Criteria range.

00:03:32

I'm using just G1 through G2.

00:03:37

To copy the results to a different location on the worksheet,

00:03:40

click Copy to another location;

00:03:44

click in Copy to, select the cell to copy to,

00:03:51

click OK, and the filtered results are copied to the location.

00:03:59

Now you've got a pretty good idea about how to sort and filter data.

00:04:04

Of course there's always more to learn.

00:04:06

So check out the course summary at the end,

00:04:09

and best of all, explore Excel in your own.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2495 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES