advertisement

advertisement

Information about 03 ms excel

Published on August 31, 2011

Author: fosterstac

Source: slideshare.net

advertisement

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

Overview: Simple calculations in Excel

Lesson 1: Get started

Lesson 2: Use cell references

Lesson 3: Simplify formulas by using functions

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. Overview: Simple calculations in Excel Enter formulas You’ll also learn how to use simple formulas that automatically update their results when values change.

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.

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

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.

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. 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 A budget worksheet needs an amount in cell C6.

In this lesson, you’ll learn how to use Excel as your calculator by typing simple formulas into cells.

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.

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. You do math in Excel by typing simple formulas into cells. Excel formulas always begin with an equal sign (=). Enter formulas Typing a formula in a worksheet

Two CDs purchased in February cost $12.99 and $16.99. The total of these two values is the CD expense for the month.

You do math in Excel by typing simple formulas into cells. Excel formulas always begin with an equal sign (=).

Begin with an equal sign Here’s how to add 12.99 and 16.99 in cell C6: Enter formulas Typing a formula in a worksheet Type the formula =12.99+16.99 . The plus sign (+) is a math operator that tells Excel to add the values. Press ENTER to display the formula result, 29.98.

Here’s how to add 12.99 and 16.99 in cell C6:

Type the formula =12.99+16.99 . The plus sign (+) is a math operator that tells Excel to add the values.

Press ENTER to display the formula result, 29.98.

Begin with an equal sign Here’s how to add 12.99 and 16.99 in cell C6: Enter formulas Typing a formula in a worksheet The formula appears in the formula bar near the top of the worksheet whenever you select cell C6.

Here’s how to add 12.99 and 16.99 in cell C6:

The formula appears in the formula bar near the top of the worksheet whenever you select cell C6.

Use other math operators 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 Excel uses familiar signs to build formulas. Math operators Add (+) =10+5 Subtract (-) =10-5 Multiply (*) =10*5 Divide (/) =10/5

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.

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 . Enter formulas Using the AutoSum button to total column values

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 .

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

To get your January total:

A colored marquee surrounds the cells in the formula, and the formula appears in cell B7.

Select cell B7, and then click the AutoSum button on the Standard toolbar. The AutoSum button adds up all the values in a range of cells.

Total all the values in a column To get your January total: Enter formulas Using the AutoSum button to total column values Press ENTER. This displays the SUM function result 95.94 in cell B7. Select cell B7 to display the formula =SUM(B3:B6) in the formula bar.

To get your January total:

Press ENTER. This displays the SUM function result 95.94 in cell B7.

Select cell B7 to display the formula =SUM(B3:B6) in the formula bar.

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

B3:B6 is the information, called the argument , that tells the SUM function what to add.

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

The colon (:) in B3:B6 indicates a cell range in column B, cells 3 through 6. The parentheses are required to separate the argument from the function.

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. Start by selecting cell B7, 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 Copying a formula

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.

Start by selecting cell B7, which contains the January formula. Then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears.

Copy a formula instead of creating a new one Next: Enter formulas Copying a formula Drag the fill handle over cell C7 and then release it. The February total 126.93 appears in cell C7. After the formula is copied, the AutoFill Options button appears to give you some formatting options.

Next:

Drag the fill handle over cell C7 and then release it. The February total 126.93 appears in cell C7.

After the formula is copied, the AutoFill Options button appears to give you some formatting options.

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

Create a formula to add.

Create formulas for other arithmetic.

Add up a column of numbers.

Copy a formula.

Add up a row of numbers.

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

What do you type into an empty cell to start a formula? (Pick one answer.)

*

