Excel Guides

Incrementing References by Multiples when Copying Formulas in Excel

When you copy a formula in Excel, the references are automatically incremented by the number of rows or columns that you've copied the formula over. However, there may be times when you want to increment the references by a multiple of the number of rows or columns that you've copied the formula over. For example, if you have a formula in cell A1 that references cell B1 and you copy that formula down to cell A2, the reference in A2 will automatically change to B2. But what if you want it to reference B3 instead?

There are a couple of ways that you can do this. One is to use the INDIRECT function. The INDIRECT function returns a reference specified by a text string. So, if we wanted to increment the reference in cell A2 by one row, we could use this formula:

=INDIRECT("B" & ROW()+1)

If we wanted to increment it by two rows, we would use this formula:

=INDIRECT("B" & ROW()+2)

And so on. You can also use the INDIRECT function to increment by a multiple of the number of rows or columns that you've copied the formula over. For example, if you have a formula in cell A1 that references cell B1 and you copy it down 10 rows, you can use this formula to have it reference cells B11, B21, B31, and so on:

=INDIRECT("B" & ROW()*10+1)

Another way to increment references by a multiple is to use the OFFSET function. The OFFSET function returns a reference to a range that is offset from another range or cell. So, if we wanted to increment the reference in cell A2 by one row, we could use this formula:

=OFFSET(A1,1,0)

If we wanted to increment it by two rows, we would use this formula:

=OFFSET(A1,2,0)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.