Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Excel 221 Handbook
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.






Microsoft Excel 221
Course Handbook Supplement

By Richard Rost


Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.599cd.com


First Printing 11/22/2005
Copyright 2005 by Amicron Computing
All Rights Reserved


Welcome

Welcome to the 599CD Microsoft Excel 221 Handbook. This course follows Microsoft Excel 220.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 221. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.


Table of Contents

Welcome 2
Table of Contents 2
Introduction 3
Lesson 1. Filtering Data 4
Lesson 2. Custom Filters 9
Lesson 3. Advanced Filtering 14
Lesson 4. Side Project: Sending Late Notices 20
Lesson 5. Calculated Filters & Custom Views 34
Lesson 6. Subtotals 41
Review 53




Introduction

Welcome to Microsoft Excel 221, brought to you by 599CD.com and our new Web site, ExcelLearningZone.com. I am your instructor, Richard Rost.





Objectives for today’s class:

· AutoFilter
· Custom AutoFilter
· Advanced Filtering
· Calculated Filters
· Custom Views
· Subtotals


Pre-Requisites: Microsoft Excel 220

In this class we will be using Microsoft Excel XP and Windows XP for our live-action videos. However, the version of Excel and Windows you have does not matter. Most, if not all of these examples should be applicable regardless of what version of Excel you’re using.


Lesson 1. Filtering Data

Here is a copy of the sheet used from the last class in Excel 220. As a reminder, you can find a copy of this sheet on our web site at www.ExcelLearningZone.com/Excel/221.





Now, let’s say we only want to see the data for our Toronto store. Click somewhere in your list. Click on Data > Filter > AutoFilter.





Notice the drop-down boxes that appear. Click on the one next to Store and pick Toronto.




Notice I’m only seeing the Toronto stores. The other records are hidden.




I can continue to filter the records further by selecting Adams from the Manager column. Notice the down-arrows and the row header (numbers) turn blue when a column is filtered.





You can turn the filter off for a particular field by dropping the box down and selecting (All). You must do it for each column.




Now, drop the Sales filter down, and select (Top 10…)




You now get the Top 10 AutoFilter box open. You can see the top or bottom records. You can specify how many items to see, or a percentage (top 10% for example).





Remove that filter by selecting (All). Notice if I pick the top 10 expenses in this case, I actually get 11 results. That’s because I have duplicate values.




You can turn AutoFilter off by clicking on Data > Filter > AutoFilter again. This will remove the filter drop-down boxes.




Next, I’m going to take a moment to add some of the AutoFilter commands to my Rick’s Toolbar that we created in a previous lesson – to make navigating these filtering commands easier. I’ll add the commands AutoFilter and Show All from the Data section to my toolbar.





Now, watch this. Click on one of the Toronto records, and then click on the AutoFilter toolbar button.




Notice how the AutoFilter turned on, and filtered by Store column to show Toronto records. Turn the filter off by clicking on the Show All button.




Note that AutoFilter can only filter up to 1000 items. If your list is larger than that, you need to use Advanced Filtering.


Lesson 2. Custom Filters

Turn on AutoFilter. Drop down the filter box for the Store field. Click on Custom.




The Custom AutoFilter box appears. Select Equals from the first drop-down box, and Toronto from the second (upper-right) box.




Click OK. Notice the filter has been turned on.





Let’s say you want to see records from Toronto OR from Chicago. Go back into custom AutoFilter:




There are our results:




Try this one… the store Ends With the letter “o”.




Notice now you get stores that end with the letter “o”.
You’ve got a ton of options in here… equals, does not equal, greater than, and so on.




Let’s try this one. Go to the Custom AutoFilter for the Sales field, and set it so that you see all sales greater than 100 and less than or equal to 900.




Click OK. Notice your filtered results.





If you want to clear one of the filters in the dialog, just pick the blank option at the top of the list.




If you want to see all stores except the Toronto store, try this:




Notice that the filtering techniques are a good way to make sure your data is clean and uniform. To make sure someone didn’t type in “Adam’s” instead of “Adams” for example. You can even see your list of unique records just by dropping down the filtering box.





Let’s say we’re missing some data in our list. (I’ll delete a few sales numbers to cheat and show you this example).




If you go to put a filter on now, you’ll see (Blanks) and (NonBlanks). You can select these to filter records accordingly.




If you select Blanks for example, you’ll see the records with blank sales data.




Lesson 3. Advanced Filtering

Before using advanced filtering, you should familiarize yourself with the boolean operators.




Let’s say I want to see all of my Toronto sales that are greater than 500. Set up a little section at the bottom of your list as such:




Now, click somewhere in your list. Now click on Data > Filter > Advanced Filter.