(

=

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

=

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

What is a function? (Pick one answer.)

A prewritten formula.

A math operator.

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

A prewritten formula.

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

A formula result is in cell C6. You wonder how you got the result. To see the formula, you do which of the following? (Pick one answer.)

Select cell C6, and then press CTRL+SHIFT.

Select cell C6, and then press F5.

Select cell C6.

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

Select cell C6.

Lesson 2 Use cell references

Use cell references 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. Enter formulas Cell references Cell references Refer to values in A10 the cell in column A and row 10 A10,A20 cell A10 and cell A20 A10:A20 the range of cells in column A and rows 10 through 20 B15:E15 the range of cells in row 15 and columns B through E A10:E20 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.

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

Suppose it turned out that the 11.97 in cell C4 for video rentals in February was incorrect. A rental of 3.99 was left out.

To add 3.99 to 11.97, you would select cell C4 and type this formula into the cell:

Update formula results As the picture shows, when the value in cell C4 changes, Excel automatically updates the February total in cell C7 from 126.93 to 130.92. Enter formulas Excel can automatically update totals to include changed values. Excel can do this because the original formula =SUM(C3:C6) in cell C7 contains cell references.

As the picture shows, when the value in cell C4 changes, Excel automatically updates the February total in cell C7 from 126.93 to 130.92.

Update formula results If you had entered 11.97 and other specific values into a formula in cell C7, Excel would not be able to update the total. Enter formulas Excel can automatically update totals to include changed values. You’d have to change 11.97 to 15.96 not only in cell C4, but in the formula in cell C7 as well.

If you had entered 11.97 and other specific values into a formula in cell C7, Excel would not be able to update the total.

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. 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 Selecting cell references to add a few values

You can type cell references directly into cells, or you can enter cell references by clicking cells, which avoids typing errors.

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.

Other ways to enter cell references Imagine that you want to know the combined cost for video rentals and CDs in February. Enter formulas Selecting cell references to add a few values 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 C9.

Imagine that you want to know the combined cost for video rentals and CDs in February.

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

Here’s how to enter the formula:

Type the equal sign, type SUM , and type an opening parenthesis in cell C9.

Click cell C4, then type a comma in cell C9.

Other ways to enter cell references Here’s how to enter the formula: Enter formulas Selecting cell references to add a few values Click cell C6. Then type a closing parenthesis in cell C9. Press ENTER to display the formula result of 45.94. The arguments C4 and C6 tell the SUM function what values to calculate with.

Here’s how to enter the formula:

Click cell C6. Then type a closing parenthesis in cell C9.

Press ENTER to display the formula result of 45.94. The arguments C4 and C6 tell the SUM function what values to calculate with.

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 . Enter formulas Relative and absolute cell references

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 .

Reference types Here are the details: Enter formulas Relative and absolute cell references Relative references automatically change as they are copied down a column or across a row. 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.

Here are the details:

Relative references automatically change as they are copied down a column or across a row.

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.

Reference types A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column. Enter formulas 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.

A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column.

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? 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 Using 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?

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.

Use an absolute cell reference Type the discount rate of 0.07 in the empty cell D9, and then type a formula in cell D4, starting with =C4* . 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. Enter formulas Using an absolute cell reference Your formula will multiply the value in cell C4 by the value in cell D9.

Type the discount rate of 0.07 in the empty cell D9, and then type a formula in cell D4, starting with =C4* . 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.

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

Next, copy the formula from cell D4 to D5 by using the fill handle .

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

So, to recap the relative and absolute cell references in the example:

Relative cell references change from row to row.

The absolute cell reference always refers to cell D9.

Cell D9 contains the value for the 7 percent discount.

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

Type cell references in a formula.

Select cell references in a formula.

Use an absolute reference in a formula.

Add up several results.

Change values and totals.

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

What is an absolute cell reference? (Pick one answer.)

The cell reference automatically changes when the formula is copied down a column or across a row.

The cell reference is fixed.

The cell reference uses the A1 reference style.

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

The cell reference is fixed.

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

Which cell reference refers to a range of cells in column B, rows 3 through 6? (Pick one answer.)

(B3:B6)

(B3,B6)

Test 2, question 2: Answer (B3:B6) Enter formulas The colon indicates a range of cells starting at B3 and including B4, B5, and B6.

(B3:B6)

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

If you copy the formula =C4*$D$9 from cell C4 to cell C5, what will the formula be in cell C5? (Pick one answer.)

=C5*$D$9

=C4*$D$

=C5*$E$10

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

=C5*$D$9

Lesson 3 Simplify formulas by using functions

Simplify formulas by using functions 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 Function names express long formulas quickly. Function Calculates AVERAGE an average MAX the largest number MIN the smallest number

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.

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

You could use the AVERAGE function to find the average cost of all entertainment for January and February:

Click in cell D7, click the arrow on the AutoSum button , and then click Average in the list.

Press ENTER to display the result in cell D7.

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

The formula =AVERAGE(B7:C7) appears in the formula bar near the top of the worksheet.

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. Enter formulas Using the MAX function

The MAX function finds the largest number in a range of numbers, and the MIN function finds the smallest number in a range.

Find the largest or smallest value Here’s a formula to find the largest value in the set: Enter formulas Using the MAX function Click in cell F7, click the arrow on the AutoSum button, and then click Max in the list. Press ENTER to display the result in F7. The largest value is 131.95.

Here’s a formula to find the largest value in the set:

Click in cell F7, click the arrow on the AutoSum button, and then click Max in the list.

Press ENTER to display the result in F7.

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. Enter formulas Using the MAX function The smallest value would be 131.75.

Finding the smallest value in the range is a similar process: You’d click Min in the list and press ENTER.

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

You can print formulas to put up on your bulletin board to remind you how to create them.

On the Tools menu, point to Formula Auditing , and then click Formula Auditing Mode .

Print as you usually would.

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. Enter formulas The ##### error value

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.

What’s that funny thing in my worksheet? Here are three common error values: Enter formulas 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.

Here are three common error values:

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

Here are three common error values:

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

Excel offers many other useful functions, such as date and time functions and functions you can use to manipulate text.

You can see these other functions by clicking More Functions in the AutoSum list. This opens the Insert Function dialog box, which helps you search for a function and provides another way to 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. Enter formulas The Insert Function dialog box

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.

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

Find an average.

Find the largest number.

Find the smallest number.

Display and hide formulas.

Create and fix error values.

Create and fix the error value #NAME.

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

How would you print formulas? (Pick one answer.)

Click Print on the File menu.

Click Normal on the View menu, and then click Print .

Point to Formula Auditing on the Tools menu, click Formula Auditing Mode , and then print as usual.

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

Point to Formula Auditing on the Tools menu, click Formula Auditing Mode , and then print as usual.

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

What does ##### mean? (Pick one answer.)

The column isn’t wide enough to display the content.

The cell reference isn’t valid.

You’ve misspelled a function name or used a name that Excel doesn’t recognize.

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

The column isn’t wide enough to display the content.

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

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

USING THIS TEMPLATE See the notes pane or view the full notes page (View menu) for detailed help on this template.

Microsoft Excel 2013, free and safe download. Microsoft Excel 2013 latest version: Smarter and easier workbooks. Excel 2013 is a powerful workbook and ...

Read more

Modul 3 . MS Excel – Tabellenkalkulation. Qualifizierungsziel. Sie können alle im Büro und/oder in der Verwaltung gängigen Arbeiten im Bereich der ...

Read more

Hallo zusammen, wie kann ich in Excel 2003 die Formeln eines Arbeitsblattes ausdrucken? Viele Grüße Nikos -- Wem das Wasser bis zum ...

Read more

Excel 2013 Deutsch: Die Tabellenkalkulation Microsoft Excel 2013 kommt mit zahlreichen neuen Features und in Modern UI-Optik von Windows 8.

Read more

Microsoft Office Compatibility Pack für Dateiformate von Word, Excel und PowerPoint

Read more

16.-17.03.2015 & 19.03.2015 MS Excel für Fortgeschrittene Zielgruppe/ Voraussetzungen Mitarbeiter der Universität, die bereits mit Excel arbeiten und ...

Read more

Microsoft Office Compatibility Pack für Dateiformate von Word, Excel und PowerPoint Öffnen, Bearbeiten und Speichern ... 03 PowerPoint ...

Read more

MS Excel 2007 / Excel 2010 / Excel 2013 / Excel 2016 - Formeln und Funktionen - Seminare Schulungen Kurse und Trainings in Deutschland Österreich und in ...

Read more

Microsoft Excel 2013 15.0.4420.1017 kostenlos in deutscher Version downloaden! ... MS-Office-Tools, Plugins, Addons & Vorlagen; Notizzettel auf dem Desktop;

Read more

Microsoft Excel 2013, Download kostenlos. Microsoft Excel 2013 15.0.4805.1003: Tabellenkalkulation mit Touch-Gesten und Cloud-Anbindung. Die ...

Read more

## Add a comment