Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Excel 104 Handbook
By Richard Rost   Richard Rost on LinkedIn Email Richard Rost   17 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 104
Course Handbook Supplement

By Richard Rost


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


First Printing 7/9/2005
Copyright 2005 by Amicron Computing
All Rights Reserved


Welcome

Welcome to the 599CD Microsoft Excel 104 Handbook. This course follows Excel 103.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Excel 104. 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. Format Cells: Number Formats 4
Lesson 2. Format Cells: Alignments 17
Lesson 3. Format Cells: Fonts 29
Lesson 4. Conditional Formatting 37
Lesson 5. Spelling, Find & Replace, AutoFormat 44
Lesson 6. Financial Calculator 50
Review 64




Introduction

Welcome to Microsoft Excel 104, brought to you by 599CD.com. I am your instructor, Richard Rost.





Objectives for today’s class:

· Format Sales – Sales Log
· Conditional Formatting
· Spelling, Find & Replace, AutoFormat
· Financial Calculator
· Protect Worksheets


Pre-Requisites: Microsoft Excel 101 through 103. We will assume you have taken at least Excel 101 before this class.

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. Format Cells: Number Formats

Begin by loading Excel. Start with a blank sheet. In cell A1 I will type in “Sales Log”. In cell A2 I’ll type in “December 2003” but remember to start off A2 with a single quote so Excel doesn’t convert that into a date. We covered this in a previous lesson.




Set up the following columns: Date, Customer, Type, Qty, Unit Price, Subtotal, Tax Rate, Tax, Total, Payment Type.




I’ll type in “12/1” for the date of the first sale. Excel converts this to “1-Dec.” I hate this.





Right-click on that cell and select Format Cells.




The Format Cells dialog appears. Make sure you’re on the Number tab. There are a ton of different formats. General has basically no specific format.





Number allows you to specify the cell is a number. You can specify how many decimal places to use, whether or not to show a “thousands” separator (like 1,000 instead of 1000), and how negative numbers should be displayed.




Currency is very much like Number. It also allows you to specify the currency symbol (like $) that gets displayed. The Accounting style is similar, but it lines up the $ symbols to the left of the column. I’ll show you an example later.





The Date format allows you to specify how dates should appear. For our sales log, I’ll pick the “3/14” format.




The Time format allows you to specify that this cell contains a time value, and how to display it.





Percentages just let you pick how many decimal places are displayed.




Fractions allow you to specify how much precision to use.




Scientific allows you to specify how many decimals to use with scientific notation.



Text is just for plain text with no options. Special allows for some special data types, such as ZIP codes, phone numbers, and social security numbers. Excel will automatically format these data types properly.




Custom allows you to specify a custom format, which we’ll talk about a little later.




For now, go back to Date and select the 3/14 format. Click OK.




Now, my date displays on the sheet as 12/1. Notice in the formula bar it still shows the complete date of 12/1/2003.




I’m going to left align the date in the cell.




Notice if I type in another date in the next row, I have the same problem.





We can use the Format Painter to apply the date format we want to the entire column. Click on cell A5. Click on the Format Painter.




Now, click on the header for column A. This will apply the format to the whole column.




Notice how if I type in another date, we get the format we just applied.





You could optionally highlight the column first, then right-click on the column header and select Format Cells…




You can then specify whatever format you want for the entire column. Me, personally, I usually just set one cell and then format-paint the whole column. Let’s enter in some other data. I’ll put in the customer name and the type of product they purchased. Notice I’m resizing columns as I go along (we learned how to do this in Excel 101).





I’ll type in that they purchased a quantity of 2500 units. Let’s right-click on that cell and select Format Cells. I’ll change this to Number with one decimal place, and Use 1000 Separator will be checked on.




Notice the format.




For Unit Price I’ll type in “.13” for thirteen cents. Notice Excel changes it to “0.13”. Let’s set the format here (right click, Format Cells). Let’s set this to Currency with 2 decimal places. I am going to turn off the symbol (set it to None).





Now, the Subtotal I can calculate by multiplying the quantity by the unit price.




Again, I’ll set the format for this cell to Currency with 2 decimal places and no symbol.




