Microsoft Office Tutorials and References

In Depth Information

**Creating simple cell formulas**

Understanding formulas and cell references in Excel

When you build a formula, you need to identify the

worksheet cells that provide the values for the formula and the

operations that you want to perform on those values. To

identify a cell, you give its cell a reference. The first cell in the

first column is cell A1, meaning column A, row 1. If you

examine a formula, you sometimes see a cell reference written as

$A$1, rather than just A1. The difference is that cell references

written with the dollar signs are absolute references, meaning

that the reference doesn’t change when the formula is copied

to another cell. Cell references written without the dollar signs

are relative references, which do change when the formula

with the reference is copied to another cell.

that any cell in any workbook can be described by three

pieces of information: the name of the workbook, the name

of the worksheet, and the cell reference.

Here’s the reference for cell Q38 on the January worksheet in

the Y2013ByMonth workbook:

[Y2013ByMonth.xlsx]January!$Q$38

The good news is that you don’t need to remember how

to create these references yourself. If you want to use a cell

from another workbook in a formula, all that you need to

do is click the cell where you want to use the value, start the

formula, and then click the cell in the other workbook. Excel

fills in the reference for you.

The benefit of relative references is that you can write a

formula once, copy it to as many other cells as you like, and have

Excel update the formulas to reflect the new cells. For

example, consider the worksheet in the following figure, which

tracks the number of hourly package pickups for a month.

The cells in column P contain formulas that calculate the sum

of the hourly pickup values in column C through column

O. The formula in cell P5, =SUM(C5:O5), finds the sum of

cells in row 5, corresponding to January 1. When you copy

the formula from cell P5 to cell P6, the formula changes to

=SUM(C6:O6). Excel notices that you copied the formula to a

new row and assumes that you want the formula to work on

that data. Had you written the formula as =SUM($C$5:$O$5),

however, Excel would notice that the formula used absolute

references and would copy the formula as =SUM($C$5:$O$5).

If you want to reference a value from a cell in another

workbook, you can do that. Excel uses 3D references, which means