Need 1, 2, 3… down a column? The quickest way is to type 1, then drag the fill handle with Ctrl held down. For numbers that update themselves, use a formula such as =SEQUENCE(10) or =ROW()-2. Here are ten methods, from drag-and-fill to formulas that skip blank rows.
Table of Contents
1. SEQUENCE (Excel 365)
One formula in B3 fills ten cells. SEQUENCE can also start anywhere and step by any amount:
=SEQUENCE(10)
=SEQUENCE(10,1,5) starts at 5, and =SEQUENCE(10,1,10,10) gives 10, 20, 30…. To number however many rows have data, use =SEQUENCE(COUNTA(C3:C100)).
How the formula works
SEQUENCE(rows, columns, start, step)creates the whole series at once and spills it down.ROW()-2works in any version: change 2 to the number of the row above your first number.COUNTA($C$3:C3)uses a range that expands as you copy, the same trick as a running total.
2. ROW, which survives sorting and deleting
ROW returns the row number of the cell it’s in. In B3 that’s 3, so subtract 2 to start at 1:
=ROW()-2
If you delete a row, the numbers below renumber themselves. Typed numbers wouldn’t.
3. Number only the rows with data
When the list has gaps, count the filled cells so far with COUNTA and leave blank rows unnumbered:
=IF(C3="","",COUNTA($C$3:C3))
$C$3:C3 grows as you copy down ($C$3:C4, $C$3:C5…), so each row counts the names above it. Blank rows get no number.
Without formulas: fill methods
| Method | How |
|---|---|
| 4. Fill handle with Ctrl | Type 1, hold Ctrl and drag the fill handle down |
| 5. Two starting numbers | Type 1 and 2, select both, and drag the fill handle |
| 6. Auto Fill Options | Type 1, drag the fill handle, then click the Auto Fill Options button and choose Fill Series |
| 7. Fill Series dialog | Type 1, select it, go to Home → Fill → Series, choose Columns, set the Stop value, such as 1000, and click OK |
| 8. Double-click | With 1 and 2 typed next to existing data, select both and double-click the fill handle to fill down to the end of the data |
These create plain numbers: they don’t update if you sort, insert or delete rows.
9. Power Query and 10. VBA
| Method | Code |
|---|---|
| Power Query (Data → Get Data → From Other Sources → Blank Query) | = List.Numbers(1, 100), then To Table and Close & Load |
| VBA macro (writes 1 to 100 down from the active cell) | For i = 1 To 100: ActiveCell.Offset(i - 1).Value = i: Next i |
To number rows inside Power Query, use Add Column → Index Column.
Notes
- In an Excel Table,
=ROW()-ROW(Table1[#Headers])numbers rows and fills automatically for new rows. - Formula-based numbers change when you sort. Convert them to values first (Paste Special → Values) if the order must stay fixed.
- Excel’s fill handle is the small square at the bottom-right corner of the selected cell.
Common errors
| Problem | Cause | Fix |
|---|---|---|
| Every cell shows 1 | Fill handle dragged without Ctrl | Hold Ctrl, or use Auto Fill Options → Fill Series |
| Numbers start at 3 | =ROW() used without subtracting |
Use =ROW()-2 (first number in row 3) |
#SPILL! |
Cells below SEQUENCE aren’t empty | Clear the cells it needs |
Practice file
SEQUENCE, ROW and skip-blanks numbering, with space to try the fill methods.