The Tax Rate is the sales tax rate for this customer (or for this sale). Let’s say this sale was 8%. I’ll type in “0.08”. Remember that percentages are fractions of one. 100% = 1. 10% = 0.10.




You could right-click and format cells to change this cell to a percentage OR you could click on the Percent Style button on the toolbar.




You can increase the decimal places either using Format Cells or the Increase/Decrease Decimal buttons on the toolbar.

The actual tax can be calculated by multiplying the subtotal by the tax rate.




I’ll use the Format Painter to copy the currency format I’ve been using from the Subtotal field to the Tax field.




The total is simply the subtotal plus the tax.




Finally for Payment Type I’ll just type in “Cash”.





I’m going to Right Align all of the cells with numbers and currency in them.





Lesson 2. Format Cells: Alignments

Let’s type in another sale. How about ABC Roofing who purchased a Floppy Drive. The quantity is 1. The unit price is 25 dollars.




Notice the formats aren’t changing. We didn’t copy the formats down the column. So, click on the previous sale item, click on the format painter, and then format the entire column like we did in the last lesson with the dates.




You’ll have to do this once for each column you want to copy, but then you’ll never have to do it again for this sheet.


For the calculated fields, we can use the AutoFill function and just drag it down a couple of rows.




Yes, you get some zeros, but it’s better than copying the formula for every row. In a future lesson I’ll teach you how to make the zeros not display.




Do the same thing for the tax rate, tax, and total.




I’ll just type in “Cash” for that second sale payment type.

I’ll put in one more sale: Gryfon Painting, CD Burner, qty 2, $54.25 each. Now, notice if I try to AutoFill down the group of calculated fields, that Tax Rate goes crazy.




Since Tax Rate really wasn’t a calculated field, AutoFill tried to increment it for us. You have to keep a close eye on AutoFill. Sometimes it will do weird things. Go ahead and delete all that.





Let’s put in a SUM for our subtotals. I’ll use the AutoSum button.




Let’s do the same for Tax and Total.





Let’s add a little bit of color and some bolding…




Right-click on the first quantity value (2500) and select Format Cells.





Click on the Alignment tab. Notice the horizontal alignment is currently set to Right. There’s also a Vertical alignment which you can use to place the text to the top, bottom, or middle of the cell vertically.




Hit Cancel. Let’s make Row 1 really tall. Make the text larger, and bold it.




Now, right-click on cell A1 and go to format cells. On the alignment tab, change the Vertical setting to Top.





Hit OK and notice that “Sales Log” appears at the top of the row.




Go to Sheet2. In cell A1 type in “Blank CDs.” Type in a long description for the product in A2. Make the row taller.




Now, on cell A1, right-click, format cells. Change the horizontal alignment to Left and the vertical alignment to Top.





Now here’s what you get…




Notice how the description in B1 spills over into the rest of the row. Let’s force it to stay inside it’s own cell. Right-click on B1, Format Cells, Alignment Tab. Check on the Wrap Text box.




Now make the column a little wider. Notice the text stays inside it. Now just go back in and set the vertical alignment to Top so it lines up with the text in A1 nicely.





Here’s a new trick. Highlight all of row 4 (our header row).




Right-click, Format cells, Alignment tab. In the Orientation section, click and drag that little word “Text” with the line next to it to adjust the angle of your text.




Click OK. Notice your text.





You can do a mixture… some at an angle, and some straight up and down.




Let’s move the Payment Type column. I’m going to click on the column, hit CTRL-X to cut it out. Then, right-click on column D and select Insert Cut Cells. This is a great way to move columns.





That will move the column.




You can shrink the text in a cell to fit into whatever size the cell is. Click on cell A1. Right-click, format cells, alignment tab. Click on Shrink to Fit.




Click OK. Notice the text has shrunk to fit inside the cell dimensions.




If you later widen or shrink the cell, the text will resize to fit accordingly.




Now, highlight cells A1 through J1. Click on the Merge and Center button.




Notice how the contents of cell A1 has been spread out over than range and centered. That’s a good way to place titles across the top of a sheet. I’ll do the same thing for the date.




Notice that if you look at the properties for A1, the alignment tab shows that the cells are merged.





Lesson 3. Format Cells: Fonts

