Increment a calculation with ROW or COLUMN

Summary

To dynamically increment a calculation, so that a value automatically increments each time the formula is copied to a new row or column, you can use the ROW or COLUMN functions. In the example shown, the formula in cell D6 is:

=$B$6*(ROW()-5)

When this formula is copied down column D, it multiplies the value in B6 by a number that starts with 1 and increments by one at each step.

Generic formula

=calculation*ROW()

Explanation

The ROW function, when entered into a cell with no arguments returns the row number of that cell. In this case, the first instance of the formula is in cell D6, so ROW() returns 6 inside the formula in D6.

We want to start with 1, however, so we need to subtract 5, which yields 1.

As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to "normalize" the result back to a 1-based scale:

=$B$6*1 // D6
=$B$6*2 // D7
=$B$6*3 // D8
etc

If you are copying a formula across columns, you can use the COLUMN function the same way.

Note that you are free to use the result of COLUMN or ROW any way you like in the formula. Both functions return a number, so you can apply them in a formula them just like you would use any number.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.