2 Formulas and Cell References

Learn how spreadsheet formulas use cell references, operators, and functions, how references behave when formulas are copied, and how to check results for errors.

How formulas calculate

A begins with an equals sign (=) and calculates a result from values, cell references, operators, and sometimes functions. For example, =A2+B2 adds the values in cells A2 and B2.

The operators +, -, *, and / perform addition, subtraction, multiplication, and division. Parentheses can clarify the intended order of calculation: =(A2+B2)*C2 adds A2 and B2 before multiplying by C2.

Cell references and ranges

A identifies a cell by its column letter and row number. A identifies cells from a first cell to a last cell with a colon, as in A2:A10. Functions can take cell references and ranges as inputs; for example, =SUM(B2:B10) adds the values in cells B2 through B10.

Using references makes formulas easier to update than typing values directly into each formula. If a value in a referenced cell changes, the formula can recalculate using the new value.

A sales worksheet example

Suppose A2 contains a unit price of 12, B2 contains a quantity of 3, C2 is the subtotal, and F1 contains a tax rate of 8%. Enter =A2*B2 in C2 to calculate the subtotal. With these values, C2 should display 36.

To calculate the total including tax in D2, enter =C2*(1+$F$1). With the example values, D2 should display 38.88.

Reference behavior when copying

When a formula is copied or filled into another cell, spreadsheet software adjusts its references according to their type.

A , such as A2, adjusts when copied. If =A2*B2 is copied from C2 to C3, it becomes =A3*B3. This is useful when each row should calculate from that row’s data.
An , such as $F$1, stays fixed. The dollar signs lock both the column and row. Copying =C2*(1+$F$1) from D2 to D3 changes C2 to C3 but keeps $F$1 unchanged.
A locks only one part. $A2 keeps column A fixed while allowing the row to change; A$2 keeps row 2 fixed while allowing the column to change. Mixed references are useful in formulas filled across or down a grid.

Leave references relative when they should move with each row or column, and make them absolute when they should keep pointing to a fixed input, such as a tax rate. In many spreadsheet apps, pressing F4 while editing a reference cycles through relative, absolute, and mixed forms.

Checking formula results

A displayed number alone does not prove that a formula is correct. Check both the formula and its result.

Confirm the inputs. Make sure referenced cells contain the intended values and that numbers have not accidentally been entered as text.
Inspect the formula. Select the cell and read the formula bar. Check the operators, parentheses, cell references, and function arguments.
Test a known example. Calculate a simple row by hand and compare it with the spreadsheet result. For instance, a price of 12 multiplied by a quantity of 3 should produce 36.
Check copied formulas. Select a few filled cells and confirm that row-specific references changed as expected while fixed references remained locked.
Look for errors or unusual results. #DIV/0! can indicate division by zero or a blank divisor; #REF! indicates an invalid reference; #NAME? can indicate unrecognized text, such as a misspelled function name; and #VALUE! can indicate an unexpected input type. A display of ##### can simply mean the column is too narrow to show the value.
Investigate complex formulas step by step. Excel’s Evaluate Formula tool can show how parts of a formula are calculated in sequence. Error-checking tools can also flag some formula problems, such as inconsistent formulas nearby.

A formula can return a plausible number and still be wrong, for example if it leaves out a row or uses the wrong cell. Verify that the formula matches the question being answered, not just that it produces a result.