Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 
Courses - Microsoft Excel 221
Description: Intermediate Microsoft Excel - Data Analysis 2
Running Time: 75 minutes
Pre-Requisites: Excel 220 strongly recommended
Versions:
We use Excel XP in this course, but the lessons are valid for all versions of Excel from 95 to 2003. There are cosmetic changes in Excel 2007. Order before 1/23/2017 to get a FREE upgrade to our 2007 version when released!
 

 Click here for the Teacher's Outline
 
In this series of Excel courses (220-224) we will study Data Analysis. This course is the second in the series. This course covers AutoFilter, Custom AutoFilter, Advanced Filtering, Calculated Filters, Custom Views, and Subtotals.

We'll work with Filters a lot in this course. Filters allow you to weed out data you don't want and show just a specific set of data. For example, if you have a bunch stores in different cities, and you want to see sales from just your Toronto stores.

 

You can also use filters to show you the Top 10 items (or top 10%, or top 20 items, and so on).

 

You can use Custom Filters to show you, for example, all stores that have sales between $100 and $900.

 

You can create Advanced Filters to specify your own criteria row. This will let you create query-like structures in your Excel spreadsheets and really get the data you want!

 

We've got a great side project in this class where we take a long list of customers (some of whom are missing data), filter that data correctly, sort and filter by ZIP code...

 

...and then create mailing labels using Microsoft Word and a Mail Merge.

 

You will learn all about Filter Wildcard characters.

 

You will learn about Calculated Filters.

 

You will learn how to create Custom Views with your filter. This will allow you to set up special filters, such as "show me all Buffalo stores with at least $500 in sales," and "show me all Toronto stores with at least $300 in sales." You can then quickly switch between them using the toolbar.

 

Next we'll learn about the SUBTOTAL function, and how it's much better than SUM or AVERAGE when working with Filters. Why do you need it? What's the benefit? You'll find out!

 

You'll also learn how to create automatic Subtotals (not to be confused with the SUBTOTAL function). These are automatic subtotals for groups of records - like "show me the sales totals for each group of stores by month."

 

We'll talk a little bit about Grouping and Outlining. We'll discuss it in great detail in the next class (Excel 222).

 

Learning how to use Filters properly is the cornerstone for good, solid data analysis. Whether you've got a few records to filter, or several thousand, the lessons in this course will teach you how to manage that data.

 

 

 Click here for the Teacher's Outline
 
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Excel 221

Richard on 1/1/2007:  In this series of Excel courses (220-224) we will study Data Analysis. This course is the second in the series. This course covers AutoFilter, Custom AutoFilter, Advanced Filtering, Calculated Filters, Custom Views, and Subtotals.
Jacqueline Henits on 4/7/2008: Richard,

You are absolutely the best. You have demystified Excel for me. You are so thorough and clear. Thanks a million.

Richard Rost on 4/16/2008: Jacqueline, thank you very much!
 Henry on 11/4/2009: After filtering a table of data, is there a way to exclude all items in a particular filter, without eliminating the filter?
Richard Rost on 11/6/2009: Henry, can you give me an example of what you mean?
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP