Increment a Value Every X Number of Rows in Excel

Add to Favorites
Author: | Edits: don

(New Easier Version for Excel 2010 and Later)

How to increment a value, number, percent, etc. every so many rows in Excel.

This tutorial teaches you how to build the formula needed to increment a value or, really, do anything every so many rows in Excel.

Section:

The Formula That Knows in Which Row You are in Excel

How to Increment a Value Based on Which Row You are in Excel

Data Set Not on Row 1

Notes

The Formula That Knows in Which Row You are in Excel

This is the meat of the process and it allows you to know that you are in every 3rd or 5th or 25th row of your data set.  For the example below, we want to act on every 3rd row.

This is the function that we will use for this:

=IF(MOD(ROW(),3)=0,"Hi","")

For now, this will just output "Hi" every third row and it looks like this:

0138a1e9e10937566f1a368d88628abe.png

To change this to your data set, simply change the 3 to whichever number of rows you want for the increment.  Make sure that your data set starts in row 1 or this won't work.  If it's not in row 1, then copy your data set to a new tab where it can start on row 1 (if you can't do that I'll show you how to adjust for it below).

How it Works

Let's break down the formula in case you feel like understanding how it works.  The best way to start with Excel is inside-out.

ROW() this function tells you the current row that the formula is in.  If we are in row 1, it returns a 1, row 2, it returns a 2, etc.

MOD() We use the MOD function to tell us if we are in the third row or not by dividing the current row number, gotten from the ROW function, by the number 3, which is the second argument in the MOD function.  We can change 3 to whatever we want: 5 to do something every 5 rows, 10 to do something every 10 rows, etc.

When the MOD function performs this division, it will return the remainder, if one exists.  If you divide a number by three and there is no remainder, the MOD function will return zero, and you know that we are on the third row again.  If the MOD function returns anything other than a zero, we are not on the third row.  It may seem confusing but it's a basic math concept; if the current row number is divisible by three without any left-over decimal places, then the function will return a zero.

We then use the IF statement to check if the MOD function equals zero or not.  If it equals 0, then we know we are in the correct row to do something.

If all of this sounds confusing, don't worry because it IS confusing!  This is a rather complex formula that relies on a couple rarely used functions and some more advanced techniques in Excel.  The more that you use Excel and nest functions, the easier it will become to understand formulas like this.

How to Increment a Value Based on Which Row You are in Excel

Now that we have the formula that will tell us when we need to do the incrementing, we need to do the incrementing.

In this example, I will simply increment a number by one to illustrate how you do this.  However, you could use any type of increment or mathematical equation or function to do whatever you need.

Here is the result we will get:

bd3ed7c9fac30710bcc2de2cc8652882.png

Column C contains the final formula.

First, you need to fill the first cell in the column with a number or value that you type in by hand.  Here I put 1.  We do this because, otherwise, it requires a much more complicated formula to account for the first row in the data set, when we can just type the 1 in by hand and be done.

Now, we need to figure out what we want to do.  I want to increment this value that is in the first cell by one every three rows.  If we are not in a "third row" then I want to repeat the last number.

To do this, we fill-in the last two arguments for the IF statement that we made above; here is the result:

=IF(MOD(ROW(),3)=0,C1+1,C1)

Remember that this goes into row 2.

From the formula above, we replaced "Hi" with C1+1 and "" with C1.

This is basic IF statement stuff and there really isn't anything too complex going on here.

Change these cell references to work for your data and that's it.

As previously mentioned, you can do whatever you want here; you don't have to simply increment the value; perform any calculations that you need to do here.

Data Set Not on Row 1

If your data set does not start on row 1, you will need to adjust the above formula like this:

=IF(MOD(ROW()-2,3)=0,"Hi","")

Notice the -2.  You have to subtract a value from the ROW() function in order to get this to work when your data set does not start in row 1.

In this case, the data set starts in row 3, so we subtract 2; if it was in row 5, we would subtract 4.  You are basically offsetting the calculation so it will act like it was starting in row 1.

The final formula that we used above would end up like this:

=IF(MOD(ROW()-2,3)=0,C3+1,C3)

Here is what the result looks like in Excel:

d5fdb9ea1826dcaed63d04e1e9f50873.png

Notes

This may seem confusing, but you don't have to learn how everything works in order to be able to use this for your spreadsheet; simply follow the instructions above and you can customize it for your needs with just a few edits.  However, there is nothing prohibitively difficult here, just nesting a few functions that you may not have previously seen and checking if they equal zero and putting all of that within an IF statement.

Make sure to download the spreadsheet that accompanies this tutorial so you can see these examples in action and change them to suit your needs.


Excel Function: MOD(), ROW()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Odd or Even Row Formulas in Excel
Tutorial: Formulas to determine if the current cell is odd or even; this allows you to perform speci...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
MOD Function in Excel
Tutorial: The MOD function is very simple but it can be used to do wonderful things in Excel. It ret...
Get the Row or Column Number of a Cell in Excel
Tutorial: How to get the row or column number of the current cell or any other cell in Excel. This t...
Transpose Every X Number of Rows in Excel - Advanced Transpose Technique
Tutorial: How to Transpose groups of rows from a large column of data; this allows you to do things...
Tutorial Details
Excel Function: MOD(), ROW()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course