Excel – How to Make Two Formulas Reference the Same Cells in Excel

You can use the OFFSET function to shift an entire range by a certain amount. For example, if you had the text ="A1:A15" in cell C1, you could get the range B1:B15 by using the following formula:

=OFFSET(INDIRECT(C1), 0, 1)

For your reference, the function is defined in Excel as =OFFSET(reference, rows, columns, [height], [width]). To retrain the width/height of the original range, do not specify the height or width arguments. Also, note that the rows and columns arguments can be positive or negative (so you can shift both up/down and left/right).

Just another note, the OFFSET function works with entire ranges, so if in the previous example you entered the text ="A1:A12,A15" the returned range after using OFFSET to shift it right one column would be B2:B12,B15 as you would expect.

You're best option would be using named ranges: a named range can be relative to the cell entered. E.g. if you select cell A2 and insert a name _CellAbovewith the formula=A1(without the$` that are automatically inserted!), you can use CellAbove in any cell to refer to the cell above the cell with the formula.

Therefore for your example, select cell D2 and insert two names (using the Name Manager): * ColA: =$A2 * ColA_above: =$A1

Then use =ColA+ColA_above as the formula in D2 and copy it down. (Use =ColA in D1).

This will always return you the correct result - and is non-volatile which is really handy in large calculations!

Additional note: For your first example with the multiplication, I'd recommend to simply convert your data to an Excel Table (Insert tab -> Table. Then instead of column A and B, you would have columns, say Price and Quantity- and instead of =A1*B1 you would end up =[@Price]*[@Quantity] when you enter the formula (the same way as usual, i.e. Excel will place those names, no need to worry about it). Now even if you change the sheet strucuter, the formulas will work!