Using a Simple Formula

Operand

Example

Constant value

2

Cell reference

A1

Range reference

A1:B2

Label

Jan Sales

Range name

Revenue

Function

Sum(A1:B4)

Operators:

Symbols

Addition

+

Subtraction

-

Multiplication

*

Division

/

Percent

%

Exponential

^

Operator

Description

: (colon)   (single space) , (comma)

Reference operators

–

Negation (as in –1)

%

Percent

^

Exponentiation

* and /

Multiplication and division

+ and –

Addition and subtraction

&

Connects two strings of text (concatenation)

= < > <= >= <>

Comparison

How To Resize The Formula Bar

SUMMARY

TIPS

1. C lick the Expand Formula Bar button at the right of the formula bar. The formula bar expands to two rows. To collapse the Formula Bar, click the Collapse Formula Bar button, as shown below. You can also press <Ctrl>+<Shift>+<U> to expand and collapse the Formula Bar.

2. Position mouse pointer over the horizontal line under the Formula Bar. The mouse pointer changes to a double-headed arrow, as shown below.

3. Click and drag to adjust the size of the Formula Bar. This method helps you to expand the Formula Bar to the size you want.

How To Enter A Formula Using The Keyboard

SUMMARY

Enter the following data before you begin this task.

1. Click the cell B5. You want to enter a formula in this cell.

2. Type =B2+B3+B4. The formula always starts with = (equal sign) as illustrated below.

3. Press <Enter>. The total is calculated. If you click at the cell B5, the formula appears in the formula bar.

How To Enter A Cell or A Range Reference Using The Mouse

SUMMARY

1. Click the cell B5. You will enter a formula in this cell.

2. Type =, click the cell B2, type +, click the cell B3, type +, click the cell B4. The formula appears, as shown below.

3. Press <Enter>. The total is calculated.

How To Use AutoSum To Calculate Totals

SUMMARY

1. Click the cell B5. You will calculate the sum of the contents in the cells above the active cell.

2. Click the Formula tab. In the Function Library group, click the AutoSum button. The formula is created automatically. Excel adds the numbers above the active cell. If no numbers are found above the active cell, the numbers on the left of the active cell are added up. The formula appears, as shown below.

3. Press <Enter>. The total is calculated. If the active cell is not located at the bottom or on the right of a list of numbers. You can still use the AutoSum button to calculate the total.

1). Click the cell and click the AutoSum button. A formula appears, as shown on the right.
2) Click to select the cells that contain the numbers you want to add.
3) Press <Enter>.

How To Use Relative References

SUMMARY

TIPS

By default, when a formula is created, references to cells or ranges are based on their position relative to the cell that contains the formula.

When you copy the formula, Excel will automatically paste the adjusted references in the destination cells relative to the position of the formula.

Before you begin, type in the data for Feb column, as shown below.

1. Click the cell B5. You are going to copy the formula =SUM(B2:B4) in cell B5 to cell C5.

2. D rag the Fill Handle to the right to fill the cell C5 with the formula. The Fill Handle is the small dot at the lower-right corner of the active cell B5. The formula is copied from the cell B5 to cell C5.

3. Click the cell C5 to see the formula =SUM(C2:C4). The range reference B2:B4 changes to C2:C4 when the formula is copied to the right. Since you dragged the Fill handle to the right, the column letter changes relatively to the right but row number remains unchanged. The destination cell contains references that are relative to the source formula.

How To Use Absolute References

SUMMARY

Use absolute references if you do not want Excel to adjust the references in the formula when copying a formula to another cell.

Before you begin, type in the Sales column and the Unit Price, as shown below. The formula in cell C2 is =B2*B7. When you copy the formula to cell C3 using AutoFill, the formula changes from =B2*B7 to =B3*B8. However, you do not want B7 to change to B8 because the unit price is stored in cell B7. So, you need to set the cell reference B7 as an absolute reference. To do this, add dollar signs ($) in the cell reference.

1. Click the cell C2. You are going to copy the formula =B2*B7 to C3:C5.

2. Press <Enter>. Click the Fill Handle at cell C2 and drag to fill C3:C5. This is to find the sales for Mary, Steve and Total. You will get the wrong results as shown below.

3. Click the cell C3 to see the formula. The formula is =B3*B8, which is a relative reference of =B2*B7 in cell C2. However, the correct formula should be =B3*B7 instead.

TIPS

4. C lick the cell C2 again. Change the formula in cell C2 by adding dollar signs ($) to the cell reference B7. The new formula should be =B2*$B$7. This is to set the cell B7 as an absolute reference. You can change the formula in the formula bar.

5. Click and drag the Fill handle down to cell C5. The sales are recalculated. Click on cell C3, the formula is =B3*$B$7. The cell reference B7 (absolute reference) remains unchanged while cell reference B2 (relative reference) changes to B3.