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 220
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 10/12/2005
Copyright 2005 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Excel 220 Handbook.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 220. 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 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. Data Lists 4
Lesson 2. Formatting Data Lists 6
Lesson 3. Data Entry Forms 12
Lesson 4. Sorting 19
Lesson 5. Customizing Toolbars 26
Welcome to Microsoft Excel 220, brought to you by 599CD.com and our new Web site, ExcelLearningZone.com. I am your instructor, Richard Rost.
Rick’s Note: This class marks the first time we’ve used the ExcelLearningZone.com name. Don’t worry, 599CD isn’t going anywhere… we’re just trying to add stronger name recognition to our products.
Objectives for today’s class:
· Creating and Formatting Data Lists
· Data Entry Forms
· Sorting Data Lists
· Customizing Toolbars
Pre-Requisites: Microsoft Excel 101 through 202
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. Data Lists
A “List” in Excel is a part of a spreadsheet the contains rows of data of the same type of information. You may here them referred to as “databases,” however I wouldn’t go that far. In Excel, we’ll call them “Data Lists,” or just “Lists.” Lists are great for managing and analyzing data. A list starts off with a Header Row – a row across the top of the list that tells you what kind of data each column will contain. Go ahead and type in the following List and be sure to create a header row.
If you’re familiar with database terminology, each column represents a field and each row represents a record of data. All of the information in a column should be of the same data type, i.e. don’t mix dates with numbers with currency values.
You can think of records like an address book… each person in your address book represents one record of data. Each person has a phone number. That phone number is the field represented by a column in Excel.
Also, make sure each of your field names in your header row is unique.
When you have a list set up with some data in it, you can right-click in one of the fields and select Pick From List to select an item from a list of items that you already have typed in.
You will see a list of all of the unique values you’ve already typed in. Select one by clicking on it.
Lesson 2. Formatting Data Lists
Let’s format the list like we have learned to do in the past. I’ll left-align the Date and Employee fields, and right-align the Start and End date fields. I’ll also add some color and formatting to the header row. Let’s also type in a bunch of additional data so we can see the list techniques we’re about to learn work better.
Review Tip: Remember, we can make our header row stay put when we scroll up and down by freezing the panes. Click on cell A2 and then Window > Freeze Panes.
I’ll unfreeze them for class.
One of the most requested questions I get in email is how to calculate the difference (in hours) between two different times. Let’s say we want to figure out how many hours each of these employees worked. The hours difference should just be the End time minus the Start time right? 5:00 minus 1:00 is 4 hours. Let’s try that…
When I type in =D2-C2 for the first Hours field, I get 9:00 am. That’s not right. Well, we’re not looking for a time here… we want a number… the number of hours. So let’s format that cell as a number. Let’s right-click on it and select Format Cells and format cell E2 as a number. (You know how to do this from previous classes, right?) Now I end up with 0.38 in that cell.
Now we’ve got the right answer… but Excel treats dates and times like this: one day equals 1.00. So what Excel is telling you is that the difference between these two times is 0.38 days. How do we convert days into hours? Just multiply by 24, right? Our formula should read =(D2-C2)*24. Now we get 9.00 hours. Looks good to me! AutoFill it down the column.
You can refer to values outside of your list. For example, if you want to put the number 24 outside of the list instead of in your formulas, that’s OK:
Just remember to refer to that cell with an absolute reference in your formulas so it doesn’t get messed up when you AutoFill down the column.
We don’t need that for this class, so let’s put the formula back to 24 and delete those extra cells. We can also use conditional formatting to notice any data that’s out of the ordinary. Let’s say they’re not supposed to work more than nine hours. Let’s click on Format > Conditional Formatting.
Let’s say if the cell value is greater than or equal to 9…
Set the format…
Set the color equal to red text.
Hit OK a couple of times and use the Format Painter to apply the format to the entire column. Notice the records where the employee worked more than 9 hours are now red.
Rick’s Note: Yes, I know we already covered some of these topics in previous classes, but this is another useful tool for data analysis… making your records jump out. I strongly feel that a properly formatted sheet goes a long way to helping you get the right information out of all of your data.
Notice if I change one of my end times to accidentally be earlier than the start time, I get a negative value. I might want to update my conditional formatting to make that stand out as well… perhaps make negative values bright blue?
Don’t put a separating blank row between your header row and the first row of your data. Leave them next to each other. You can insert blank rows above your header row if you want to put other heading information in there… like a title for your sheet.
If you want complex information in here like employee first name and last name, don’t put it in the same field (column). It’s much easier later if you have the data in separate fields. Store first name and last name in different fields. Store address separate from city separate from state separate from ZIP code, for example. It’s much easier to put these fields back together later than it is to try and pull them apart! We’ll see how this stuff works later in a future Excel class when I get into advanced text string manipulation.
Another tip: keep only one list per sheet. Don’t try to put multiple lists on the same sheet. You can insert more sheets into your workbook. Use them.
Excel is fine for a few hundred or even a few thousand records, but if you start getting into tens- or hundreds-of-thousands of records, you really should consider moving up to a database program like Microsoft Access. A database program is designed for storing large volumes of information, whereas Excel is really better for analyzing smaller lists.
If you want to learn more, visit www.AccessLearningZone.com for our complete listing of Microsoft Access database courses.
Lesson 3. Data Entry Forms
In this lesson we’re going to learn about data entry forms. Forms are great if you want a simpler method of entering or editing data, or you want to give your spreadsheet to someone who doesn’t know a lot about Excel (like a secretary) and you don’t want him/her messing up your formulas and functions. Forms give Excel a database-like look and feel that some people prefer.
Let’s create a new list to track sales for each of our stores. Create the following header row:
Now, highlight the header row plus one more blank row below it.
Now click on Data > Form…
Excel gives us a warning message that says it cannot figure out which row in the list contains the column labels. Click OK to use the first row as labels and not data.
Now I get the data entry form. Notice I have a series of text boxes representing each field in my sheet and some commands like New, Delete, Previous, and so on. Go ahead and enter in your first record, pressing TAB to move from field to field.
I’m going to click on the New button. Excel inserts the record into the data list as a new row, and puts me on a new blank record.
Pressing ENTER after typing in a record is the same as clicking on the New button.
You can go forward and backward through your records using the scroll bar or the Find Prev(ious) and Find Next buttons. You can also use the up-arrow and down-arrow keys or ENTER and SHIFT-ENTER.
You can delete the current record by clicking on the Delete button. Be Careful! Once you delete a record using the form button you cannot undo it.
If you exit out of your form and go back in, make sure you have the whole list selected. Otherwise, you will not see all of your records. Alternatively, and even easier, just click on one of the fields inside the list and go to Data > Form. Excel should correctly guess the dimensions of your form provided you didn’t leave any extra blank rows or columns (like I told you not to!)
TIP: If you type in CTRL – “ (that’s control-double-quotes) in a field, the data entry form will bring up the last value that you typed into that field.
Let’s insert a calculated field in our data list. How about the profit? The profit is simply the sales minus the expenses.
I’ll also add a little bit of color to my header row (yes, I’m a format nerd). Let’s go back into the data form and notice that Excel shows you the calculated field, but you can’t change it here. This is perfect so your secretary can’t mess things up.
Reminder: if you’re working with ZIP codes or Social Security Numbers or other numbers you want to save in your sheet with a leading zero, make sure to format your field as text.
You can also use your data entry form for searching for data. Click on the Criteria button.
Notice how the button changes to say Form and the word “Criteria” appears at the top. Your data also disappears. Don’t worry – you’ve switched over into search mode. Now, I want to find all of the Buffalo stores. So go to the Store field and type in “Buffalo”.
Now click on the Find Next button. You switch back to Form view (out of Criteria mode) and the first Buffalo record is displayed in the form.
Click on Find Next again to find the next record (Find Prev to go back to the previous record). You can see how this is helpful for a novice user. If you want to change your search criteria, just click on the Criteria button again. You can search for managers whose names begin with the letter “J” by just typing a “J” in the Manager field.
Type in “J” in the Manager field and “buf” in the Store field to find stores where the manager starts with “J” and the store starts with “buf”.
You can clear all of the criteria by clicking on the Clear button.
If you want to return to the form, click on the Form button. The Form and Criteria button are interchangeable… they switch back and forth (two toggle states).
If you know about the Wildcard Characters * and ? (asterisk and question mark) you can use them in your form criteria.
For example, if you search for Jon* you will get all names that begin with the letters “jon” with any number of characters after them. If you search for *Jon you will get anything that ends with the letters “jon”.
Rick’s Tip: I didn’t mention it in the video, but you can also put the asterisk in the middle of your criteria. For example, if you search for To*l you will get any words that begin with “To” and end with “l” like Tool, Total, Toll, etc.
You can also use the ? wildcard character which will give you one character per ? that you use. For example, Jo?n will yield John and Joan but not Jonathan. You could use T??l to yield Tool and Toll but not Total.
Finally, you can use the Restore button to cancel the editing of a record if you’ve made changes and decide you don’t want to save them. Do this before hitting ENTER or New.
Lesson 4. Sorting
We covered Sorting in detail in one of our previous lessons, however I want to review a couple of sorting topics here again, and also show you some advanced sorting techniques and how they apply to data lists.
Before we get to sorting however, take a look at a different feature. You know that if I highlight a block of cells, you will see the Sum of those cells on the status bar at the bottom of the screen.
Here’s something new. Right-click on that Sum and select a different function, like Average.
You’ll see the Average in the status bar now..
You will also see Count and Count Nums. The Count function will show you how many cells actually have data in them, whereas Count Nums shows you the number of cells that have Numeric values in them. For example, if you highlight the entire column and you have 15 rows plus a header row, the Count function will return 16, and the Count Nums function will return only 15.
The Max and Min functions are also in this list which you know show the maximum and minimum values in a range of cells.
Now let’s talk about sorting. Click somewhere in your list, and then click on Data > Sort.
Let’s sort the list by Month, Ascending. Click OK.
Notice what happens. Excel sorts the month names alphabetically, putting April before February. Excel doesn’t know that these are month names and that they come in a particular order.
Let’s go back into Data > Sort. Click on the Options button.
You’ll see a box that says “First key sort order.” What is this? Well, drop it down and you’ll see some Custom Lists. In the custom lists, you’ll see days of the week, months of the year, and abbreviations for both. Pick the one you want (months – full names).
I will teach you how to create your own custom lists in a future class. For now, click OK. Case Sensitive indicates whether “January” is different from “january”. For now we don’t care – leave it unchecked. You can also indicate whether you’re sorting across rows (top to bottom) or columns (left to right). In this case, we’re top to bottom. Click OK twice. Notice your months are now sorted correctly according to the custom list.
As a quick review, you can also sort by multiple columns… like Month, then by Store (where the months are identical), then by Manager (where both previous fields are identical). You can also indicate whether or not your list has a header row or not (ours does).
Can you sort by more than 3 fields? Yes you can, but you have to do it in more than one sort operation. If you wanted to sort by – for example – the first four fields in order, just sort the last 3 fields first, then run another sort on the first 3 fields. Since fields 3 and 4 are already sorted in relation to each other, it should work out the way you want.
If you want to be able to temporarily sort a list, but need to be able to go back to the way it was originally sorted, you can insert a new row with numbers counting up from 1. This index field will indicate the way that your list was originally sorted.
Then you can go ahead and resort your list…
And to go back to your original sort, just sort the list based on your index field (your original order column).
Text is sorted alphanumerically which means values with numbers at the beginning will come first, before the letter “A”. Take this list for example (no, you don’t have to type this in if you don’t want to).
Notice when I sort the column, the value with the SPACE in front of it came first, followed by the exclamation point, followed by the ampersand, followed by the data with leading numeric digits, followed by the letters. That’s an alphanumeric sort.
Blank cells will all sort together – usually last in your list.
Here’s what Excel does with a list where the values have spaces in them…
Look at the first letters – all Ms. The second letters, the “a”s followed by the “c”s. The third letters are then sorted where the second letters are identical… and so on.
If you’ve got a column of text labels that are a mixture of letters and numbers like this, you may not get what you want. This is because “A10” comes alphanumerically before “A2”.
Lesson 5. Customizing Toolbars
In this lesson we’re going to learn about customizing your toolbars. Now, this really isn’t part of data analysis (and probably should have been covered in a 100-level class) but if you know how to make your own toolbars, or at least put your own buttons on the existing toolbars, data analysis is much easier. A lot of the functions we’re going to need to get at are buried deep in menus and having the button available on a toolbar speeds things along.
Now, toolbars in Excel consist of menu bars (pull-down menus), button bars (actual toolbars), and the right-click shortcut menus. Excel treats them all the same.
Toolbars consist of one or more commands. Commands can be icons, text (like the Paste Special button), a combination of both, or even drop-down boxes, like the Font drop-down.
You can get a listing of all of the different toolbars that are available by clicking on View > Toolbars, or by right-clicking on any one toolbar.
To turn one of the toolbars on (or off) just click on it, and the toolbar will appear. It may be floating (like the Borders toolbar pictured below) or docked to the top, bottom, or sides of the window (like the standard and formatting toolbars).
We’ve also seen in previous classes how we can move the toolbars around by clicking and dragging on the little handle (the straight up-and-down bar) on the right side of a toolbar.
Now, to Customize your toolbars, click on View > Toolbars > Customize. You can also right-click on any toolbar and select Customize.
The Customize dialog appears.
You can click on the check boxes to turn on/off any toolbar you want to see/don’t want to see.
The Commands tab shows you all of the different commands (usually buttons) you can put on your toolbars. They’re grouped together based on where the commands usually appear on the regular menus (like File > New, File > Open, File > Save, etc.)
The Options tab has some different options on it that we’ll cover in a few minutes.
Now, with the Customize dialog box still open, click and drag it over to the side (out of the way) and notice how you can move buttons on your existing toolbars around by just clicking and dragging them. Here, for example, I’ll move the Open button to the right of the Save button.
You can create your own little button group separators (those little lines) by just clicking and dragging a button just slightly to the right of itself.
You can delete a button by dragging it off of the toolbar. Here I’ll delete the Paste button. Notice the “X” on my mouse pointer.
You can move buttons from one toolbar to another by simply dragging them. Here I’ll move the Cut button to the formatting toolbar.
If you want to Copy a button from one toolbar to another, hold the CTRL key down while you drag it.
You can also right-click on a button to delete it.
Now, if you’ve completely messed up your toolbar and you want to reset it to its factory default settings (they way Excel was installed out of the box) just come into the Customize dialog, click on the toolbar, and then click on the Reset… button.
You’ll be asked if you’re sure you want to reset the changes. Click OK. The toolbar will be reset back to its original settings.
Keep in mind that the menu-bar items also work the same way. You can click and drag menu items around and play with them.
Now, let’s add a Print… button to my toolbar next to the normal Print button. What’s the difference? The Print button that you get by default sends the current sheet right to your default printer without any options. The Print… button that you can add will give you the Print Dialog box that lets you change your printer settings, paper settings, select a different printer, etc.
On the Commands tab, click on the File category, and then find the Print… button under the list of Commands.
I’m going to click and drag that Print… icon right up to the toolbar and drop it next to the other printer button.
Now, both buttons look the same. How do we tell them apart? Right-click on the new button and change the button face from Default Style (which is checked) to Image and Text.
Now look what I have. The original button is just a printer icon. The new button has the icon and the text Print… on it.
You can also select either of the Text Only options if you want. I personally like the icons. Text Only (Always) means you’ll always see just the text. Text Only (in Menus) means that you’ll see text in the drop-down menus, but the icon on toolbars. Now, let’s do the same thing again by adding Paste Special… to our toolbar.
Now, editing the existing toolbars is OK, but the real power is in making your own. Let’s click on the Toolbars tab and click on the New button. Type in a name for your toolbar. I’ll call mine “Ricks Toolbar”.
And now you’ll see a little, itty, bitty, tiny blank toolbar that pops up. It’s all ready for you to drop buttons on it.
Now, let’s click and drag those buttons (Print… and Paste Special) off of the standard toolbar, and onto the new Ricks Toolbar.
I’ll also bring in some copies of other buttons I use often using the CTRL key as I drag them. For example, I’ll bring in the Save button. I’ll also copy the bold and format painter buttons.
You can also dock your toolbar under the other ones by dragging and dropping it.
If you don’t like the name of your toolbar, click on it in the Toolbars list and click on the Rename button. You can also delete a toolbar by clicking on the Delete button.
There’s also a button labeled Attach. You can use the Attach button to attach your toolbar to the Excel Workbook that’s currently open. This way, if you send the workbook file to someone else, you can also send them copies of your custom toolbars. Just click on the Attach button.
You’ll see a list of all of the custom toolbars in your workbook. Just click on the one(s) you want to copy and click on the COPY button. Those toolbars will be attached to your workbook.
Now, if you send this workbook to someone else (or yourself at home) you’ll have the toolbar available.
NOTE: If you change the toolbar again on your system, you’ll have to re-attach it to the workbook to send out the newly changed toolbar. Your local copy will take priority over the attached toolbar of the same name.
Now, if you don’t like the way a button looks, you can right-click on the button and select Change Button Image.
I’ll pick the disk with the blue arrow pointing in to it. Notice how the icon has changed.
Now, let’s get rid of the text. I’ll right-click on the button and select Default Style. Since the buttons are different, I know which one is Save and which is Save As.
You can also change the button image. Right click on it and select Edit Button Image.
You’ll get the button editor. You edit the individual pixels of the button by clicking on a color from the color palette and then clicking on the image. I’ll change the blue arrow to a red arrow. Select Red from the colors and then just click and drag to start drawing over the arrow image.
I’ll change the color of the disk to green. You can click on the Erase color to erase pixels. You’ll see a preview of your button image towards the bottom.
TIP: I didn’t cover it in the video, but the Move buttons are to slide your whole image up, down, left, or right.
Notice my new button.
Now, you can copy a button image from one button to another. Just right-click on the button and select Copy Button Image. I’ll copy the image from the Save button.
Then right-click on the button you want to paste it on and select (guess what) Paste Button Image. The image will appear on the button.
Now that’s no big deal, but here’s the cool part… you can use your favorite graphic editor (like Microsoft Paint) to create your own button images! Here’s MS Paint (which I cover in detail in my Windows classes). I’ll quickly draw an image. I’ll select a square area of it and copy it to the clipboard.
Now, switch back over to Excel. Right-click on a button and Paste Button Image. Notice that the image that was stored in the clipboard is now on your button!
You can right-click on the button and select Reset to make the image blank (in this case). You can also change the text that appears on a button, or on the Tooltip Text that pops up when you hover your mouse over the button. In this case, the text is set to “Save &As…”. Now the ampersand character indicates that the following letter is the ALT-hotkey for this button. So if I press ALT-A, then it’s the same as clicking on this button.
I’ll type “Save with different filename” into this box. Press Enter.
Now close the Customize Toolbar dialog box and then hover over that button. Notice the Tooltip text.
Go back into Customize. We’ll cover Assign Hyperlink and Assign Macro in a different lesson. We’re actually going to have a whole series on Macros programming.
Let’s take a look at the Options tab.
The first item, Show Standard and Formatting toolbars on two rows separates the toolbars into two rows. Remember back in Excel 101 I had you separate them? Well, if you uncheck this box, it puts them back into one big toolbar. I don’t like that.
Always show full menus forces the Excel menus to always open up completely – instead of hiding the features you don’t use much. Show full menus after a short delay causes them to open up fully after a few moments.
Reset my usage data makes Excel forget which menu items you’ve used – and returns Excel to it’s initial state as far as the menu items go. Remember, as you use menu items more often, it causes Excel to put those in the initial menu for you.
You can optionally turn on Large Fonts which is good for the visually impaired. List font names in their font makes the fonts show up in their actual font styles when you drop down the font box. Show ScreenTips on toolbars is another name for those Tooltips I mentioned earlier – the helper text that pops up when you hover over a button.
You can also change the Menu Animations on this screen as well. As you can see, there’s nothing earth-shattering on this menu.
Now, if you look way over to the right side of the toolbars, you’ll see little down arrows.
Click on these down arrows to open up a menu. For the standard and formatting toolbars, you’ll see the Show Buttons on One Row option that we talked about earlier. You’ll also see a quick Add or Remove Buttons menu.
If you click on Add or Remove Buttons, you’ll see that you can go into Customize mode here. You’ll also see the buttons that are normally on this Standard toolbar. You can check them on or off to show/hide the buttons.
You’ll see the same thing for the Formatting toolbar.
Here’s another tip: you can actually move buttons around without being in Customize mode. Just hold down the ALT key and click-and-drag them around.
Some of the buttons have dual roles. For example, if you click on the Sort Ascending button with the SHIFT key held down, it acts the same as the Sort Descending button. If you can remember this, you can remove the Sort Descending button from your toolbar to save yourself some space.
Here’s a list of all of the buttons (that I know of) that play a dual role like this:
· Sort Ascending & Sort Descending
· Print & Print Preview
· Open & Save
· Increase Indent & Decrease Indent
· Underline & Double Underline
· Center & Merge & Center
· Increase Decimal & Decrease Decimal
Now some people have emailed me saying that they spent all kinds of time designing custom toolbars at home and they want to copy them all to the office – or they have gotten a new computer and want to copy all of their toolbars to it.
If you want to save all of your toolbars, click on your Windows Start Button and then Search (or Find if you have an older version of Windows). Look for a file on your hard drive called EXCEL.XLB. This file contains information on your toolbars. Just copy this file to your other system and put it in the same folder that it exists on your current system.
For example, on my computer, the file is located here:
C:\Documents and Settings\Administrator\Application Data\Microsoft\Excel
So if I were to copy it to another computer, I’d need to make sure to put it in the same folder. If my username was different on that computer, I’d have to adjust accordingly (instead of Administrator, it might be Richard, for example). And this depends on your operating system as well. Windows 2000 (like I use) would be different from Windows 98, for example.
· Creating Data Lists
· Formatting Data Lists
· Data Entry Forms
· Sorting Lists
· Customizing Toolbars
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: