If you need 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 your formula.
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.
How this formula works
The ROW() function, when entered into a cell with no arguments with return the 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
If you are copying a formula across columns, you can use 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.
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.