• Excel Book Excerpt

Excel Copy a Formula While Keeping One Reference Fixed

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Copy a Formula
While Keeping One Reference Fixed

Problem: I have 5,000 rows of data. Each row contains a quantity and the unit price. The sales tax rate for all orders is shown in cell C1. After I enter a formula to calculate the total with sales tax in the first row, how do I copy the formula down to other rows?

LE10000155.jpg 

Figure 186 This formula works in row 4...

If I copy the formula in F4 to F5, I get an invalid result of zero.

LE10000158.jpg 

Figure 187 ...but the formula fails in other rows.

Look at the formula in the formula bar above. As I copy the formula, the references to D4 and E4 changed as expected. However, the reference to C1 moved to C2. I need to find a way to copy this formula and always have the formula reference C1.

Note: This may be the most important technique in the entire book. I once had a manager who entered every formula in every data set by hand. 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. You do this by inserting a dollar sign before the C and before the 1 in the formula. For example, you would change the formula in F4 to =ROUND((D4*E4)*$C$1,2).

As you copy this formula down to other rows in your data set, the portion that refers to $C$1 will continue to point at $C$1, as shown below.

LE10000157.jpg

Figure 188 The dollar signs keep C1 pointing to C1.

Additional Details: See “Create a Multiplication Table" below to learn the effect of using just one dollar sign in a reference instead of two. Read “Simplify the Entry of
Dollar Signs in Formulas" on page 111
to learn a cool shortcut for entering the dollar signs automatically.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: