Using OFFSET to Manipulate Data in Cells That Are Inserted

Assume that you are using the following data in cells A1:A7 and that you want to subtract the last row from the first row in the range: 

A1: 1 

A2: 2 

A3: 3 

A4: 4 

A5: 5 

A6: 

A7: =A5-A1 Assume that you want to use a formula that will always be two rows below the last cell (with a blank cell between the formula and the last cell that contains data). Assume that when you insert a new row at the blank cell (row 6 in the following example), you want the formula to subtract the data in cell A6 (instead of the data in cell A5) from the data in cell A1. Note that in this example, the formula (=A5-A1) fails to subtract the data in row A6 when you insert a row with data in A6. To do this, use the OFFSET function. This function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. In the example, use the following formula: 

=OFFSET(A6,-1,0)-A1

This OFFSET formula is not fixed on the row above A6 and changes as you insert new rows

<<<Back Home Next>>>