let excel do the math introduction to formulas and … excel do the math: introduction to formulas...

Let Excel Do the Math: Introduction to Formulas and Functions

OverviewPerforming calculations — whethersimple or complex — can be tediousand time consuming. You can useExcel formulas and functions to dothese calculations for you; after you'vetried them, you'll never go back tousing a calculator!

What you'll learnAfter you complete this course, you'll be able to:

Create a simple formula for basic mathematicalcalculations.Use cell references to update formula resultsautomatically.Use a function to simplify formulas.Copy a formula.Edit a formula.

Estimated time to complete

20 minutes

Practice fileLessons are designed to be followed

in order. Download and use thispractice file for hands-on experience

while you take the course.

FunctionsFormulas.xlsx (18K)

Create a simple formulaYou can calculate values on your sheet by using formulas. For example, you might use a formulato calculate your monthly grocery expenses or to find the average amount that you pay for astock.Let's create a formula to add a list of values.

1 of 15

Click the cell where you want the result to appear (for example, B5).1.

Click in the formula bar, and then type an equal sign (=).

Tip If you don't see the formula bar, click View > Formula Bar.

2.

Click the first cell that you want to include in the total (for example, B2), and then type aplus sign (+).

3.

Repeat step 3 until you include all cells that you want to include in the total (forexample, =B2+B3+B4), and then press RETURN.

4.

2 of 15

Notice that the result of the formula appears in the result cell.5.

Hints

Each cell on a sheet has a cell reference. A cell reference is the set of coordinates that acell occupies. For example, the reference of the cell that appears at the intersection ofcolumn B and row 3 is B3. You can use cell references in a formula instead of typing thenumber in the cell.All formulas must begin with an equal sign (=).The plus sign (+) is called an operator. The other basic mathematical operators are: theminus sign (-), the multiplication sign (*), and the division sign (/).

Before you move onMake sure that you can do the following:

Create a simple formula in the formula bar.

Update a formula result automaticallyIf you change the value of a cell that's used in a formula, Excel can update the resultautomatically. You don't have to change the formula at all.Let's change a value in a cell and see how Excel updates the result.

3 of 15

Click a cell that is used in the formula that you created in the previous lesson (forexample, B2).

1.

Type a new value in the cell, and then press RETURN.2.

Notice that the result of the formula reflects the new value.3.

Hints

To delete a formula, click the cell that contains the result, and then click Edit > Clear >Contents.


Change the value of a cell in a formula and confirm that the result is updatedautomatically.

Add a list of numbers by using the SUM functionIn Excel, a function is a predefined formula. For example, the SUM function adds a range ofvalues that you specify, so that you don't have to type a plus (+) operator between each value. Inother words, you can use a function as a shortcut so that you don't have to create a formulamanually.Let's use the SUM function to add a range of values.

4 of 15

Click the cell where you want the result to appear (for example, C5).1.

