See all Microsoft Excel tips
If you copy the formula in F4 to F5, you get an invalid result, as shown in Fig. 174.
Look at the formula in the formula bar in Fig. 174. As you copied the formula, the references to D4 and E4 changed as expected. However, the reference to C1 moved to C2. You need to find a way to copy this formula and always have the formula reference C1.
Frankly, this is the most important technique in the entire book. I once had a manager who would enter every formula by hand in the entire dataset. I didn’t have the heart to tell him there was an easier way.
Strategy: You need to indicate to Excel that the reference to C1 in the formula is Absolute. Do this by inserting a dollar sign before the C and before the 1 in the formula. The formula in F4 would change to =ROUND((D4*E4)*$C$1,2). As you copy this formula down to other rows in your dataset, the portion that refers to $C$1 will continue to point at $C$1, as shown in Fig. 175.
Additional Details: See the next chapter to understand the effect of using just one dollar sign in a reference instead of two. Read “Simplify Entry of Dollar Signs in Formulas” a few chapters after that to learn a cool shortcut for entering the dollar signs automatically.
Summary: Entering dollar signs in a reference will lock the reference and make it absolute. No matter where you copy the formula, it will continue to point to the original cell.
Functions Discussed: =ROUND()
Cross Reference: Create a Multiplication Table; Simplify Entry of Dollar Signs in Formulas
See all Microsoft Excel tips
