Fulton AdultAlternative Education presents Microsoft Office Excel 2003

Fulton Adult/Alternative Education presents: Microsoft Office ® Excel 2003 Training ® Enter formulas

Course contents • Overview: Simple calculations in Excel • Lesson 1: Get started • Lesson 2: Use cell references • Lesson 3: Simplify formulas by using functions Each lesson includes a list of suggested tasks and a set of test questions. Enter formulas

Overview: Simple calculations in Excel After you try Excel, you’ll never go back to a calculator. In this course you’ll learn how to add, divide, multiply, and subtract by typing formulas into Excel worksheets. You’ll also learn how to use simple formulas that automatically update their results when values change. Enter formulas

Course goals • Do math by typing simple formulas to add, divide, multiply, and subtract. • Use cell references in formulas, so that Excel can automatically update results when values change or when you copy formulas. • Use functions (prewritten formulas) to add up values, calculate averages, and find the smallest or largest value in a range of values. Enter formulas

Lesson 1 Get started

Get started In this lesson, you’ll learn how to use Excel as your calculator by typing simple formulas into cells. A budget worksheet needs an amount in cell C 6. You’ll also learn how to total all the values in a column with a formula that updates its results if values change later on. We’ll start with the example worksheet shown in the picture. Enter formulas

Begin with an equal sign Two CDs purchased in February cost $12. 99 and $16. 99. The total of these two values is the CD expense for the month. Typing a formula in a worksheet You do math in Excel by typing simple formulas into cells. Excel formulas always begin with an equal sign (=). Enter formulas

Begin with an equal sign Here’s how to add 12. 99 and 16. 99 in cell C 6: 1. Type the formula =12. 99+16. 99. The plus sign (+) is a math operator that tells Excel to add the values. Typing a formula in a worksheet 2. Press ENTER to display the formula result, 29. 98. Enter formulas

Begin with an equal sign Here’s how to add 12. 99 and 16. 99 in cell C 6: 3. The formula appears in the formula bar near the top of the worksheet whenever you select cell C 6. Typing a formula in a worksheet Enter formulas

Use other math operators Math operators Add (+) =10+5 Subtract (-) =10 -5 Multiply (*) =10*5 Divide (/) =10/5 Excel uses familiar signs to build formulas. To do more than add, you can use other math operators as you type formulas into worksheet cells. You start each formula with an equal sign and then use a minus sign (-) to subtract, an asterisk (*) to multiply, and a forward slash (/) to divide. Enter formulas

Total all the values in a column To add up the total of expenses for January, as shown in the picture, you wouldn’t have to type all those values again. Instead, you could use a prewritten formula, called a function. Using the Auto. Sum button to total column values Enter formulas

Total all the values in a column To get your January total: 1. Select cell B 7, and then click the Auto. Sum button on the Standard toolbar. The Auto. Sum button adds up all the values in a range of cells. Using the Auto. Sum button to total column values 2. A colored marquee surrounds the cells in the formula, and the formula appears in cell B 7. Enter formulas

Total all the values in a column To get your January total: 3. Press ENTER. This displays the SUM function result 95. 94 in cell B 7. Using the Auto. Sum button to total column values 4. Select cell B 7 to display the formula =SUM(B 3: B 6) in the formula bar. Enter formulas

Total all the values in a column B 3: B 6 is the information, called the argument, that tells the SUM function what to add. By using a cell reference (B 3: B 6) instead of the values in those cells, Excel can automatically update results if values change later on. Using the Auto. Sum button to total column values Enter formulas

Total all the values in a column The colon (: ) in B 3: B 6 indicates a cell range in column B, cells 3 through 6. The parentheses are required to separate the argument from the function. Using the Auto. Sum button to total column values Enter formulas

Copy a formula instead of creating a new one Sometimes it’s easier to copy formulas than to create new ones. In this example, you’ll see how to copy the January formula and use it to add up the February expenses. Copying a formula Start by selecting cell B 7, which contains the January formula. Then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears. Enter formulas

Copy a formula instead of creating a new one Next: 1. Drag the fill handle over cell C 7 and then release it. The February total 126. 93 appears in cell C 7. Copying a formula 2. After the formula is copied, the Auto. Fill Options button appears to give you some formatting options. Enter formulas

Suggestions for practice 1. Create a formula to add. 2. Create formulas for other arithmetic. 3. Add up a column of numbers. 4. Copy a formula. 5. Add up a row of numbers. Online practice (requires Excel 2003) Enter formulas

