Insert Row Numbers

What it does

You need an ID column. 1, 2, 3, ... down a couple of thousand rows. The fill-handle drag works for short lists; for long ones, it's clumsy. =ROW()-1 works but leaves a formula instead of a value. The macro takes a single-column selection, asks what number to start from (1, 0, 100, anything you type), and writes static integers down every cell in the selection in one pass. No formulas, no fill-handle hand cramps.

Real-world example

Picture a study coordinator at a clinical research center building the participant tracking sheet for a new trial. The trial enrolls about 320 participants. The protocol requires every participant be referenced in study materials by their participant ID, and the IDs follow the pattern 001, 002, ..., 320. She wants those IDs in column A so other tabs can VLOOKUP against them.

Her current method: type 1 in A2, click the fill handle, drag down 320 rows. About 10 seconds, but the drag goes off the visible part of the screen and she has to release, scroll, re-grab, re-drag. Sometimes the autofill assumes she wants to repeat 1 instead of incrementing, and she has to start over with 1, 2 typed in to give it a hint. About 30 seconds total, with one near-miss per use.

With this macro she selects A2 through A321, runs the tool, types 1 for the start, and 320 sequential integers fill the column in less than a second. The popup confirms 320 cells were filled. She has a clean ID column with zero formula-recalc dependencies (since the macro writes static values). To get the leading-zero 001 format, she selects the column and applies the custom number format 000, which is a one-step Excel built-in.

On a 320-row participant tracking setup, expect about 25 seconds saved versus the fill handle, plus zero risk of the autofill misbehaving. Per study (3 to 4 per year) it's a small win, but the simplicity is the point: no formula, no recalc dependencies, just a clean column of integers.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select the single column where the row numbers should go. The macro fills exactly the range you select.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick InsertRowNumbers, click Run.
3. Type the starting number. 1 for 1, 2, 3, ..., 0 for 0, 1, 2, ..., 100 for 100, 101, 102, ....
4. The macro fills the column with sequential integers and reports the count.

FAQ

Why static integers instead of formulas?

Formulas like =ROW() recalculate, which means deleting or inserting rows above the formula changes the displayed numbers. For an ID column, that's the wrong behavior: the IDs need to stay attached to their rows even after restructure. Static integers don't shift. If you need a recalculating row counter, use =ROW()-N directly; this macro is for the static case.

What about leading zeros (like 001, 002, 003)?

The macro writes integers, so 1, 2, 3 rather than 001, 002, 003. To display them with leading zeros, select the column after running the macro, press Ctrl+1 to open Format Cells, choose Custom, and type 000 (or 0000 for 4-digit). The cells stay as integers internally so they sort and lookup correctly; only the display changes.

Can I number every other cell or only fill blanks?

No, the macro fills every cell in your selection sequentially. To number only specific rows, select just those rows (Ctrl+click multi-select) before running. To number with gaps, run the macro on a contiguous range and then delete the rows you don't want.

What if the selection has existing data?

The macro overwrites whatever was there. If you select a range with data and run the macro, that data is replaced with the sequential numbers. Always insert a blank column first if you want to keep existing data.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes the fill in one step, restoring the previous cell contents. As always, work on a copy of your file if it's the only copy. The macro runs entirely offline. No signup, no account, no data leaves your machine.