By 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 222
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 11/29/2005
Copyright 2005 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Excel 222 Handbook. This course follows Microsoft Excel 221.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 222. 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
Table of Contents 2
Lesson 1. Styles 4
Lesson 2. Grouping and Outlining 15
Lesson 3. Manual Grouping 29
Lesson 4. Grouping Tips & Tricks 42
Lesson 5. Data Validation 50
Welcome to Microsoft Excel 222, brought to you by 599CD.com and our new Web site, ExcelLearningZone.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Auto Outlining
· Manual Grouping
· Data Validation
Pre-Requisites: Microsoft Excel 221
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. Styles
Styles are used to store a collection of formatting information about a specific cell. More than just the font information, styles can also store other formatting, such as colors, patterns, protection info, and so on. Let’s begin by creating a basic sheet to track sales for different stores by month. I’m going to highlight all of my numbers.
Now click on Format > Style.
The Style dialog appears. Here you see the name of the style, plus all of the different settings for the style. You can “package” things like the alignment, font, border, patterns, and so on… into the style.
If you drop down the Style Name dialog, you’ll see the different built-in styles that Excel comes pre-packaged with.
The Comma style turns 1000000 into 1,000,000. Comma simply means with zero decimal places.
Let’s apply the Currency style to our numbers. Notice how the Currency style does not have everything packaged in with it… the alignment and font, for example. It’s just a number specification.
If you want to make changes to the style at this point, you can click on the Modify button, and the Format Cells dialog appears. For now, let’s just click on OK and see what we get.
Let’s go back into Format > Style. Click on Modify. Let’s go to the Number tab. Select Currency for the category, 0 decimal places, None for the Symbol, and –1,234 for the Negative Numbers (no parentheses, give me the negative sign).
Hit OK. In addition to changing just the number style, I want all of my currency values to have a green background. Check the box that says Patterns. Notice it says “no shading.” That means you will get NO shading with this style. Click on the Modify button again. Go to the Patterns tab and click on a green box for a background color.
Click OK. Notice Patterns says “Shaded.” Now, we don’t want to change the built-in Currency style, so I’m going to change the name to “RicksCurrency”.
Click OK. Notice how all of our currency values are formatted with the new style.
Now here’s the real benefit… if I change the style, it changes all of these numbers, everywhere in the workbook!
Click on Format > Style and pick RicksCurrency from the drop-down list.
Click on Modify. Click on Patterns and set it to No Color. Let’s instead make the foreground color green.
Click OK a couple of times, and notice the change in all currency values everywhere… recall I didn’t have to highlight anything! Once you set them with the style, they’ll react to all changes to the style.
Notice I can go to any sheet in the Workbook and apply these styles by clicking on Format > Style and picking the RicksCurrency style. I’ll even change mine to blue and watch them change throughout the entire workbook.
Let’s add the Styles dropdown box right next to the Font dropdown box on our toolbar. Customize your toolbars (right-click on any one toolbar and select Customize – we’ve done this before). Under the Commands tab, find the Style drop-down box and add it to your toolbar.
Notice when I click on one of my currency values, the style box says “RicksCurrency”.
Now, let’s format our header row. I’ll make mine bold, green background, blue foreground. Click on one of the months. Click in the Style box and type in “GreenHeaderRow”. Press ENTER.
Just like a Named Cell or Range, we’ve created our own Style – as easy as that. Let’s go to a different sheet. Highlight the header row, and pick GreenHeaderRow from the style box.
Notice the change.
Now the GreenHeaderRow style makes our cells right-aligned. That’s OK. You can pick the cells you want left-aligned after applying the style – and then left-align them.
Let’s modify our style to put a bottom border on it. Click on Format > Style. Click on Modify. Go to the Border tab. Click on the line thickness you want (I’ll pick a thick solid line).
Then in the Border window click where the bottom line would go.
Now, make sure you apply your style to the entire row.
And you’ll now see the bottom border on the entire row – and on the other sheets you gave this style to.
If you’ve set up a bunch of styles and you love them, and you want to copy them into other workbooks, here’s how you do it. First, make sure the workbook that you want to copy the styles to is OPEN. I’ll open up my Excel 221 workbook for this example. Now, in that new workbook, click on Format > Styles and click on the Merge button.
You’ll see a list of open workbooks that have custom styles in them. Pick the book you want (in this case, the only one is our Excel 222 book). Click OK. Now notice that our Excel 221 book has the styles copied into it.
Lesson 2. Grouping and Outlining
Consider a traditional outline, such as one done in Microsoft Word, where you have categories like this:
I. Buffalo Store
II. Chicago Store
This is a “traditional”outline format. Excel can do something similar using the Grouping and Outlining feature. Let’s set up a new sheet showing sales by stores with different sales and expense categories, broken up by month.
Start off with some Sales categories… hardware, software, and service. Follow it up with a “Sales Total” row. Plug in some sales data (random numbers are fine). Finally, use the SUM function to calculate the totals for each column. Nothing new so far.
Now, do the same thing with some Expense categories (rent, utilities, payroll).
Now, all of this data is for our Buffalo store, so let’s make another row header called “Buffalo Profit” and lets set this equal to the Sales minus the Expenses.
AutoFill that formula across, and now we’ve got one store worth of data in our sheet like so:
Now, click on Data > Group and Outline > Auto Outline.
Look what happened. Excel found that we had some groups in our data, and created Grouping Levels for them. See the lines, and the little boxes with the minus signs in them?
Now, click on the box with the minus sign in it next to the “Sales Total” row, and all of the sales information is hidden. The sales group is collapsed.
Notice how the button is now a little plus sign. You can click on the plus sign to expand the group back out again. You can do the same thing with any grouping level. Click to collapse the Expenses group…
You can also click on the little numbers at the top to expand or collapse all members of that “level”. For example, click on the “3” button to show all details. Click on the “2” to show just the sales and expenses totals, and click on the “1” to show just the store totals.
Just to break things up a little bit, I’m going to add a little bolding and some color… we’ll talk about actual formats in a few minutes.
Now, click on Data > Group and Outline > Clear Outline to remove the outline.
This removes the outline from the sheet.
Now, let’s create a second store. I’m just going to copy and paste all of the data for Buffalo and paste it below it… change some numbers if you like (the actual data doesn’t matter for this example), and change the store name to “Chicago.”
I’ll also copy and paste for a third store. Let’s call this one “Toronto.” Let’s turn the outline back on. Click on Data > Group and Outline > Auto Outline. Notice that Excel did a good job of breaking up my stores and categories into grouping levels.
I can click on the “Level 2” button to see the sales and expenses totals for each city. I can click on Level 1 to see just the city summaries. I can click on Level 3 to see all of the details.
You can also click on the individual plus and minus buttons at any time to expand and collapse areas. My favorite trick is to collapse everything (Level 1) and then “drill down” using the pluses to find the data I’m looking for. Let’s remove the outline again.
Note that you can also create horizontal outlines as well. Insert a blank column between March and April. Let’s call this column Qtr1. Now create summary data for the first quarter using the SUM function. You know how to do this.
I’m going to copy and paste that whole column over to the end (after June) and then just change the Qtr1 to Qtr2. Excel figured out what I was doing and updated my formulae for me.
Now, let’s do another Auto Outline. Data > Group and Outline > Auto Outline. Look at the nice horizontal outline.
Notice I’ve got them on both axes (horizontal and vertical).
Notice how I can collapse both axes and then drill down to see just what I want.
Now click on Data > Group and Outline > Settings.
The Settings dialog appears.
You can use this dialog to change some of the settings for your outlines, such as where the summary rows appear and whether or not to apply automatic styles.
Cancel this dialog. Go back and Remove the outline. Now, click on Data > Group and Outline > Settings. Check ON the “Automatic Styles” box, and then click on Create. Notice how Excel creates the outline again and this time applies its own formatting styles to the group summaries.
Notice if you look in the style list, there are new styles, such as ColLevel_1.
Notice if you click on “Buffalo” that the style window shows RowLevel_1. Likewise the Sales and Expenses Totals are RowLevel_2.
Now that we know how to use Styles, we can quickly and easily reformat all of our summary information by just manipulating the styles. Here’s a quick trick to reformat a style without having to use the menu items. Highlight the row that Sales Total is in.
Change the background color to yellow.
Now, click just on the Sales Total cell.
Now, click in the Style box (get the blinking edit cursor). Press the END key to move to the end of the style name – or just make sure you’ve clicked to the right of it.
Now press ENTER. You’ll be asked if you want to redefine the RowLevel_2 style based on your selection. Click on Yes. The style will be changed.
Notice how all of your level 2 row styles have changed throughout the entire sheet.
We can do the same thing for the Qtr1 column… change the ColLevel_1 style.
Lesson 3. Manual Grouping
In this lesson we’re going to learn how to create our own manual grouping and outline levels. Let’s create another basic sheet with sales reps, customers, and sales amounts.
I’m going to add some color, and then do a multi-level sort (Data > Sort) by Rep, and then by Customer.
Let’s group together all of the Jones records. Select all of the rows with Jones in them.
Now click on Data > Group and Outline > Group.
Notice now I have a grouping level for Jones.
Let’s try to do the same thing for Smith…
Data > Group and Outline > Group… oops! Look what happens.
Now, let’s create a custom toolbar to put these grouping commands on. Right-click on any toolbar. Customize. On the Toolbars menu, click on New. Let’s call it our Sorting Grouping Toolbar.
Let’s put the Sort Ascending, Hide Detail, Show Detail, Ungroup,and Group buttons on my toolbar.
I’m also going to add Clear Outline and then change the button to the little piggy bank (we covered this in a previous class when we worked with toolbars – watch the video for a refresher). OK, now let’s Clear the outline and start over. Let’s insert some blank rows after the Jones and Smith sections.
Let’s put a summary in for Jones…
Do the same thing for Jones and Williams. Bold them.
Now, highlight all of Jones’ records – NOT including his summary info.
Now, let’s click on our Group button.
Do the same thing for Smith…
And Williams. Then you can close them all up and see just the summaries.
Now I’m going to insert a blank row in the Jones section under all of his ABC Inc sales, and create a summary section for just that customer.
I’m going to move the summaries over one column… because things are getting hard to read and our calculations are now wrong (the ABC Inc summary is also adding in to our Rep summary).
Now I can group just my ABC Inc sales under Jones.
You can also take all of Jones and slide it over to the right…
Then do the same thing for Smith and Williams… this way the Rep name headers are in a column all to themselves. Also move the summaries over to the right one more column…
You can also use the Hide Detail and Show Detail buttons that we put on our toolbar earlier…
Let’s put the Custom Views dropdown on our new toolbar. Customize your toolbar, and under the View section, find the Custom Views dropdown. Drag it to your toolbar.
Let’s create a new Custom View called SalesByRep.
The Add View dialog appears. Click OK.
Note that once you Clear the grouping and outline levels, you cannot bring them back with the Custom View. Now, let’s delete the summary rows we put in so we’re left with the data rows. Also, move the data back to the left one column where it started. Now, let’s move the Customer column in front of the Rep column. Highlight the customer column, CUT it (CTRL-X) out. Now, right-click on the Rep column and select Insert Cut Cells.
That essentially moves the column.
Let’s Data > Sort by Customer then by Rep then by Sales.
Now we can do the same thing for Customers instead of Reps.
Now, cancel that… try creating the summary information on TOP of the data.
Now, highlight the data rows, click on Data > Group and Outline > Settings. Turn OFF the “Summary rows below detail” and the “Summary columns to the right of detail” items. Click OK.
Now notice that when you click on the Group button, the summary information is placed at the TOP of the data.
Lesson 4. Grouping Tips & Tricks
Notice if you have grouping and sorting on, and you collapse a group, Excel essentialy hides those rows.
Notice, however that if you select everything from A1:A14, and copy and paste it, you get everything, not just the visible rows. Here’s the trick to copy just the visible rows. Highlight the whole range from A1:A14. Click on Edit > Go To…
The Go To box appears. Click on Special…
Click on Visible Cells Only and click OK.
Notice now that just the visible cells are selected.
Now copy them (CTRL-C or the toolbar button). Notice the dancing ants are only around the visible cells. Now, go ahead and paste them, and you’ll see just the visible cells are pasted.
Here’s another trick… use CTRL-8 (that’s the number eight) to show or hide the grouping bar on the left side of the screen.
Now, let’s insert some blank rows above each of our customer groups, and then copy and paste the customer name to create a group header. I’ll then bold each one.
Let’s put in the SUM totals for each of the customers. Make sure you select the numbers below the customer since the header is on the top of each customer group.
Now, let’s group them all together using the Group button.
I’m going to add the Style dropdown item to my custom toolbar.
I’m going to select the first header row for ABC Inc, color it blue and then call this my HeaderGroupStyle. Remember how to do this?
Now, I’ll apply that new style to the other header rows (highlight the row, and then select the new style from the dropdown list).
Oh wait a minute… what happened? It changed the style but didn’t take the color blue!
Let’s examine the style itself. Click on Format > Style.
Notice that the style did not take on the Font of the text we selected. So, let’s check the Font box on, and now it says “Arial 10, Bold Blue.”
Rick’s Tip: this will sometimes happen if you have multiple cells selected when you create your style definition. If you stick to just one cell highlighted when you create the style, you shouldn’t have a problem.
Now I have no problems applying that style to the others.
The benefit again, is that I can go in and modify the style (say, change it to dark red) and that will apply the change to all of the cells everywhere.
Lesson 5. Data Validation
Data Validation allows you to control the information that someone enters into your spreadsheet. Let’s make a new sheet and collect some information about our employees: first and last name, social security number, date of birth, and number of children.
For first and last name, I want each field to contain text, and at least two characters in each. So, let’s highlight columns A and B. Now click on Data > Validation.
The Data Validation dialog appears. Under the Allow dropdown, you’ll see all of the different types of data that’s available: any value, whole numbers (non-decimals), decimals, list (select from a list of options you create), data, time, text of a specific length, or custom.
Let’s pick Text Length. Now we can select a minimum and maximum value for the data. From the Data dropdown, let’s pick Greater than or equal to.
Set the Minimum value to three (3). This means, that they have to have at least three characters for a name.
Now, let’s hit OK and put some values in our sheet. Notice I can put “Joe Smith” just fine in the first row, but as soon as I put just “Ed” in the second first name field, I get an error message:
Let’s highlight columns A:B again, and go back into Data >Validation. Let’s go to the Input Message tab. This is a message that appears when you select a cell with this validation rule. Put in a title and input message for your user:
Hit OK and notice the message that you get when you’re on one of those cells.
And you can highlight each individual column and put specific messages on for each… Here’s a message for just the First Name column:
Let’s take a look at the Error Alert tab. There are three kinds of error alerts: Stop, Warning, and Information. A Stop alert will give an error message and not let the user continue entering the invalid data. A Warning gives them the warning, and lets them decide whether or not to continue or leave the invalid data. Information just tells them they entered invalid data, but does not make them change it.
Let’s choose Stop for this one, and type in an error title and message:
Notice now if you enter invalid data, you get your custom message. Click Retry and fix it.
Try setting it to Warning and you get Yes, No, and Cancel options – allowing you to choose whether or not to leave the bad data. If you say yes, Excel leaves your invalid data in place.
If you set it to Information then you just get an “OK” or Cancel. This is good just to let someone know they put invalid data into your sheet.
Now, if you’ve been watching the video carefully, you would have noticed that I was changing the data validation properties for just one cell while I was showing you the previous examples. Now, I just highlighted the entire row and attempted to change the data validation settings again, and got this:
This is because there were multiple validation settings in that range. Excel wants me to cancel them and start fresh. I’ll click OK and reset everything. I’ll set it back to a Warning state, and set it up for my first name too (notice also I had the first name and last name headers backwards… duh!)
Important Tip: when you create the data validation rule, it does NOT check the existing data. Data validation only checks NEW data as it’s entered into the sheet.
Now let’s set up the validation for SSN. So, highlight the SSN column. A valid SSN is in the format
Which is exactly 11 characters with the dashes… so let’s make this Text Length, equal to, 11.
Let’s set up an Input Message…
ERROR ALERT: I didn’t catch this when recording the video, but as you can see in the screen-shot above, I have the format of the SSN wrong. It should be ###-##-####. Notice I do it again below in the Error Alert too.
Let’s set up that Error Alert…
Boy, someone needs to check these videos better, huh? J I haven’t gotten any emails on this one yet, so apparently none of you have caught this either. Usually when I have a mistake in one of my videos (usually minor, thank you!) I get a flood of emails about it.
Positions now available for editors. Ha ha ha.
Now, let’s hit OK and enter in an invalid SSN. Notice I get the (wrong) error message.
Let’s set up data validation for our DOB field. Highlight the column. Let’s say this has to be a Date between 1/1/1900 and 1/1/1990. Our employees won’t be older than 105 (it’s now 2005) or younger than 15. Note that these dates may have to change if this sheet is used for more than a few years.
Set up an Input Message…
Set up an Error Alert. See, this is a good example of one where you just want the user to check and make sure he entered the right data… didn’t accidentally type in “1/1/03” and the system assumed 2003 when he wanted 1900, for example.
REAL WORLD TIP: You will need to set this up according to the data you’re entering. If you’re entering birth dates and you work in a geriatric office, you may very well want 1/1/00 to 1/1/30 to mean 1900 – 1930. However, if you work in a pediatric office, you want 2000 – 2030. Remember, you can change the default (how Windows deals with two-digit numbers) in your Windows Control Panel.
See, here I accidentally typed in 1875 instead of 1975.
Let’s set up data validation for the Children field. This will be a whole number (we hope) greater than or equal to zero (0).
I’ll set up an error alert. Moving on, let’s say I want to have a Marital Status field on here, and I want to be able to pick from a list of options. Set up a new column. Call it “Marital.” Now, we need to set up our list of options. I’ll move over to the right somewhere and set up a little mini list of options.
Now, highlight the Marital column and set up Data > Validation. Select List from the Allow dropdown.
Use the little button next to the Source box to collapse the dialog and highlight your range for the list…
Mine is in I3:I6. Expand the box again (we’ve done this before with several other topics. Watch the video if you need a refresher).
I’ll set up an input message and a Stop error alert. Nothing fancy. Now, look what I get… a nice drop-down list in my Marital cells.
Notice if I try typing in something not on my list, I get my error alert…
If you’re worried about inexperienced Excel users changing your list, you can just come over and hide the column. You could also lock the cells using cell protection. There are tricks. J
Alternatively, you can also type in the list right in the Source textbox. Just type in each option separated by a comma.
I personally prefer the external list myself. It’s much easier for you to make changes later.
Now, if I have some invalid data in my sheet (like this wrong birth date and and number of children I just inserted), you can see all of the invalid data by turning on the Formula Auditing Toolbar. Right-click on any one of your toolbars and turn this Formula Auditing toolbar on.
We’ll do a lot more with this toolbar in future classes, but here it is. There’s a button on it called Circle Invalid Data. Click on it.
You will see circles around all of the invalid data. Yes, you should ignore the circles in your header row, but you can clearly see which other bits of data are invalid.
NIT PICK TIP: You could highlight just row 1 and then turn the data validation rules OFF for your header row – if you really care enough. I personally never bother.
Fix the invalid data, and the circles go away.
This is a good tool if you’ve been handed a big sheet of data and you want to see what information on this sheet is invalid without searching through all of it yourself manually. Just put some validation rules on and then tell Excel to show you where the bad data is. Or… likewise, if you’re looking for specific data, not necessarily bad data, you can cheat and make the validation rules show you whatever you want.
You can turn the circles off by clicking on the Clear Validation Circles button. I’ll then close the toolbar.
Remember we also covered Conditional Formatting in a previous lesson. This works really well with data validation rules. Highlight the Children column and go to Format > Conditional Formatting. Set the Format to Cell Value Is, Greater Than or Equal To, 0 (Zero). Leave that condition with no format set.
Now add a second condition. Where the Cell Value Is, Less Than 0 (Zero), set the format to red text.
Now, if someone types in –1 children, they’ll get yelled at by the Error Message, and they’ll see that it shows up bright red.
Now, let’s say that we want to make sure that something gets entered into the Last Name field, and it has to be text. Highlight the Last Name column. Go to Data > Validation. Hit the Clear All button to make sure any current data validation rules are removed.
Now, set the Allow dropdown to Custom. We’re going to create a custom rule. Set the Formula equal to:
This is the ISTEXT function, and you only need to check the first (uppermost) cell of the range, and Excel will check all of the rest of them automagically.
The ISTEXT function returns a TRUE or FALSE value letting you know whether or not the value contains text or not. Now, hit OK and try typing in something invalid… change Po to the number 8 and press ENTER.
The number 8 is not text. So, ISTEXT returned a FALSE, and you got the error message.
Now, if you want to make sure that blank records are also checked, make sure to turn off the Ignore Blank checkbox in the validation settings.
Now Ignore Blank only seems to work if you have data in there and you backspace over it – but not if you delete what’s in that cell. Very strange, but that’s the way it works.
Here’s another function for you to try:
This says that the leftmost character of the text entered has to be the letter “A”. Try it and see if it works. If you type in the word “Apple” you’re OK, but if you type in “Pear” you get the error message.
· Manual Grouping
· Data Validation
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:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: