Type 1 and 2 in the first two cells, select both and drag the fill handle down. Or type =ROW(A1) in the first cell and drag the fill handle down. Excel has no button for automatic numbering, so it uses one of these two routes.
Excel doesn't have a numbering button. What it gives you instead are two reliable routes, and the right one depends on whether your list will change. Microsoft sets both out in its guide on how to number rows automatically.
Route one: the fill handle
- Enter the first number in the top cell and the next beneath it.
- Select both cells.
- Drag the fill handle down across the range you want to fill.
This is the quick option for a fixed list. It's also the one with the weak spot, which I'll come to in a moment.
A worked example: numbering a contact list
Say your names sit in column B and you want a running count beside them in column A. Start the fill-handle route by typing 1 and then 2 in the first two cells, select that pair, and pull the handle down level with the last name. The pair sets the pattern and Excel carries it on.
For the function route, put =ROW(A1) in the first cell and pull the handle down the same distance. Both give you a tidy 1, 2, 3 down the side. The difference only shows later, when the list starts to move around.
If you can't see the fill handle at all, go to File > Options > Advanced and tick Enable fill handle and cell drag-and-drop under Editing options. That brings it back.
Route two: the ROW function
- Type =ROW(A1) in the first cell of the range.
- Drag the fill handle across the range you want to fill.
Each cell then works out its own number from its row, so the figures follow the data. The big plus: ROW-function numbers update when you sort them along with your data, so a sorted list doesn't end up with scrambled numbers.
That makes it the natural choice for anything you'll reorder. One typed formula at the start saves a manual renumber every time the list changes shape.
Checking your numbering
- Scroll to the bottom of the list and confirm the last number matches the last row.
- After you sort, glance down the column. ROW-function numbers should still run in order.
- After you delete or insert a row, look for gaps or blanks, because fill-handle numbers won't repair themselves.
Do this check before the list goes to anyone else. A broken sequence is the first thing a reader spots.
The catch: lists that change
Numbers made with the fill handle are plain values, so they are not updated when you add, move or remove rows. Delete a row and you'll have a gap in the sequence, and insert one and the new row has no number.
ROW-function numbers hold up better but can still break in sequence after such changes, unless you turn the data into an Excel table. If you want new rows numbered automatically, convert the data range to a table first and use the ROW function inside it.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
| Situation | Better route |
|---|---|
| A short, fixed list that won't change | Fill handle |
| A list you'll sort | ROW function |
| A list where rows get added and removed | ROW function inside an Excel table |
For help shaping that list afterwards, take a look at how to group rows in Excel or how to delete blank rows, since both change the numbering you've just set up.
Which route I'd pick
Pick by how long the list will live. A one-off sheet you'll print today? Drag the fill handle and move on. A sign-up sheet, stock list or client tracker that grows every week deserves the formula route set up on day one, because fixing a broken count later always takes longer than doing it right first time.
Frequently asked questions
Does Excel have an auto-number button?
Not a dedicated one. Microsoft's page says there's no button for numbering data automatically, which is why the two routes above exist.
Why do my numbers have gaps after deleting a row?
Numbers from the fill handle are fixed values that don't react to removed rows. The gap stays until you renumber by hand, or switch to the ROW function in a table.
Which method survives sorting?
The ROW function. Its numbers are updated when you sort them along with your data.
How do I get new rows numbered as I add them?
Turn the data range into an Excel table and use the ROW function. That's the route Microsoft names for automatic numbering of new rows.
Where has my fill handle gone?
Tick Enable fill handle in File > Options > Advanced.
Which route is better for a list I'll keep editing?
The ROW function inside an Excel table. Fill-handle numbers are static values, so every edit to the list means renumbering by hand.
