Formula Auto-Increment After So Many Rows

Add to Favorites
Author: | Edits: don

This Excel auto-increment formula includes the ability to control the interval of the increment and the increment itself.

The automatic interval formula allows you to more easily make lists of values or more advanced formulas that use functions like the INDIRECT() function to make interval-based advanced range references.

The techniques in this tutorial are explained step-by-step so that everyone can understand them and I break down the final formula into pieces and build it up so that you can see how everything works separately and together.

INT() Version

=INT((ROW(A1)-1)/5)

ROUNDDOWN() Version

=ROUNDDOWN((ROW(A1)-1)/5,0)

Watch the above video to see how these formulas work and how to change them to suit your needs.

Question? Ask it in our Excel Forum


Excel Function: INT(), ROUNDDOWN(), ROW()
Downloadable Files: Excel File

Similar Content on TeachExcel
Increment a Value Every X Number of Rows in Excel
Tutorial: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...
Creating a Splash Screen in Excel
Tutorial: Create a pop-up window, splash screen, that appears when Excel starts. This allows you to ...
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...
How to Find and Fix Errors in Complex Formulas in Excel
Tutorial: Here, I'll show you a quick, simple, and effective way to fix formulas and functions in E...
Best Lookup Formula in Excel - Index and Match
Tutorial: A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Ma...
Guide to Referencing Other Excel Files with Formulas and Functions
Tutorial: Your guide to making cross-workbook formulas and functions. This includes an overview of p...
Tutorial Details
Excel Function: INT(), ROUNDDOWN(), ROW()
Downloadable Files: Excel File
Similar Content
Increment a Value Every X Number of Rows in Excel
Tutorial: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...
Creating a Splash Screen in Excel
Tutorial: Create a pop-up window, splash screen, that appears when Excel starts. This allows you to ...
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...
Excel Forum