Go into Format Cells and take a look at the Font tab.




Most of these options can be changed with the formatting toolbar. There are a few options that you have to come into here for. Take the Underline option, for example. You can specify different styles of underlining (single, double, accounting, etc.).





There are also some options on here for strikethrough, superscript, and subscript.




Now let’s take a look at the Border tab.





The three main border buttons are none, outline, and inside. None erases borders from the selected cells. Outline draws a border outside the selected cells. Inside draws the inside borders for the selected range of cells (it’s disabled if you only have one cell highlighted).




Let’s cancel out of this and select a larger range of cells… anywhere, just to demonstrate.




Right click, format cells, border. Notice how you can click on the outline and inside buttons to turn on the appropriate borders.





You can turn individual borders on and off by clicking on them in the preview window. Here I’ll turn on the left and bottom outside borders only…




Here’s what it looks like:




Go back into format cells. You can turn on diagonal borders by clicking in the middle of the preview window “cells” or the little diagonal buttons.





There is a color palette to change the border color.




You can also change the thickness or give the border style a pattern with the Style section.





I’ll click for a thick blue inside border.




Now hit OK to take a look at it…




So as you can see, you can do a lot with different border styles. How about a red, dashed, outside border…





Here it is…




Let’s move on to the Patterns tab. You can pick a solid color to shade in your cells with (like a background color), or you can select one of the different patterns that are available – like diagonal lines.




Keep in mind the pattern lines will take whatever color you currently have set.

I’ve picked blue diagonal lines.




Now, if you decide you no longer want these borders and colors, the easiest way to get rid of it all is to click on an empty (virgin) cell and use the Format Painter and just paint over the colored cells.




That will wipe all of the formatting out. See, the format painter can be used to copy a format, or remove a format.




The last tab is called Protection. This feature allows you to lock specific cells so that people can’t change their values. We’re going to cover this feature later today in lesson 6.


Lesson 4. Conditional Formatting

Conditional formatting allows you to change the format of a cell based on the data in the cell. For example, I would like to make my sales log so that if the order total is over $300, the total appears in red. So, let’s select all three order totals and click on Format > Conditional Formatting…




Change the drop-down box that says “between” to “greater than or equal to.”





Type “300” into the data box. We’re saying “if the cell is greater than or equal to 300, then apply this format.”




Now let’s specify the format… click on the Format… button.




Let’s say we want to Bold it and change the color to Red.





Now, click OK twice. Notice what happens. The cell that is over $300 becomes formatted.




Notice the conditional formatting is dynamic. If I change the quantity of that order from 2500 to 10, the order total drops below 300, and the formatting goes away…




Likewise, if I change the quantity of the 2nd order to 100, the total goes over $300.





You can specify additional conditions as well. Highlight that same range again, and go to Format > Conditional Formatting once more.




Click on the Add>> button to add a 2nd condition.




Now we have Condition 2 available.





Let’s say if the value is less than or equal to 50, make the format blue.




And there we go…




You can also use conditional formatting to compare two cell values. This works well for showing you data that’s out of the ordinary. For example, let’s say that our normal sales tax is 8%. Put that in cell B11 as shown:




Now, highlight all of the sales tax fields. Click on Format > Conditional Formatting…




Select Not Equal To for the condition. Instead of typing in a value, click on the little selector button way at the end of the data field button…




Click on the sales tax field we created.




Then click on the little button again.




Notice how the value is now in that data cell. Now set the format to Bold and Orange.





Click OK. Notice now that if you change one of the sales tax rates from 8% to something else, Excel will flag that value for you – just so you can see that it’s different.





Lesson 5. Spelling, Find & Replace, AutoFormat

You can spell-check your spreadsheet by clicking on Tools > Spelling or hitting F7 on your keyboard.




NOTE: I cover spell checking in much more detail in my Microsoft Word course.

The first word that Excel doesn’t recognize comes up: Gryfon.




Excel makes some recommendations at the bottom of the window. We can click on Ignore Once to ignore just this word, Ignore All to ignore all instances of this word everywhere on the sheet, Add to Dictionary which will add this word to our custom spelling dictionary (so Excel never again bothers us about it), Change which will change this word to the currently selected suggestion, or Change All which change all of this word to the selected suggestion.