Test 1, question 1 What do you type into an empty cell to start a formula? (Pick one answer. ) 1. * 2. ( 3. = Enter formulas

Test 1, question 1: Answer = An equal sign tells Excel that a calculation follows it. Enter formulas

Test 1, question 2 What is a function? (Pick one answer. ) 1. A prewritten formula. 2. A math operator. Enter formulas

Test 1, question 2: Answer A prewritten formula. Functions are prewritten formulas, such as SUM, that save time. Enter formulas

Test 1, question 3 A formula result is in cell C 6. You wonder how you got the result. To see the formula, you do which of the following? (Pick one answer. ) 1. Select cell C 6, and then press CTRL+SHIFT. 2. Select cell C 6, and then press F 5. 3. Select cell C 6. Enter formulas

Test 1, question 3: Answer Select cell C 6. It’s that simple. The formula is visible in the formula bar near the top of the worksheet whenever you select cell C 6. Or you can double-click cell C 6 to see the formula in cell C 6. Then press ENTER to see the formula result again in the cell. Enter formulas

Lesson 2 Use cell references

Use cell references Cell references Refer to values in A 10 the cell in column A and row 10 A 10, A 20 cell A 10 and cell A 20 A 10: A 20 the range of cells in column A and rows 10 through 20 B 15: E 15 the range of cells in row 15 and columns B through E A 10: E 20 the range of cells in columns A through E and rows 10 through 20 Cell references identify individual cells or cell ranges in a worksheet. They tell Excel where to look for values to use in a formula. In this lesson you’ll see why Excel can automatically update the results of formulas that use cell references, and how cell references work when you copy formulas. Cell references Enter formulas

Update formula results Suppose it turned out that the 11. 97 in cell C 4 for video rentals in February was incorrect. A rental of 3. 99 was left out. Excel can automatically update totals to include changed values. To add 3. 99 to 11. 97, you would select cell C 4 and type this formula into the cell: =11. 97+3. 99 Enter formulas

Update formula results As the picture shows, when the value in cell C 4 changes, Excel automatically updates the February total in cell C 7 from 126. 93 to 130. 92. Excel can automatically update totals to include changed values. Excel can do this because the original formula =SUM(C 3: C 6) in cell C 7 contains cell references. Enter formulas

Update formula results If you had entered 11. 97 and other specific values into a formula in cell C 7, Excel would not be able to update the total. Excel can automatically update totals to include changed values. You’d have to change 11. 97 to 15. 96 not only in cell C 4, but in the formula in cell C 7 as well. Enter formulas

Other ways to enter cell references You can type cell references directly into cells, or you can enter cell references by clicking cells, which avoids typing errors. Selecting cell references to add a few values In the first lesson you saw how to use the SUM function to add all the values in a column. You could also use the SUM function to add just a few values in a column, by selecting the cell references to include. Enter formulas

Other ways to enter cell references Imagine that you want to know the combined cost for video rentals and CDs in February. You don’t need to store the total, so you could enter the formula into an empty cell and delete it later. The example uses cell C 9. Selecting cell references to add a few values Enter formulas

Other ways to enter cell references Here’s how to enter the formula: 1. Type the equal sign, type SUM, and type an opening parenthesis in cell C 9. 2. Click cell C 4, then type a comma in cell C 9. Selecting cell references to add a few values Enter formulas

Other ways to enter cell references Here’s how to enter the formula: 3. Click cell C 6. Then type a closing parenthesis in cell C 9. Selecting cell references to add a few values 4. Press ENTER to display the formula result of 45. 94. The arguments C 4 and C 6 tell the SUM function what values to calculate with. Enter formulas

Reference types Now that you’ve learned more about using cell references, it’s time to talk about the different types of references that are used in formulas: absolute, relative, and mixed. Relative and absolute cell references Enter formulas

Reference types Here are the details: 1. Relative references automatically change as they are copied down a column or across a row. Relative and absolute cell references 2. Absolute references are fixed; they don’t change if you copy a formula from one cell to another. Absolute references have dollar signs ($) like this: $D$9. Enter formulas

Reference types A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column. Relative and absolute cell references As a mixed reference is copied from one cell to another, the absolute reference stays the same but the relative reference changes. Enter formulas

Use an absolute cell reference Say you receive a package of entertainment coupons offering a 7 percent discount for video rentals. How much could you save in a month by using the coupons? Using an absolute cell reference To figure it out, you could create a formula to multiply those February expenses by 7 percent, using absolute references to refer to cells that you don’t want to change as the formula is copied. Enter formulas

Use an absolute cell reference Type the discount rate of 0. 07 in the empty cell D 9, and then type a formula in cell D 4, starting with =C 4*. Then enter a dollar sign ($) and D to make an absolute reference to column D, and $9 to make an absolute reference to row 9. Using an absolute cell reference Your formula will multiply the value in cell C 4 by the value in cell D 9. Enter formulas

Use an absolute cell reference Next, copy the formula from cell D 4 to D 5 by using the fill handle. Using an absolute cell reference As the formula is copied, the relative cell reference changes from C 4 to C 5, while the absolute reference to the discount in D 9 does not change —it remains $D$9 in each row it is copied to. Enter formulas

Use an absolute cell reference So, to recap the relative and absolute cell references in the example: 1. Relative cell references change from row to row. Using an absolute cell reference 2. The absolute cell reference always refers to cell D 9. 3. Cell D 9 contains the value for the 7 percent discount. Enter formulas

Suggestions for practice 1. Type cell references in a formula. 2. Select cell references in a formula. 3. Use an absolute reference in a formula. 4. Add up several results. 5. Change values and totals. Online practice (requires Excel 2003) Enter formulas

Test 2, question 1 What is an absolute cell reference? (Pick one answer. ) 1. The cell reference automatically changes when the formula is copied down a column or across a row. 2. The cell reference is fixed. 3. The cell reference uses the A 1 reference style. Enter formulas

Test 2, question 1: Answer The cell reference is fixed. Absolute cell references won’t change if you copy a formula from one cell to another. Enter formulas

Test 2, question 2 Which cell reference refers to a range of cells in column B, rows 3 through 6? (Pick one answer. ) 1. (B 3: B 6) 2. (B 3, B 6) Enter formulas

Test 2, question 2: Answer (B 3: B 6) The colon indicates a range of cells starting at B 3 and including B 4, B 5, and B 6. Enter formulas

Test 2, question 3 If you copy the formula =C 4*$D$9 from cell C 4 to cell C 5, what will the formula be in cell C 5? (Pick one answer. ) 1. =C 5*$D$9 2. =C 4*$D$ 3. =C 5*$E$10 Enter formulas

Test 2, question 3: Answer =C 5*$D$9 As the formula is copied, the relative cell reference, C 4, changes to C 5. The absolute cell reference, $D$9, does not change; it remains the same in each row it is copied to. Enter formulas

Lesson 3 Simplify formulas by using functions

Simplify formulas by using functions Function Calculates AVERAGE an average MAX the largest number MIN the smallest number Function names express long formulas quickly. SUM is just one of the many Excel functions. These prewritten formulas simplify the process of entering calculations, making it easy and quick to create formulas that might be difficult to build for yourself. In this lesson you’ll see how to speed up tasks with a few easy functions. Enter formulas

Find an average You could use the AVERAGE function to find the average cost of all entertainment for January and February: Using the AVERAGE function 1. Click in cell D 7, click the arrow on the Auto. Sum button , and then click Average in the list. 2. Press ENTER to display the result in cell D 7. Enter formulas

Find an average The formula =AVERAGE(B 7: C 7) appears in the formula bar near the top of the worksheet. You could also type this formula directly into the cell. Using the AVERAGE function Enter formulas

Find the largest or smallest value The MAX function finds the largest number in a range of numbers, and the MIN function finds the smallest number in a range. Using the MAX function Enter formulas

Find the largest or smallest value Here’s a formula to find the largest value in the set: 1. Click in cell F 7, click the arrow on the Auto. Sum button, and then click Max in the list. Using the MAX function 2. Press ENTER to display the result in F 7. The largest value is 131. 95. Enter formulas

Find the largest or smallest value Finding the smallest value in the range is a similar process: You’d click Min in the list and press ENTER. The smallest value would be 131. 75. Using the MAX function Enter formulas

Print formulas You can print formulas to put up on your bulletin board to remind you how to create them. 1. On the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode. Formulas displayed on the worksheet 2. Print as you usually would. Enter formulas

What’s that funny thing in my worksheet? Sometimes Excel can’t calculate a formula because the formula contains an error. If that happens, you’ll see an error value instead of a result in a cell. The ##### error value Enter formulas

What’s that funny thing in my worksheet? Here are three common error values: The ##### error value ##### The column is not wide enough to display the contents of the cell. Increase column width, shrink the contents to fit the column, or apply a different number format. #REF! A cell reference is not valid. Cells may have been deleted or pasted over. Enter formulas

What’s that funny thing in my worksheet? Here are three common error values: #NAME? You may have misspelled a function name or used a name that Excel does not recognize. The ##### error value Enter formulas

Find more functions Excel offers many other useful functions, such as date and time functions and functions you can use to manipulate text. The Insert Function dialog box You can see these other functions by clicking More Functions in the Auto. Sum list. This opens the Insert Function dialog box, which helps you search for a function and provides another way to enter formulas. Enter formulas

Find more functions When the dialog box is open, you can type what you want to do in the Search for a function box, or select a category and then scroll through the list of functions. The Insert Function dialog box Enter formulas

Suggestions for practice 1. Find an average. 2. Find the largest number. 3. Find the smallest number. 4. Display and hide formulas. 5. Create and fix error values. 6. Create and fix the error value #NAME. Online practice (requires Excel 2003) Enter formulas

Test 3, question 1 How would you print formulas? (Pick one answer. ) 1. Click Print on the File menu. 2. Click Normal on the View menu, and then click Print. 3. Point to Formula Auditing on the Tools menu, click Formula Auditing Mode, and then print as usual. Enter formulas

Test 3, question 1: Answer Point to Formula Auditing on the Tools menu, click Formula Auditing Mode, and then print as usual. This displays the formulas on your worksheet before you print. Enter formulas

Test 3, question 2 What does ##### mean? (Pick one answer. ) 1. The column isn’t wide enough to display the content. 2. The cell reference isn’t valid. 3. You’ve misspelled a function name or used a name that Excel doesn’t recognize. Enter formulas

Test 3, question 2: Answer The column isn’t wide enough to display the content. You can increase the column width to display the content. Enter formulas

Quick Reference Card For a summary of the tasks covered in this course, view the Quick Reference Card. Enter formulas