In the formula bar, type =SUM( .2.

Select the range of cells that you want to include in the formula (for example, C2, C3,and C4), and then press RETURN.

3.

Click the cell that contains the result.4.

Notice that the result appears in the cell and that the formula appears in the formulabar.

5.

5 of 15

Note The notation C2:C4 is called a “range reference,” which means that the formulaincludes everything in the range from cell C2 through cell C4.

Hints

A range reference can span multiple columns and rows. For example, the formula=SUM(A1:D5) adds everything in cells A1 through cell D5.You can include nonadjacent cells in a formula, such as =SUM(A3,B5,C7).


Create a basic formula by using the SUM function.

Calculate quickly with AutoSumWith AutoSum, you can use some of the most common Excel functions, such as SUM, AVERAGE,MAX, and MIN, to do quick calculations. AutoSum creates the formula for you and includes boththe function and the cell references.Let's use AutoSum to total a list of numbers.

Click the cell where you want the result to appear (for example, D5).1.

6 of 15

Click AutoSum.2.

Notice that Excel selects the range of cells to include in the formula, for example=SUM(D2:D4).

3.

Press RETURN.4.

Notice that the total appears in the cell.5.

Hints

To use other common functions such as AVERAGE, MAX, and MIN, click the arrow nextto AutoSum.You can use AutoSum to create formulas in both rows and columns.


Use AutoSum to add a list of numbers.

Copy a formula with AutoFillWith AutoFill, you can copy a formula to cells in the same row or column so that you don't haveto make the same calculation in several places on a sheet. For example, you might want to copy

7 of 15

a formula that adds expenses for one month to all of the months.Let's use AutoFill to copy a formula to several cells.

Click the cell that contains the formula that you want to copy (for example, D5).1.

Rest the pointer over the lower-right corner of the cell.2.

Drag across the cells where you want to copy the formula (for example, E5, F5, and G5),and then release the mouse.

3.

Notice that totals appear in the cells where you copied the formula.4.

Click one of the new result cells, and notice that the formula bar contains the correctformula and cell references.

5.

8 of 15

Tip To verify that a formula contains all the cells that you intended, click the formula inthe formula bar. After you verify the formula, press ESC.

Hints

When you use AutoFill to copy formulas, Excel adjusts cell references in differentcolumns or rows automatically, but it uses the same function in all new results cells.


Use AutoFill to copy a formula to several cells in the same row or column.Identify a new formula in the formula bar.

Copy formula results to another cellTo copy the results of a formula without copying the formula itself, you use a special pasteoption. You can copy and paste formula results anywhere on a sheet.Let's copy the results of a formula to another cell.

Select the cell that contains the formula results that you want to copy (for example, C5).1.

Click Edit > Copy.2.

9 of 15

Select the cell where you want to copy the formula results (for example, A6).3.

Click Edit > Paste Special.4.

Under Paste, click Values, and then click OK.5.

10 of 15

Press ESC.6.

Notice that the formula result appears in the cell and in the formula bar.7.


Use the Paste Special option to copy the results of a formula to any cell in a sheet.

11 of 15

Edit a formulaYou can change a formula to include different cells or to use a different function. You edit aformula either in the formula bar or directly in the cell.Let's change a formula to calculate an average instead of a sum for a list of numbers.

Click the cell that contains the formula that you want to change (for example, H2).1.

In the formula bar, double-click SUM.2.

Type AVERAGE, and then press RETURN.3.

Notice that the average value appears in the result cell.4.

Hints

12 of 15

To cancel changes in the middle of an edit, press ESC.To edit a formula directly in the cell, double-click the cell, and then type the changes.


Edit a formula either in the formula bar or directly in the cell.

Course QuizComplete this quiz to make sure that you understand the material. Your answers are private andare not scored.

In Excel, all formulas must begin with an equalsign (=).

TrueFalse

In Excel, the symbols +, - , *, and / are called:DivisorsSignsOperatorsConstants

If you use cell references (for example,A2+C5*D8) in a formula, the formula resultupdates automatically when you change a value inany of the referenced cells.

TrueFalse

In the formula =AVERAGE(A1:A5), AVERAGE is the:FunctionConstantOperatorNotation

AutoFill is the best way to copy a formula result toa cell in another row or column.

True

13 of 15

False

In Excel, a function is basically a predefined, orbuilt-in, formula.

TrueFalse

SUM is the only function that is available from theAutoSum feature.

TrueFalse

Quick Reference Card

Add a list of numbers with AutoSum

Click the cell where youwant the result to appear.

1.

Click AutoSum.2.Press RETURN.3.

Use a function in a formula

Click the cell whereyou want the resultto appear.

1.

In the formula bar,type an equal sign(=), a function (forexample, SUM), andthen type an openparentheses.

2.

Click the cells thatyou want to includein the formula, andthen press RETURN.

3.

Copy a formula with AutoFill

Hints

All Excel formulas must begin withan equal sign (=).The basic mathematical operatorsinclude: + (addition), -(subtraction), * (multiplication), and/ (division).To refer to a cell range in aformula, use the notation A1:A4.For example, =SUM(A1:A4) addsvalues in the cell range A1 throughA4.

Q & A

Q: How do I copy a formula result toanother cell on my sheet?

A: Select the cell that contains theformula result, and click Edit >Copy. Then select the cell where youwant to copy the result, click Edit >Paste Special, and click Values.

Q: How can I edit a formula withoutdeleting the result in the cell?

A: Either edit the formula in the formulabar or double-click the cell thatcontains the result, and then edit theformula directly in the cell.

Q: How do I find other AutoSumfunctions (AVERAGE, MAX, COUNTNUMBERS, MIN)?

14 of 15

Rest the pointerover the lower-rightcorner of the cellthat contains theformula you want tocopy.

1.

Drag across thecells where youwant to copy theformula, and thenrelease the mouse.

2.

A: Click the arrow next to AutoSum.

15 of 15

let excel do the math introduction to formulas and … excel do the math: introduction to formulas...

Documents