How to Increment Values by Row or Column in Excel
How to Increment Values by Row or Column in Excel. If you need to increment a value or a calculation in Excel as it is copied across rows and columns, you can achieve this using the ROW function (unless you have access to the SEQUENCE function). This article explains how to increment calculations based on rows or columns.
How to Increment Values by Row or Column in Excel
How to Increment Values by Row or Column in Excel
Generic Formula: =Expression + ((ROW() – number of rows above the first formula) * [steps])
Expression: This represents the value or expression that you want to increment. It can be a fixed value or any expression that produces a valid output. Generally, it should be an absolute expression. Number of rows above the first formula: If the first formula is in cell B3, then the number of rows above this formula would be 2. [steps]: This is optional. It indicates the number of steps to jump for each subsequent increment. You can replace the arithmetic operator between the expression and formula with other operators based on your increment requirements.
To illustrate the generic formula, let’s consider some examples:
Example 1: Creating an Auto Increment Formula for ID Generation
How to Increment Values by Row or Column in Excel
Suppose you want to create an auto-incrementing ID formula that generates IDs like “EXC1”, “EXC2”, “EXC3”, and so on. Since we only need to start the increment from 1 and concatenate it with “EXC”, we won’t use any steps. Assuming “EXC” is in cell C1, you can use the following formula in cell B4 and copy it downwards:
=$C$1&(ROW()-3)
In this formula, we replaced the “+” operator with the ampersand operator (&) for concatenation. As we start writing the formula in cell B4, we subtract 3 from ROW() to adjust for the row offset. The result will be an auto-incrementing ID.
How to Increment Values by Row or Column in Excel
Example 2: Incrementing the ID every 2 steps
If you want to increment the ID by 2 for each step, you can use the following formula:
=$C$1&((ROW()-3)*2)
This formula multiplies the row adjustment by 2, resulting in an incremented ID every two steps.
How to Increment Values by Row or Column in Excel
Example 3: Adding the Original Value to Each Increment
To add the starting expression to every incremented value, let’s say the starting value is 100 and we want to increment it as 100, 200, 300, and so on. In this case, you can use the following formula:
=$C$1*(ROW()-3)
Here, C1 contains the starting value of 100.
How to Increment Values by Row or Column in Excel
How does it work?
The technique is straightforward. The ROW function returns the current row number where it is written. In the formula above ROW() returns 4. By subtracting 3 from it (considering there are 3 rows above the 4th row), we obtain 1. This value is hardcoded to ensure it remains constant as we copy the formula downwards. Finally, this value is multiplied (or subjected to any other operation) with the starting expression. As the formula is copied downwards, ROW() returns 5, but the subtracted value remains the same (3), resulting in 2, and so on.
Incrementing Values by Column:
In the examples mentioned above, we incremented values by rows. However, if you want to copy the formulas to the next column in the same row, you need to use the COLUMN function instead of the ROW function.
Generic Formula to Increment by Columns: =Expression + ((COLUMN() – number of columns to the left of the first formula) * [steps])
The number of columns to the left of the first formula is determined by the position of the formula. For instance, if the first formula is in cell B3, the number of columns to the left of the formula would be 1.
Alternative with SEQUENCE Function:
For users of Excel 365 and 2019, there is a new function called SEQUENCE that can simplify the process of incrementing values. It returns an array of sequential numbers that can be used to increment values sequentially, either by rows, columns, or both. Additionally, you can specify the steps without the need to copy down the formula manually, thanks to Excel 365’s auto spill functionality.
For example, if you want to achieve the same result as in Example 3 using the SEQUENCE function, you can use the following formula:
=$C$1*SEQUENCE(100)
This formula will automatically generate 100 incremented values in one go, without the need to copy the formula. You can find more information about the SEQUENCE function in Excel‘s documentation.
How to Increment Values by Row or Column in Excel
That’s how you can perform auto incrementation in Excel. You can easily increment the previous cell’s value by adding 1 or apply steps to it. I hope this article has been helpful. If you’re facing any difficulties or have further questions, feel free to let me know in the comments section below. I’ll be glad to assist you. Keep excelling!
