Excel – How to copy a formula multiple times and always refer to the same cell

microsoft excelmicrosoft-officeopenofficespreadsheet

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.

Best Answer

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

Related Solutions

Excel – Can Excel show a formula and its result simultaneously

I'm not aware of any built-in functions for this. You can, however, create a user-defined VB function to accomplish what you want.

Press Alt+F11 to open the VBA editor, right-click in the Project Explorer and select Insert -> Module. Paste the following code:

Function GetFormula(cell)  
  GetFormula = cell.Formula  
End Function

Now you can use =GetFormula(A1) to show the formula of that cell.

Excel – How to stop Excel From Updating Links When Copy and Pasting Cells Linked to Other Workbooks

Depending on the volume of the data you are copying, this may not be a viable option but...

You could copy the formula text for all of the linked cell without the equals sign with minimal slowdown and, once everything is in place, use CONCATENATE or search and replace to add the equals sign to the beginning of all of those text strings then let it crank out all of those links at once.

Let's say all of the data is in a range adjacent to "NamedCellNumberOne" You could paste "C:\Some folder\someworkbook.xlsm'NamedCellNumberOne" in the first cell then drag the corner of the cursor to "fill series" as needed. Once that is done, search all instances of "C:\Some folder\" and replace them with "=C:\Some folder\" to create all the formulas at once.

Best Answer

Related Solutions

Excel – Can Excel show a formula and its result simultaneously

Excel – How to stop Excel From Updating Links When Copy and Pasting Cells Linked to Other Workbooks

Related Question