Add, divide, multiply, and subtract

Type an equal sign (=), use a math operator (+,-,*,/), and then press ENTER.

• =10+5 to add
• =10-5 to subtract
• =10*5 to multiply
• =10/5 to divide

Formulas are visible in the formula bar when you click a cell that contains a result. If the formula bar is not visible, on the View tab on the Ribbon, in the Show/Hide group, select the Formula Bar check box.

Use cell references in formulas

Entering cell references lets Microsoft® Excel® automatically update formula results if cell values are changed. For example:

• Type =C4+C7 in a cell.
• Or type the equal sign (=), click cell C4, type the plus sign (+), and then click cell C7.

Note    If results are not updated, on the Formulas tab, in the Calculation group, click Calculation Options. Then click Automatic.

Add the values in a row or column

Use the SUM function, which is a prewritten formula, to add all the values in a row or column:

1. Click a cell below the column of values or to the right of the row of values.
2. On the Home tab, in the Editing group, click the Sum button , and then press ENTER.

Tip    The Sum button is also located on the Formulas tab, in the Function Library group.

To add some of the values in a column or row:

1. Type an equal sign, type SUM, and then type an opening parenthesis.
2. Type or select the cell references you want to add. A comma (,) separates individual arguments that tell the function what to calculate.
3. Type a closing parenthesis, and then press ENTER.

For example: =SUM(B2:B4,B6) and =SUM(B2,B5,B7)

Find the average, maximum, or minimum

Use the AVERAGE, MAX, or MIN functions.

1. Click a cell below or to the right of values for which you want to find the average (arithmetic mean), the maximum, or the minimum.
2. Click the arrow next to Sum on the Home tab, in the Editing group. Click Average, Max, or Min, and then press ENTER.

To see more functions, click More Functions on the AutoSum list to open the Insert Function dialog box. Or click the Formulas tab.

Tip    You can also enter formulas and cell references by typing them in the formula bar after selecting a cell.

Copy a formula

Into an adjacent cell using the fill handle:

1. Click in the cell that contains the formula, and then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears.
2. Drag the fill handle over the cell or cells to which you want to copy the formula, and then release the mouse button.

Without using the fill handle:

1. Select the cell that contains the formula, and on the Home tab, in the Clipboard group, click Copy .
2. Select the cell or cells that you want to copy it to.
   • To copy the formula and any formatting, on the Home tab, in the Clipboard group, click the arrow on Paste, and in the list, click Paste again.
   • To copy the formula only, on the Home tab, click the arrow on Paste, and in the list click Formulas.

Print formulas

1. Display formulas on the worksheet. On the Formulas tab, in the Formula Auditing group, click Show Formulas .
2. Click the Microsoft Office Button in the upper left. Then click Print.
3. Hide the formulas on the worksheet by repeating step 1.

Tip    You can also press CTRL+` (the ` key is next to the 1 key on most keyboards) to display or hide formulas.

Understand error values

• #####    The column is not wide enough to display the content. Increase column width, shrink 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.
• #NAME?    You may have misspelled a function name.

Cells with errors such as #NAME? may display a color triangle. If you click the cell, an error button appears to give you some error correction options. How to use the button is not covered in this course.

Use more than one math operator in a formula

If a formula has more than one operator, Excel follows the rules of operator precedence instead of just calculating from left to right. Multiplication is done before addition: =11.97+3.99*2 is 19.95. Excel multiplies 3.99 by 2, and then adds the result to 11.97.

Operations inside parentheses take place first: =(11.97+3.99)*2 is 31.92. Excel adds first and then multiplies the result by 2.

Excel does use operators from left to right if they have the same level of precedence. Multiplication and division are on the same level. Lower than multiplication and division, addition and subtraction are on the same level.

Select the format for values to use in calculations

The worksheets in the practice sessions were formatted to display two decimal place numbers by clicking Increase Decimal in the Number group on the Home tab, until there were two decimal places.