Understanding Relative and Absolute Cell References
By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another.
As an example, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts to the right by one column and becomes =B2+C2.
Absolute and Mixed Cell Referencing within Formulas
If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same.
In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3). To change the type of cell reference:
- Select the cell that contains the formula.
- In the formula bar, select the reference that you want to change.
- Press F4 to switch between the reference types.
The following table summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.
Understanding Basic Mathematical Operators
In some cases, the order in which a calculation is performed can affect the return value of the formula, so it's important to understand how the order is determined and how you can change the order to obtain the results you want.
Operator precedence in Excel formulas
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence—for example, if a formula contains both a multiplication and division operator—Excel evaluates the operators from left to right.
Using parentheses in Excel formulas
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because Excel performs multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21.
= (5+2)*3