MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy a Formula While Keeping One Reference Fixed

November 24, 2021 - by

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?

A new example. There is a percentage of 106.5% in C1. The headings are in row 3, with Quantity in E4 and Unit Price in E4. The current formula for row 4 is =ROUND((D4*E4)*C1,2).
Figure 176. This formula works in row 4…

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

Look at the formula in the formula bar. 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.

Copy the formula from row 4 to all rows. Look at the formula in F5. The references to D4 and E4 correctly changed to D5 and E5. However, the part of the formula pointing to C1 incorrectly changed to C2. This part of the formula needs to stay pointing to C1. Change the formula to refer to $C$1 instead.
Figure 177. ...but the formula fails in other rows.

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..

Copy the formula down from row 4 to all rows. The figure shows the formula in F7. The original references to D4 and E4 now point to row 7. The C1 reference keeps pointing at C1 thanks to the dollars signs: $C$1.
Figure 178. The dollar signs keep C1 pointing to C1.

Additional Details: See Create a Multiplication Table 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 to learn a cool shortcut for entering the dollar signs automatically.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash