Excel - AutoFilter details Tutorial

In this video, you will learn about AutoFilter details in Microsoft 365. The video covers how to filter data by unchecking and checking values, filtering for blank cells, using criteria to filter, applying AutoFilter to multiple columns, and auditing filters by cell and font color.
This will help you effectively filter and analyze data in Excel.

3:27
4086 views

Available in Microsoft365

Objectifs :

This video aims to teach users how to effectively use the Sort and Filter features in Excel to manage and analyze data. It covers various filtering techniques, including filtering for blank cells, applying criteria-based filters, and using color filters.

Chapitres :

Introduction to Filtering in Excel
Filtering data in Excel allows users to focus on specific information by hiding irrelevant data. This video demonstrates how to utilize the Sort and Filter features to streamline data analysis.
Basic Filtering Techniques
To begin filtering, click any cell within your data range or table. Navigate to the Home tab, select 'Sort and Filter', and then click 'Filter'. This action adds dropdown arrows to each column header, enabling filtering options.
Filtering for Blank Cells
To filter for blank cells in a column, click the down arrow for that column. Uncheck 'Select All' and check 'Blank'. If there are no blank cells, this option will not appear. Click 'OK' to display only the rows with blank values in the selected column.
Filtering for Non-Blank Cells
To view cells that are not blank, ensure all options are checked except for 'Blank'. This will display all rows containing data in the selected column.
Using Criteria to Filter Data
You can apply criteria to filter data by clicking the Auto Filter for the desired column. For numeric columns, options such as 'Greater than' will be available. For example, entering '50,000' will display only rows where the value in the code column exceeds this amount.
Applying Multiple Filters
Excel allows the application of multiple filters across different columns. For instance, you can filter by category by unchecking options like 'Drinks'. After clicking 'OK', Excel will show the data that meets all selected criteria.
Understanding Filter Results
Excel provides feedback on the number of records that meet the applied criteria. For example, it may indicate that 2 out of 9 rows match the filters set.
Advanced Filtering Options
Excel offers specific criteria filters for text and dates. For date filtering, you can select options like 'After' and enter a specific date, such as '5/1/2013', to display only relevant rows.
Filtering by Color
You can also filter data based on cell or font color. If multiple colors are present, click the Auto Filter and select the desired color. For example, selecting red will show only rows with red-filled cells in the discontinued column.
Clearing Filters
To remove a filter from a column, click the Auto Filter for that column and select 'Clear Filter From'. To turn off all auto filters, go to the Home tab, click 'Sort and Filter', and then click 'Filter'.
Conclusion
Mastering the Sort and Filter features in Excel enhances data management and analysis capabilities. By applying various filtering techniques, users can efficiently navigate through large datasets and extract meaningful insights.

FAQ :

How do I filter blank cells in Excel?

To filter blank cells in Excel, click on any cell in the range or table, go to the Home tab, click on Sort and Filter, and then click Filter. Click the down arrow for the column you want to filter, uncheck 'Select All', and check 'Blank'. Click OK to display only the rows with blank values.

Can I apply multiple filters in Excel?

Yes, you can apply multiple filters in Excel. After applying a filter to one column, you can select another column and configure its filter criteria. Excel will then display rows that meet all the specified criteria across the selected columns.

What are the different types of criteria I can use to filter data?

Excel offers various criteria for filtering data, including numerical criteria (e.g., greater than, less than), text criteria (e.g., specific text values), and date criteria (e.g., before or after a certain date). The options available depend on the data type in the column.

How can I filter data by cell color?

To filter data by cell color, click on the Auto Filter for the column, then point to filter by color. Select the desired color option to display only the rows where the cells are formatted with that color.

How do I clear filters in Excel?

To clear filters in Excel, click on the Auto Filter for the column you want to clear and select 'Clear Filter From'. To turn off all auto filters completely, click the Sort and Filter button on the Home tab and select 'Filter' to deactivate it.

Quelques cas d'usages :

Data Analysis in Sales Reports

Sales analysts can use filtering techniques to quickly identify products with no sales (blank cells) or those that exceed a certain sales threshold. This helps in making informed decisions about inventory management and sales strategies.

Project Management Tracking