Now, I get off the phone with the company, and it turns out that this “Gryfon” is a proper noun. It’s the name of the owner of the company, but I’ve spelled them all wrong! So I need to be able to change all of them without retyping them all, and spell-check won’t help.

First, come up and click on cell A1 to move our highlighted cell to the top of the sheet. Now, from the menubar, click on Edit > Replace…




The Find and Replace window appears.




NOTE: Again, we cover this feature in much more depth in our Microsoft Word classes. It’s presented here only as a brief overview. Since most students learn both Word and Excel, it would be highly redundant to cover it in great depth here as well.

Type in the wrong spelling for Gryfon in the “Find What” box. Type in the correct spelling in the “Replace With” box. Then click on Replace All.



Now, if you click on the Options>> button, you will see there are additional options for finding and replacing. For example, you can specify certain formats to search for, or to change. You could, say, find all words that are Arial, 10point, Bold, and change them to Times New Roman, 12point, Italics. I’m not going to walk through this now, but it’s real easy to figure out.




Match Case means that capitalization matters (“Gryphon” is not the same as “gryphon”). Match entire cell contents means that you have to match the entire cell, not just part of it. Within allows you to specify where to search (in a sheet, workbook, a selected range, etc.). Search By allows you to specify whether to go across rows, or down columns. Look in allows you to specify whether or not to search inside of formulas.

We’ll cover these features in more depth in future lessons, as we have a need to use them.


Using AutoFormat allows you to quickly make your sheet look nice. Click on Format > AutoFormat.




The AutoFormat window allows you to pick a “look and feel” from a list of pre-designed layouts.





I’ll click on the Classic 2 format, and click OK. Notice the format is applied to my sheet.




Feel free to experiment with the different formats. TIP: If the AutoFormat doesn’t appear to be working, make sure you have one of the cells inside of the sheet itself (like B6) selected. Otherwise, Excel may have a hard time figuring out the edges of your sheet. You may get this warning:




It’s actually BEST to actually highlight the range of cells you want to apply the AutoFormat to.


If you click on the Options button on the AutoFormat window, you’ll get some options…




This will allow you to specify whether or not formats apply to specific features… such as borders, fonts, etc. In other words, if you uncheck the font box, for example, the AutoFormat will not change fonts. I’m going to cancel out of this… and I’m also going to hit Undo a couple of times to go back to what I had my sheet initially formatted as…





Lesson 6. Financial Calculator

In this lesson we’re going to learn how to build a loan calculator. Lets go to a blank sheet. I’ll click on Sheet3. Let’s say we’re buying a house worth $150,000. Type in the following:




We’re going to put a down payment on the house of $30,000.




So the amount we’re financing (taking a loan out for) is the value of the house minus the down payment:




Next, we need to know what our Interest Rate is. What’s the bank charging? Let’s say it’s 6%.





How many years are you financing it for? Let’s say 30 years.




TIP: By the way, if you’re following along with me on your own spreadsheet, try to keep your data in the same cells as me… that way when we get to the tricky stuff in a few minutes, you won’t have to scramble to figure out what the formulas mean.

Now, Excel is going to want the length of the loan specified as months not years (that’s what the function we’re going to use wants). So, let’s represent that in months as well…




TIP: Yes, for those of you who are paying attention, I should have put =B5*12 in that cell. I’ve gotten a lot of emails about this. We’ll actually fix it later… keep watching. Yes, there is a method to my madness.


Next comes the big nasty PMT function. This is a built-in Excel function that figures out the monthly payment. Now, you don’t have to remember how this function works! I’m going to show you what data to plug into it, and if you want to use it again in the future, refer here to your notes. You don’t have to memorize this. Just be familiar with how it works. Here we go…

The PMT function wants three pieces of information (and two optional pieces we won’t need today).




The first piece of information, rate, is the interest rate divided by the number of payment periods in a year. In this case, B4/12… the interest rate is in B4, and there are 12 periods (months) in a year.




Type in a comma. Next, we need to know the total number of payment periods (in this case, months) that our loan covers. That’s why we needed to know the total number of months for this function. In this case, the value is in cell B6…





