## How to increment number every x rows in Excel?

Normally, we can fill sequential numbers in a column with Fill Handle, but have you ever tried to fill a column with increment number every x rows? For example, first five rows fill the number 1, on the 6th row the value will become 2, then on the 11th row the value will become 3 and so on as following screenshot shown, to solve this problem, this article may do you a favor.

Increment number every x rows with formula

#### Increment number every x rows with formula

The following simple formula can help you to quickly fill the column with increment numbers every x rows as you want, please do as follows:

1. Enter this formula: =INT((ROW(E1)-1)/5)+1 into a blank cell where you want to fill the sequential numbers, see screenshot:

Note: In the above formula, the number 5 indicates to increment the numbers every 5 rows, you can change it to your need.

2. Then drag the fill handle down to the cells which you want to fill the increment numbers, and the numbers have been inserted with increment by 1 after every 5 rows, see screenshot:

### Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

 Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, I'm working on a data creation where I have to create sequential numbers based on data present in columnA. I have to create id in sequence which I can later concatinate after generating the sequence. Challenge is depending on the count of each value in columnA, the series should fill incrementally based on the count of each value. Also max sequence numbers can be 6 only. If ColumnA rows for a specific value goes above 6, then next series number should start. Any help would be highly appreciated. Eg shown below.

ColumnA ColumnB
PTSA 1
PTSA 1
PTSB 2
PTSB 2
PTSC 3
PTSD 4
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 5
PTSE 6
PTSE 6
PTSF 7
This comment was minimized by the moderator on the site
Hi all,

How do i increase in the range of 100 instead of 1. For ex after every 5 columns i need 100 ,200 etc
This comment was minimized by the moderator on the site
Hello, Vignesh
=(INT((ROW(A1)-1)/5)+1)*100
This comment was minimized by the moderator on the site
Hello!!! very interesting!!! Is there a way to do this but with months?
Something like:
A1
25/11/2018
25/11/2018
25/11/2018
25/11/2018
25/12/2018
25/12/2018
25/12/2018
25/12/2018
25/01/2019
25/01/2019
25/01/2019
25/01/2019.... and so on?

Thanks!
This comment was minimized by the moderator on the site
Hello, Alfredo
=DATE(YEAR("2018/11/25"),MONTH("2018/11/15")+INT((ROW(A1)-1)/4),DAY("2018/11/25"))
Note: If the date format does not match your need, please change the date format to oyur own.
This comment was minimized by the moderator on the site
Hello,

What is the formula to add sequential numbers to every 8 rows. I don't want the rows in between to have any numbers (2-7, or 9-16, and so on).

Thank you,
Yazen
This comment was minimized by the moderator on the site
Hello, Yazen
Sorry, I can't get your point, could you give a detailed example?
You can insert a screenshot or an Excel file of your problem here.
Thank you!
This comment was minimized by the moderator on the site
I am unable to add a screenshot or attach a file. But, here is what the excel looks like:
1 Apple 1
Apple 2
Apple 3
Apple 4
Apple 5
Apple 6

2 Orange 1
Orange 2
Orange 3
Orange 4
Orange 5
Orange 6

3 Banana 1
Banana 2
Banana 3
Banana 4
Banana 5
Banana 6

4 Melon 1
Melon 2
Melon 3
Melon 4
Melon 5
Melon 6

The numbers from 1 to 4 are manually entered. I am trying to figure out a formula that lets me add these numbers automatically to the first row category only and not every cell in that category.

Any help would be greatly appreciate it.

Thank you.
This comment was minimized by the moderator on the site
Hello,
``=IF(MOD((ROW(A1)-1)/6, 1)=0,(ROW(A1)-1)/6+1, "")``

This comment was minimized by the moderator on the site
Thank you very much, that's exactly how I wanted it to be.
This comment was minimized by the moderator on the site
Hey, thank you for this article. can I increase the specified row by other value instead of 1?
This comment was minimized by the moderator on the site
Hello，Jay，
Sorry, I can't get your point, could you give a detailed example? You can insert a picture here to describe your problem.
Thank you!
This comment was minimized by the moderator on the site
Thank you so much for this article.. It saved a lot of time for me. Appreciate your support guys..
This comment was minimized by the moderator on the site
Hello, I was trying to use the same formula for every 12 rows. =INT((ROW(R5)-1)/(12))+1877. But it does not work
This comment was minimized by the moderator on the site
Hello, Marjan, To increment number every 12 Rows, please use this formula:=INT((ROW(E1)-1)/12)+1
This comment was minimized by the moderator on the site
Thank you so much for this solution!!!!...........Your blog saves my night's sleep :)
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
How could you do this with workday instead of numbers?
This comment was minimized by the moderator on the site
How could you do this with columns? Dragging formula horizontally instead of vertically. Thank you.
This comment was minimized by the moderator on the site
Hi, Matt,
=INT((COLUMN(A1)-1)/5)+1
This comment was minimized by the moderator on the site
I Want stating number 908
This comment was minimized by the moderator on the site
Hi, try this:
=INT((ROW()-1)/12)+908
NB:
2-the number 12 in the formula stands for your increment row range: increment after 12 rows
This comment was minimized by the moderator on the site
Hello friend,

Thank you for your share. If you want to insert 908 in cell H1, Increment Number Every 5 columns, try this formula：
=INT((COLUMN(A1)-1)/5)+908

This formula can make the starting number 908 no matter where you insert the formula.

But if you input =INT((COLUMN()-1)/5)+908 in H1, the starting number will be changed to 909. So it is better to include A1 in the formula. Please have a try. Thanks.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
I am mac user How Increment Alphanumeric String By 2 Or More For A Column With Formula?Like that TEI-0001.jpg TEI-0001.jpg TEI-0001.jpg TEI-0002.jpg TEI-0002.jpg TEI-0002.jpg I use dogs.google
This comment was minimized by the moderator on the site
On a separate sheet to use for your math...Break the Alphanumeric into separate columns then, use the number columns for the incremental, then concatenate the results back, then copy the concatenated results and paste [as values] into the VBA script..
Column A text is "TEI-000"Column B text is "1"Column C text is ".jpg"
Use the formula "INT Row..." to do double number row lines for the number column.Columns "A" & "C" are unchanged so copy to last row of data.
Select all of the concatenated results, Copy, paste as values into another column to save your magic formula as a master reference sheet.
I've just done something like that for 100,000 cells with formulas.

This comment was minimized by the moderator on the site
Unfortunately Google Dogs only supports functions sit, stay, fetch, and rollover.
This comment was minimized by the moderator on the site
lmfao this is great
There are no comments posted here yet