Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 8 down vote accepted

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
share|improve this answer
    
OpenOffice 3.1.1 works the same here. –  Matt Haley Oct 8 '09 at 3:08

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.

share|improve this answer

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.

share|improve this answer
3  
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. –  Mark Ransom Aug 7 '09 at 21:54
    
Right, I should have been clearer. Was just giving what was needed for his example. –  arathorn Aug 7 '09 at 21:58

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.