Lessons

Resources

Full Course Outline
Skills Test Quiz - Under Construction
Certificate of Completion
Post-Class Survey

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Excel 2003 Expert 221
Get notifications when this page is updated

Intro

In lesson 221, you will learn how to use the Auto Filter function in Microsoft Excel, create custom and advanced filters, and work with calculated filter settings. I will show you how to create custom views to save different filter setups and introduce some ways to total filtered data. We will also work through a practical example of filtering a customer list and generating mailing labels for late notices. This lesson builds on what was covered in Excel 220, so it is recommended you are familiar with creating and formatting lists, sorting, and using toolbars.

Transcript

Welcome to Microsoft Excel 221 Data Analysis Part 2, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Continuing on with our data analysis series, today we are going to learn about the Auto Filter function and how to make custom auto filters. We will learn about advanced filtering and calculated filters. I will show you how to create custom views to save your different filter settings and learn about some totals.

We are also going to have a little side project where we are going to take a list of customers. We are going to filter out the customers based on different criteria, and we will make mailing labels for them so we can send them late notices. That is a little side project in today's class.

Before taking this class, you should have taken Excel 220 Data Analysis Part 1, where we learn about creating lists, formatting our lists, using data forms, sorting our lists, and setting up our custom toolbars. We will use a lot of those techniques in today's class.

We will be using Microsoft Excel XP 2002 in this class. However, most of the functions we will cover today should work in most versions of Excel.

Some of the sample sheets for today's class will be on the website ExcelLearningZone.com/excel/221. Although the sheets we are using in today's class are not that big, I do recommend you type them in yourself. You will get more out of the lessons if you type in the data on your own instead of copying mine.

And of course, remember to have fun. Try to do something that you like doing in Excel instead of just work. Play with the sample lessons and the sample data before trying to use data that you are working on for some project for work. You will get more out of it if you have fun rather than just do the lessons the way I show them to you.

Quiz

Q1. What main topic is covered in Microsoft Excel 221 Data Analysis Part 2?
A. Auto Filter and advanced filtering techniques
B. Creating Pivot Tables
C. Recording Macros
D. Using Excel for financial analysis

Q2. Which of the following is NOT a topic mentioned in the video?
A. Creating mailing labels for filtered customers
B. Making custom auto filters
C. Adding images to cells
D. Creating custom views to save filter settings

Q3. What prerequisite is recommended before taking this class?
A. Experience with financial modeling
B. Completion of Excel 220 Data Analysis Part 1
C. Experience with VBA programming
D. Prior knowledge of Access databases

Q4. Why does the instructor recommend typing in the sample sheets yourself?
A. To get more practice and understanding from the lessons
B. Because the files are encrypted
C. To avoid virus infections
D. Because the downloads are not available

Q5. What is the 'side project' in this class?
A. Creating a company budget
B. Filtering customer lists and making mailing labels for late notices
C. Designing charts and graphs
D. Merging multiple spreadsheets

Q6. Which version of Excel is primarily used in this tutorial?
A. Excel 97
B. Excel XP 2002
C. Excel 2016
D. Excel for Mac

Q7. What attitude does the instructor recommend for enjoying the lessons?
A. Focusing only on work projects
B. Avoiding sample lessons
C. Trying to do fun activities in Excel
D. Memorizing every step exactly

Answers: 1-A; 2-C; 3-B; 4-A; 5-B; 6-B; 7-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone continues the data analysis series in Microsoft Excel. In this lesson, I will guide you through the Auto Filter feature, where I will show you how to apply filters and create custom auto filters tailored to specific needs. We will also look at advanced filtering, including how to set up calculated filters, and discuss how to save various filter settings by using custom views. Along the way, I will touch on using totals to summarize your filtered data.

We also have a practical side project in this class. I will take a list of customers and demonstrate how to filter the data based on certain criteria. Once filtered, I will show you how to make mailing labels for these customers, aimed at sending them late notices. This exercise will help reinforce the concepts and show you a common real-world application.

It is important that you have already completed Excel 220 Data Analysis Part 1. In that earlier class, we covered essential skills like creating and formatting lists, using data forms, sorting, and customizing toolbars. These foundational topics will be used extensively in this lesson.

For this course, I will be working with Microsoft Excel XP 2002. However, nearly all of the techniques I am teaching are available in most other versions of Excel, so you should be able to follow along no matter what version you have.

The sample worksheets I use are available on the Excel Learning Zone website under Excel 221. While the sheets themselves are not large, I encourage you to type in the sample data on your own rather than copying mine. Typing the data yourself helps reinforce the concepts and gives you better hands-on experience.

Above all, remember that learning is more effective when you are enjoying yourself. Try experimenting with the sample data and lessons in ways that interest you before applying them to work projects. Approaching these exercises with curiosity will help you gain a deeper understanding and make the learning process more enjoyable.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Using the Auto Filter function
Creating custom auto filters
Applying advanced filtering
Creating calculated filters
Saving filter settings with custom views
Using totals with filtered data
Filtering customer lists by criteria
Creating mailing labels for filtered customers