How to Add Row Numbers in Excel

The "I need an ID column" problem

You need a column with sequential integers: 1, 2, 3, ... down a couple of thousand rows. Maybe an ID. Maybe a row index. Maybe a participant number for a research project. Excel has multiple ways. The right one depends on whether you want recalculating or static numbers.

TL;DR — Key takeaways

• Fill-handle drag works for short lists but is clumsy for thousands of rows.
• =ROW() returns the current row number; subtract an offset to start at 1.
• =SEQUENCE() (Microsoft 365) spills sequential numbers across an array.
• A VBA macro writes static integers, which don't shift on row delete.

Method 1: Fill-handle drag

1. Type 1 in the first cell.
2. Click the fill handle (small square at the cell's bottom-right corner).
3. Drag down. As you drag, hold Ctrl to make Excel increment instead of repeat.
4. Release.

For 50 rows, fast. For 5,000 rows, the drag goes off the visible part of the screen. You release, scroll, re-grab. Usually about 30 seconds with one or two restarts.

Method 2: =ROW formula

=ROW() - 1

In A2 if your data starts at row 2. The formula returns the current row number minus 1, giving you 1 in A2, 2 in A3, etc.

Fill down (double-click the fill handle to auto-fill).

Pros: Survives if you copy the column. Recalculates.

Cons: Recalculates means deleting a row above the formula shifts the displayed numbers.

Method 3: =SEQUENCE function (Microsoft 365)

=SEQUENCE(1000)

Spills sequential integers down 1,000 cells starting from 1.

Pros: Live. Spills automatically.

Cons: Microsoft 365 only.

Method 4: The free VBA macro

Download Insert Row Numbers. Free .xlsm with one macro.

1. Select the column.
2. Alt + F8, pick the macro, click Run.
3. Type the starting number (1, 0, 100, anything).
4. Macro fills static integers.

Static vs formula numbering: which to use

Need	Use formula	Use macro
Numbers update when rows delete above	Yes	No (static)
Numbers stay attached to their original rows	No	Yes
Survives copy-paste-as-values	Need to convert	Yes (already values)
Works in Excel 2016	Yes	Yes
Works in Microsoft 365 with SEQUENCE	Yes	Yes

For an ID column where the IDs need to stay attached to their rows even after restructure, use the macro. For a row counter that recalculates, use the formula.

A common scenario: clinical trial participant IDs

Study coordinator builds the participant tracking sheet. Trial enrolls 320 participants. IDs are 001 through 320.

1. Select A2 through A321.
2. Run Insert Row Numbers. Type 1.
3. Sequential integers fill the column.
4. To display as 001, 002, etc., select the column and apply custom number format 000.

The cells stay as integers internally so they sort and lookup correctly; only the display has the leading zeros.

Frequently asked questions

How do I get leading zeros (001, 002, 003)?

After running the macro, select the column, press Ctrl+1 to open Format Cells, choose Custom, type 000 (or 0000 for 4-digit). The cells display with leading zeros while staying as integers internally.

Can it skip rows or only number specific ones?

No, the macro fills every cell in your selection. To number only specific rows, select just those rows (Ctrl+click multi-select) before running.

What about resetting numbering at certain rows (per group)?

The macro doesn't support per-group numbering. For that, use a SUMIF or COUNTIF formula chain.

Does it work with leading zeros in the source range (like an existing 0-padded column)?

The macro overwrites whatever was in your selection. So existing data is replaced. Insert a blank column first if needed.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

For a running total column next to the IDs, Add Running Total Column. To freeze the values (defensive against future row inserts that might shift formulas elsewhere), Convert Formulas to Values.