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
Search JabSto ::




Custom Search