How can I copy a formula multiple times and always refer to the same cell?

Question

I have a number in cell A1 and a column of 20 numbers in the B column.

I'd like to put a formula in each of the cells in the C column next to each number in the B column. Let's say it's =B1-A1, and then the next one would be =B2-A1, then =B3-A1, etc.

I could type all that in by hand, but it would take a long time. Is there a way I can copy the first formula, which is in cell C1, and paste it into the following cells in the C column?

I tried it, but I would get, say, =B5-A5 instead of =B5-A1.

I'm trying to do this in OpenOffice, but I suspect the answer would be similar for Excel.

Solution 1

In Excel the $ in front of the Rownumber fixes the row to a constant. A $ in front of the column letter fixes the column. So you have:

• A1 if you copy down, the 1 will change to 2,3, etc; if you copy across, the A will change to B,C, etc
• A$1 if you copy down, the 1 stays 1, if you copy across the A changes
• $A1 if you copy down the 1 changes, if you copy across the A stays
• $A$1 no matter how you copy, its always points to A1

Solution 2

Put a $ in front of the A column number in your formula, like this:

=B1-A$1

You can then copy and paste the formula and it will preserve the A column number. This should work in both Excel and Open Office.

Solution 3

OK, I figured it out.

Click the cell you want to refer to repeatedly, then type a name for it into the "name box" -- the box that usually displays the coordinates of the selected cell, such as "A1," "A2," etc.

Then once you've defined a name for the cell, use that name in your formula...

=B3-user_defined_name

If you copy and paste that formula it will always refer to the cell named "user_defined_name."

Solution 4

Just a quick addition to this. You don't have to type the "$" in your formulas. The F4 key is the keyboard shortcut to toggle a cell through the various options of absolute and relative reference. Keep hitting the key when you have the appropriate cell selected in your formula and it will toggle through the options mentioned by BillN.

View more solutions

Comments

[Mark Ransom]

You can put the $ in front of both the row and column designations. $A$1 will always refer to the same cell, $A1 will always refer to a row in the A column, and A$1 will always refer to a column in the 1 row.

[arathorn]

Right, I should have been clearer. Was just giving what was needed for his example.

[Matt Haley]

OpenOffice 3.1.1 works the same here.