Type in a comma. The last piece of data is the total amount that you’re financing… the loan amount. So that your answer isn’t negative, type this in as a negative number. In this case, the amount of the loan is in cell B3. So we’ll enter –B3 so it’s negative.




Complicated? Yes, a little. Don’t worry about it. You don’t have to remember the specifics of how it works… Yes, even I have to look in my notes and I’ve been teaching this for many years. Just be familiar enough with the function to recognize how it functions. Close the parentheses and press ENTER.




There’s your monthly payment… $719.46. Not too bad.

Now that we know our monthly payment, we can figure out the total amount of our payments. This is the monthly payment multiplied by the number of months.




This yields $259,005.83. Wow! That’s a lot for a $150,000 house. Plus, we didn’t even figure in our down payment… add that in…




Now the figure is $289,005.83. How much money is the bank making? That would be our total payments minus the value of the house…





So the bank is making $139,005.83. I’m in the wrong business! Let’s take a moment to format all of the dollar values. I’m going to select them all using the CTRL key (remember, click on the first one, hold the CTRL key down, and click on the others…)




Now click on the Currency Style button.





Let’s do some comparison shopping. I’m going to copy (CTRL-C) and paste (CTRL-V) the contents of all of column B into column C.




And now you can change and compare values. For example, let’s see what the numbers look like if we changed from a $30,000 down payment to a $10,000 down payment.




Notice how I’m paying more per month and a lot more in total interest over the course of the loan. Bottom line: it’s better to pay a larger down payment and finance less of the house.

What about changing the length of the loan. I’ll change from 30 to 20 years. Notice the number of months didn’t change. That’s the problem I mentioned earlier. I typed the formula for the months in as 30*12. We’ll fix it now.




Fix it for both columns.




Now, here’s the benefit of a shorter loan… the monthly payment goes up, but look at the money you’re saving in interest over the long haul…





Want to see exactly how much money you’re saving…




Turns out you save over $52,000 by chopping 10 years off the loan and only paying about $130 more a month. Can you swing it in your monthly budget? If so, take the shorter loan. You’ll own the house sooner and pay a lot less in interest.

Alright, let’s say you’re a banker. You want to make this sheet look pretty and allow your customers to play with it, but you don’t want them messing up your functions. You can lock specific cells so they can’t change things – like your PMT function.

Let’s begin by inserting three blank rows at the top of the sheet. Highlight rows 1 through 3. Right-click on them and select Insert.





This will insert 3 blank rows (the number of rows we had selected – neat trick, eh?)




Type in the name of your firm in cell A2.




Make it look good.





Now, I’m going to highlight the fields that I want the customer to be able to change. I’ll use the CTRL-key trick I showed you earlier.




Let’s color those cells with a background of yellow – to visually indicate to the user that they’re allowed to change those cells.





Now you can lock all of the cells in your spreadsheet by turning on sheet protection. This will prevent the user from modifying any of the cells.

By default, all of the cells in a spreadsheet are locked, however sheet protection is turned off. So right now, users can change whatever they want because the cells are locked, but there is no sheet protection.

If I were to turn on sheet protection right now, users would not be able to change any values. So, what we need to do is unlock those cells that we want them to be able to change, and then turn on sheet protection.

Right-click on these selected cells now and click on Format Cells.




On the Protection tab, check off the Locked checkbox. Notice the warning that says you have to turn sheet protection on in order for this to work!





So now the yellow cells are Unlocked and the rest of the cells are Locked. If we now turn sheet protection on, you’ll see it work. Click on Tools > Protection > Protect Sheet.




You can optionally type in a password if you want. This password will be required to unlock the sheet. The list of options on the bottom specifies what they can and cannot change in the protected cells.




Now, if you try to change anything but the yellow values, you get an error message.





To unprotect your sheet, go to Tools > Protection > Unprotect Sheet.






Review

Review topics.

· Format Cells – Sales Log
· Conditional Formatting
· Spelling, Find & Replace
· AutoFormat
· Financial Calculator
· Protect Worksheets



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 on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Excel 104 Handbook
Get notifications when this page is updated
 
 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
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
PCResale.NET
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
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 9:33:48 AM. PLT: 0s
Keywords: exceh handbook  PermaLink  Excel 104 Handbook