The Advanced Filter dialog appears. Select “Filter the list in place.” The list range is the range of your list (already selected). For the Criteria Range enter in the range of the little criteria box we made just a moment ago.




Click OK and your list is filtered based on the criteria you specified.




Let’s add Advanced Filter… to our custom toolbar.



Let’s say now I want to see all sales from Buffalo. Change the criteria, go to Advanced Filter (nothing has changed), and click OK.




Let’s make this look pretty so people who don’t know Excel can easily use advanced filtering. Delete the criteria box from the bottom. Insert 4 blank rows at the top of your sheet. I’m going to copy and paste my list header row to make a criteria header row (and change the color). Enter in some criteria (here Toronto stores with less than $500 sales).




Click somewhere in your Data List (make sure you’re not in the criteria list). Go to Advanced Filter and set up the parameters.




Notice you can also use other fields in your criteria too…




Remember we learned about Named Ranges in a previous Excel class? We can set up our Data List as a named range to make it easier to use here… Set up the data list as a named range called DataList.




Notice Excel already set up a named range called Criteria for us.





Now if I go to my Advanced Filter, I can use these named ranges in my parameter boxes…




You can also use multiple criteria rows in your advanced filters. Criteria rows work “AND” across and “OR” down. For example, if you have one criteria row like this:




This means, “show me all records where Store = Toronto AND Sales are greater than 500.”

Now, let’s insert another blank row between rows 3 and 4. Now set up additional criteria as such:




This says “show me (all records where Store = Toronto and Sales are greater than 500) OR (all records where Store = Buffalo and Sales are greater than 300).” Notice I’ve added parentheses to indicate that each row is handled on its own and the OR condition is between them.

Now, when you run your advanced filter, make sure you update your criteria range to take into consideration the new row.




Practice: What does this criteria say?




It says “show me (all records where Store = Toronto and Sales are greater than 500) OR (all records where Sales are greater than 700).”


Lesson 4. Side Project: Sending Late Notices

Let’s set up a customer list to send out late notices. I’ll create a list with the following fields:

· FirstName
· LastName
· Address
· City
· State
· ZIP
· PastDueBalance
· DueDate

I’ll type in some data. Notice I’ve intentionally left a couple of fields blank (so we can play with it).




First, let’s filter out all of the fields that are missing data. Let’s turn on the AutoFilter. (Remember we have a custom button on our toolbar for it).





Now drop down the AutoFilter box for City and select (NonBlanks).




Note that you could pick (Blanks) to go through the list and physically enter in any missing cities. Now, do the same thing for ZIP code, or any other fields you’re missing data for. Now, let’s say that today’s date is October 7, 2005. I don’t want to send late notices to customers who have relatively recent orders. Let’s drop down the AutoFilter box for DueDate and pick (Custom…)




Let’s say the DueDate should be Less Than a month ago… or 9/7/2005.





Click OK to see your filtered results.




Now, I’m going to highlight these rows and copy (CTRL-C) them to my clipboard.




I’ll go to a blank new worksheet and hit paste (CTRL-V) and there are my rows.




Now I could use this new sheet to make mailing labels. Before we do that, however, let’s delete this and do this example again using an Advanced Filter. Set up a criteria row above your list like I showed you in the last lesson. First, we need to say “show me all the records where the city is not blank.” How do we do that? Like this:





Yep, that’s it. A little greater-than-less-than sign: < >

Now when you run the filter, it should work:




If you want to see all records where the field is blank, you just put an equal sign by itself.




Here’s a neat trick. Let’s say you want to see all ZIP codes that start with 14xxx. Let’s assume all of your ZIP code are 5 digits (you should always put ZIP-plus4 information in a second field). You’ll need to create TWO ZIP code criteria fields. Put >=14000 in the first, and then <15000 in the second. (Don’t forget to extend your criteria range in the AutoFilter box).



If you want to see all states that begin with the letter “N” just put an “N” in the criteria.




Notice I now have New York and Nevada showing up. What if you want to see all of the states that were exactly equal to “N”? (Yeah, I know there aren’t any… just add one and humor me). You have to type in the criteria like this:

=“=N”

Notice that’s how it will appear when you’re typing it in, and it will appear that way on the formula bar, but you’ll only see =N in the sheet itself. That’s OK. It works.





Next, if you want to see all addresses that have the word “Gate” in them, you should use the following criteria:

= “*Gate*”




Remember, the asterisk wildcard character “*” says that this can be any number of characters – I don’t care what they are. If I run my filter now, I get…




If you want to see all of the records that don’t have the word “Gate” in them, use this criteria:

= “<>*Gate*”


This essentially says “the criteria is equal to those records that are greater than and less than *Gate*” which essentially is not the records that include “Gate” in them. Make sense? Good. I’m glad you’re following me, because I’m lost.

Just kidding. J


Here are some examples for you:




How about our due date. Let’s say before 9/7/05…




Now, let’s run this advanced filter again, but let’s make a copy of the records to a new location. First, put in the “is not blank” criteria for ZIP code. Now, run advanced filter. Pick “Copy to another location.”




Click in the Copy To box and then click where you want the copy of the data to go to.




I’m also going to check on the Unique Records Only box so that I don’t get more than one record for the same customer (let’s assume he might have more than one past-due invoice).

Notice I now have a copy of the filtered data records down below my list.




Let’s CUT these out and PASTE them into a new blank sheet. For me, the data is in Sheet4. I’m going to save my sheet. The filename I have is: Data Analysis Excel 221.xls.


Now, this isn’t a Word class, but since so many people ask me how to do this, I’m going to show you how to make mailing labels out of this information using Microsoft Word…

Inside of Word, click on Tools > Letter and Mailings > Mail Merge Wizard…




The mail merge wizard starts. Select Labels as the document type.




Click on Next: Starting Document.
Next, in step two, click on Label Options.




I’m going to select Laser and Ink Jet labels. I’ll pick Avery Standard as the manufacturer, and the 5160 – Address label as my label type (these are the popular address labels where you get 30 on a page – 3 columns of 10 rows). You can pick whatever labels you use. Click OK. Click Next.

Next select Use an Existing List. Then click on the Browse link.




Find your file.



Now Word finds a bunch of sheets, named ranges, and other stuff in that file. I know my data is on Sheet4, so I’ll pick Sheet4$. Make sure the “First row of data contains column heades” box is checked, and click OK.




Here’s your list of recipients. You can check them off if you want to exclude them from the mail merge.




Now, click Next: Arrange your labels.





Now, click on the Address Block… link.




The Insert Address Block dialog appears. You can change some of the options here, like how your recipient’s name appears, whether or not you want a company name, and how to insert the postal address.




If you want to make sure your fields match up to what Word is expecting, click on the Match Fields button.





Here you can see all of the field names that Word is expecting, and what it has selected as your fields (from your Excel list) to “map” them to. For example, the “First Name” is your FirstName field. The “Postal Code” is your ZIP field, and so on. If some of them are wrong, you can change them here.




Click OK, and OK again. Notice our first label says “AddressBlock”.




Now, click on the Update All Labels button to replicate this first “prototype label” to all of the rest of the labels on the page.




Now click on Next: Preview Your Labels. Notice you can see your mailing labels with sample data in them.







Lesson 5. Calculated Filters & Custom Views

Let’s say I want to see all of the records where the sales are greater than the average of all of the stores’ sales. First, let’s delete the criteria block at the top of the sheet.




Now, calculate the average of your sales. Put that value in cell A2.




Now create a Sales Filter item in B2. We’re going to create a boolean (True/False) value that compares the FIRST sales item (D5) to the Average Sales. Here’s what it looks like…



=D5>=$A$2


When I hit ENTER, you’ll see a TRUE value there. That’s what we’re looking for. The first value IS greater than the average sales.




Now, open up the Advanced Filter dialog. Change the Criteria Range to just our Sales Filter range (B1:B2)




Click OK, and notice the filter takes over.





There’s also a shortcut you could use. If you don’t want to put the Average Sales in its own cell, you could type in the equation like so:




=D5>=AVERAGE(D:D)

The AVERAGE(D:D) says, “give me the average of all of the data in the entire column.” Be careful there is no extraneous data anywhere else (like above or below your list).

OK, now go ahead and delete the first three rows again (1:3). Let’s create an AutoFilter showing all sales from Buffalo greater than 500. You should remember how to do that…




Now click on View > Custom Views…




The Custom Views dialog appears. We don’t have any Views set up, so let’s Add one.




Let’s call this view BuffaloSales500.




Now, let’s create another View. Change the filter to all sales from Toronto greater than 300. Again, go to View > Custom Views.





Now, create another Custom View. Let’s call this one TorontoSales300.




Now, I want to jump back to Buffalo real quick. Click on View > Custom Views. Pick BuffaloSales500 from the list, and click on Show.




And now the Buffalo information is displayed. Want to switch back to Toronto? It’s easy. Just repeat the steps above. Now, let’s put a button for this on our custom toolbar. Right-click on your custom toolbar and select Customize. On the Commands tab, in the View category, scroll down until you see the Custom Views item.




Drag and drop that item right to your toolbar.




Close the Customize dialog. Now you can select from your custom views on the toolbar to quickly jump between your different views.




Let’s make a new one. How about all sales from Chicago. Now, just click in the new Custom Views dropdown box and type in “ChicagoAllSales” and press ENTER… just like creating a Named Cell or Named Range.




You’ll get an Add View dialog, just click OK.




Now if you drop the box down, you’ll see all three.




If you want to get rid of some of your custom views, just open up the View > Custom Views dialog, click on one and hit Delete.




Lesson 6. Subtotals

We know how to create a SUM function to total up all of the sales in a column. If I put a SUM function below our sales column, and then apply a filter, the SUM function does not recognize the filter. Instead, I still get the sum of ALL of the records in that column.




We can use the SUBTOTAL function to calculate the sum of these figures for us. The SUBTOTAL function will not calculate records filtered out by our AutoFilter. Let’s replace that SUM function with a SUBTOTAL function.





Now the first thing SUBTOTAL wants to know is a value called function_num. What is this? You need to tell SUBTOTAL what mathematical formula to use. SUBTOTAL can use Sum, Average, and a bunch of other functions…




1. Average
2. Count
3. Count of ALL values (not just numbers)
4. Max
5. Min
6. Product (multiplication)
7. Standard Deviation based on a sample
8. Standard Deviation based on the whole population (we’ll cover this in a future class)
9. SUM
10. Variance
11. Variance on the whole population


So for our function, we want the SUM function, so put a 9 in as our first parameter…





Now, just highlight the range of cells you want to subtotal (in this case D2:D16) and then press ENTER.




Notice I get the same result as my SUM function gave… but let’s apply a filter now.




Notice how once the filter is applied, the SUBTOTAL shows only the SUM of the visible records.


Can’t remember the function numbers? Click on the Function Wizard.




The function wizard appears and shows you the two parameters.




If you need the list of functions, just hit F1 for the help system.




You can quickly change this to an Average function by just changing the function_num to 1.




Notice we have an Average now.




Now, check this out. Delete that function. Click on the AutoSum button on your toolbar.





Look what I get…




Excel knows I’ve got AutoFilter on and automatically uses the SUBTOTAL function for me. Now, delete it. Drop the AutoSum button down and pick AVERAGE.




And look what you get…




Excel actually used the AVERAGE function which won’t work with filtered results… so if you’re lazy and rely on the AutoSum button, you’ll get wrong numbers if you turn a filter on. Same with COUNT. You’ve got to know to pick SUM and then change the 9 to a 1 in the SUBTOTAL function you’re given.




Now, delete that function and click on Show All. Now, sort your list based on Month.




Now, click on Data > Subtotals.





Now, at each change in Month, give me a Sum of Sales, Expenses, and Profit. Here’s what it looks like:




Click OK to see your results.




Looks nice. Each month is given its own subtotal with the requested calculations.


Notice the Grouping and Outline indicators in the left margin. We’re going to cover these in depth in a future lesson. For now, just click on the Minus box to collapse a group.




Then you can click on a Plus box to expand the group again.




And, of course, you can add background colors and other formatting to these subtotals.



If you examine one of the subtotals, you’ll see how Excel calculated one for each group.




How about this… go back into the Data > Subtotals dialog, and let’s say “for each change in Store, give me the Average of Expenses.”





Now look at the mess I got…




This is because we didn’t sort our list first. Let’s remove the subtotals that are in here. Go to Data > Subtotals and click on Remove All.





Now, sort your list by Store and try that Subtotal again. That’s much better… that’s what I was looking for.





Quick Trick: If you have subtotals ON and you want to get rid of them, just sort the list. You’ll get a dialog that notifies you that your subtotals will be removed.




The list was resorted and the subtotals were removed.





Review

Review topics.

· AutoFilter
· Custom AutoFilter
· Advanced Filtering
· Calculated Filters
· Custom Views
· Subtotals



Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.


RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!


Take your skills check quiz at www.599cd.com/Test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.599cd.com for our complete list of courses.

Need Help? Visit www.599cd.com/TechHelp for technical assistance.

Make sure you’re on our Mailing List. Go to www.599cd.com/MailingList for details.

Contact Us. If you have any questions, go to www.599cd.com/Contact for information on how you can contact us by phone, email, or live online chat.



Don’t forget to visit our User Message Forums online at: www.599cd.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your questions, get answers, and tell us what you thought of our class.



This course, handbook, videos, and other materials are copyright 2002, 2003, 2004, 2005 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.

This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.599cd.com








Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

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

11/11/2021Link to Excel
11/11/2021Read Write Excel Data
11/10/2021Excel Moving Average
9/11/2021Circular References
8/25/2021Stock History
8/23/2021Concatenation
8/17/2021Flash Fill
8/5/2021Stock Portfolio
8/4/2021Stock Portfolio
6/23/2021Import Data
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: excel handbook  PermaLink