Project managers can filter tasks by their completion status (e.g., blank cells for incomplete tasks) and by due dates to prioritize work. This enhances project tracking and ensures timely completion of tasks.

Customer Feedback Review

Customer service teams can filter feedback data to find comments that are marked as urgent or require immediate attention. By filtering based on specific keywords or categories, they can address customer concerns more effectively.

Inventory Management

Inventory managers can filter products by categories (e.g., excluding discontinued items) and by stock levels to optimize inventory control. This ensures that only relevant products are considered for restocking.

Financial Reporting

Finance professionals can apply date filters to analyze transactions within specific time frames, such as filtering for transactions after a certain date. This aids in generating accurate financial reports and forecasts.

Glossaire :

Auto Filter

A feature in Excel that allows users to filter data in a table or range based on specific criteria, making it easier to analyze and view relevant information.

Sort and Filter

A tool in Excel that enables users to organize data in a specific order and apply filters to display only the data that meets certain conditions.

Blank Cells

Cells in a spreadsheet that do not contain any data or value. Filtering for blank cells allows users to identify and manage incomplete data.

Criteria

Specific conditions or rules used to filter data in Excel. Criteria can vary based on the type of data, such as numbers, text, or dates.

Date Filters

Options in Excel that allow users to filter data based on date values, such as filtering for dates before or after a specific date.

Cell Color Filter

A filtering option in Excel that allows users to filter rows based on the background color of the cells, useful for visually categorizing data.

Records

Individual entries or rows in a dataset. In the context of filtering, records are the rows that meet the specified criteria.

00:00:05

and Filter Data video.

00:00:07

There are many other powerful ways to filter.

00:00:10

If a column contains blank cells, you can filter for

00:00:13

cells that are blank and those that aren't.

00:00:17

Click any cell in a range or table, on the Home tab,

00:00:21

click Sort and Filter, and click Filter.

00:00:25

Click the down arrow for the column,

00:00:28

this is also referred to as the auto filter.

00:00:32

Uncheck Select All, and check Blank.

00:00:37

Blank won't be an option if there aren't any blank cells in the column.

00:00:42

Click OK, and only the row with a blank value in the code column is displayed.

00:00:47

To display cells that aren't blank, leave all options checked except Blank.

00:00:53

You can also use criteria to filter.

00:00:57

Click the Auto Filter for the column you want to filter,

00:01:00

since the code column contains only numbers,

00:01:03

we get the number filters option.

00:01:07

When I point to it, there are a lot of criteria.

00:01:11

Click one such as Greater than,

00:01:15

criteria will be different, for different types of data; such as text.

00:01:21

Type a value, such as 50.000, click OK

00:01:27

and only the rows where the value in the code column

00:01:30

is greater than 50,000 are displayed.

00:01:34

You can apply an Auto Filter to multiple different columns,

00:01:39

click the Auto Filter for a different column

00:01:42

such as category, configure the filter for this column,

00:01:46

such as unchecking Drinks, click OK,

00:01:50

and now multiple filters are applied to the data.

00:01:55

Down here, Excel tells us how many records meet all the criteria;

00:01:59

out of how many total records there are.

00:02:03

In this case 2 of 9 rows.

00:02:06

There are specific criteria filters for text,

00:02:09

there are also specific criteria filters for dates.

00:02:13

Date has a huge number of options,

00:02:16

I click a criterion, such as After,

00:02:20

in the custom Auto Filter dialogue,

00:02:23

enter a date; such as 5/1/2013,

00:02:28

click OK and only rows where the last order value is

00:02:32

after 5/1/2013 are displayed.

00:02:35

You can also audit filter by cell and font color.

00:02:40

Click an Auto Filter, point to filter by color,

00:02:46

this won't be an option if there aren't multiple cell or

00:02:49

font color formats in the column.

00:02:52

Click an option, I want to see the products that have been discontinued;

00:02:58

so I click the red cell color

00:03:01

and only rows where the cell in the discontinued column

00:03:04

is formatted with the red fill are displayed.

00:03:07

To remove the filter for a column,

00:03:10

click the Auto Filter for the column and click Clear Filter From.

00:03:17

To turn off auto filters completely,

00:03:20

click the Sort and Filter button on the Home tab and click Filter.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2494 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