Thread: Subtract same value in cell from a column of other cells

Hey guys,

I have a table of data with columns of figures.
e.g.

0.5477
0.5119
0.5292
0.5444
0.5798
0.5703

I then have a cell below these cells that has another figure i.e. 0.3321.

I want to subtract the figure (0.3321) from the figures in the column above, but have them pasted somewhere below the table (effectively in a new table).

When I do this in excel by simply clicking one value (say 0.5477) and then click on the cell that has 0.3321 when I "drag down" to fill the remaining cells instead of always subtracting the cell that has 0.3321 in it will subtract the cell below it.

I can overcome this by typing the number in, instead of clicking on the cell, however this isn't exactly what i want to do as once I've done it one one spreadsheet I can copy and paste to many more (which all have the exact same layout).

Many thanks in advance.

You have to make that cell below absolute by adding a $ in front of the Row Number

So in your list above is in A1:A6 and A7 contains the number to subtract..

Then you need A1-A$7 copied down.

To copy the values to other pages, select the range and copy it, then go to where you want to copy and go to Edit|Paste Special and select Values... This only copies the values and will not update if your numbers in the original formula changes..

if you do want them to change, then just use simple formula in other sheet like: =Sheet1!A8

where Sheet1, cell A8 contains first number to copy over, then drag down.