Excel can create consecutive numbers with AutoFill, Fill Series, ROW, or SEQUENCE; AutoFill is easiest for a plain list.
A long ID column gets dull after a few rows, so how to enter consecutive numbers in Excel is mostly a choice between dragging, filling a series, or using a formula. For a normal 1, 2, 3 list, type the first two numbers, select both cells, then drag the small square at the lower-right corner of the selection.
The method changes when the list is huge, must renumber after rows move, or needs codes such as 000-001. Excel can handle each case, but the wrong method is why many sheets copy the same number down the column.
Use AutoFill For A Plain Number List
AutoFill creates a plain consecutive list when Excel can see the first two values in the pattern. The two starting cells tell Excel whether to add 1, add 2, count backward, or repeat one value.
- Click the first blank cell where the list should start.
- Type
1, then press Enter. - Type
2in the cell below it. - Select both cells.
- Move the pointer to the lower-right corner until it becomes a small black cross.
- Drag down as far as the list should go.
The filled cells show 1, 2, 3 onward, and the Auto Fill Options button appears beside the range. Drag right instead of down when the numbers need to run across columns.
How Do You Fill A Number Pattern Without Typing Each Cell?
A number pattern needs two starting values so Excel can infer the step between them. Type 5 and 10 to count by fives, 100 and 99 to count backward, or 10 and 20 to build tens.
Select the two starting cells, drag the fill handle, then release when the preview reaches the last value you want. If Excel copies instead of continuing the pattern, click Auto Fill Options and choose Fill Series.
- Use one starting number when you want repeated values.
- Use two starting numbers when you want a rising or falling series.
- Use more starting numbers when the pattern is irregular but still predictable.
Entering Consecutive Numbers In Excel By Range Size
Range size decides whether dragging, a fill command, or a formula wastes less time. Microsoft says Excel does not provide one automatic numbering button, but it does recommend the fill handle and ROW formula for sequential row numbers in Microsoft’s row-numbering steps.
| Method | Use It When | Move To Make |
|---|---|---|
| AutoFill With Two Cells | Short or medium list | Type 1 and 2, select both, drag the fill handle |
| Auto Fill Options | Excel copies one number | Click Auto Fill Options, then choose Fill Series |
| Fill Command | Large range already selected | Select the first cell and range, then use Fill > Series |
| ROW Formula | Rows may be sorted with their data | Enter =ROW(A1), then fill down |
| SEQUENCE Formula | Microsoft 365 or Excel 2021 and later | Enter =SEQUENCE(200) for numbers 1 through 200 |
| TEXT With ROW | Codes need leading zeros | Use =TEXT(ROW(A1),"000-000") |
| Excel Mobile Fill | Phone or tablet entry | Type two values, select both, tap Fill, then drag the fill arrow |
Use Formulas When Numbers Must React To Changes
Formulas are better when the numbering should update as the sheet changes. A typed AutoFill list is fixed text, so deleted or moved rows can leave gaps until you fill the range again.
For a simple formula list, type =ROW(A1) in the first numbered cell and fill it down. The formula returns 1 in the first row of your sequence, then 2, 3, and onward as the reference changes.
SEQUENCE is shorter when your Excel version has it. Type =SEQUENCE(50) to spill 1 through 50 down a column, or type =SEQUENCE(10,1,5,1) to start at 5 and return ten numbers.
Why Does Excel Copy The Same Number Instead?
Excel copies the same number when the starting data does not define a series or when the fill setting is turned off. One starting cell usually means “repeat this,” while two starting cells usually mean “continue this pattern.”
| What You See | Likely Cause | Fix |
|---|---|---|
1, 1, 1, 1 |
Only one starting cell was selected | Type 1 and 2, select both, drag again |
| No fill handle appears | Fill handle setting is off | Windows: File > Options > Advanced > Enable fill handle and cell drag-and-drop |
| Mac fill handle is hidden | Edit setting is unchecked | Excel > Preferences > Edit > Allow fill handle and cell drag-and-drop |
| Numbers spill into used cells | SEQUENCE has no blank space below | Clear the spill range or move the formula to an empty column |
| Leading zeros disappear | Excel treats the entry as a number | Use TEXT, or format the cells before entering codes |
Choose The Move That Fits The Sheet
AutoFill is the move to use for most one-time lists. Type the first two numbers, select both, drag the lower-right fill handle, and check the last cell before you move on.
Use Fill Series when dragging hundreds or thousands of rows feels clumsy. Use =ROW(A1) when the numbers should travel with sorted rows. Use SEQUENCE when you want one formula to spill the whole list into a blank range.
- Plain list: AutoFill with two starting cells.
- Large list: Fill > Series.
- Renumbering after sort:
=ROW(A1). - Spilled formula list:
=SEQUENCE(rows). - Code format:
=TEXT(ROW(A1),"000-000").
The list is done when the final value matches the count you expected and no used cells were overwritten.
References & Sources
- Microsoft.“Automatically Number Rows In Excel.”Explains the fill handle, ROW formula, and fill handle setting for sequential row numbers.