How to Number a Column in Excel

To number a column in Excel, type 1 and 2 in the first two cells, select both, and drag the fill handle (the small square at the bottom-right corner of the selection) down to where the numbering should end. Excel fills in the rest of the sequence. For numbers that update when rows change, use a formula such as =ROW()-1 instead, covered further down.

How to Number a Column in Excel with the Fill Handle

Step 1: Enter the First Number

Type "1" into the topmost cell of the column you wish to number. To put the numbers in their own column beside existing data, first add an empty column in Excel by right-clicking a column letter and choosing Insert.

Entering the first number sets the starting point for your sequence. Without this, Excel won’t know where to begin the numbering.

Step 2: Enter the Second Number

In the cell directly below, type "2".

Adding the second number helps Excel recognize the pattern you wish to continue.

Step 3: Select Both Numbers

Click and drag your cursor to highlight both the cells containing "1" and "2".

Selecting both numbers lets Excel know you want to continue this pattern down the column.

Step 4: Use the Fill Handle

Move your cursor to the bottom-right corner of the highlighted cells until you see a small square (the fill handle). Click and drag the fill handle down the column to where you want the numbering to end.

Dragging the fill handle tells Excel to continue the sequence based on the pattern it detects from the highlighted cells. For other patterns, such as counting by 5 or stopping at an exact number, see how to use the fill handle in Excel.

Step 5: Release the Fill Handle

Release the mouse button once you’ve reached the desired cell.

Releasing the fill handle will auto-fill the column with sequential numbers, following the pattern you started.

After you complete these steps, your column will be numbered sequentially from top to bottom.

Other Ways to Number a Column

Dragging the fill handle works for quick lists, but formula-based numbering keeps working when your data changes. Both approaches are in the support article on automatically numbering rows. Here are the most useful methods.

Fill Series Dialog

Type 1 in the first cell, select it, and go to Home > Fill > Series. Choose Columns, set the Step value (such as 1) and the Stop value (such as 500), and click OK. This is the fastest way to number hundreds or thousands of rows without dragging.

ROW Function

In A2, enter =ROW()-1 and fill it down. ROW returns the row number, so subtracting 1 makes the list start at 1 under a header row. If you delete a row, the numbers below it update automatically. Sorting the data won’t scramble these numbers either, because each one is based on where it sits.

SEQUENCE Function

In Excel for Microsoft 365 and Excel 2021 or later, enter =SEQUENCE(100) in one cell to fill 1 through 100 down the column. Use =SEQUENCE(COUNTA(B:B)-1) to match the number of entries in column B (minus its header), so the list grows as you add data.

Number Only Rows That Have Data

If some rows are blank, use =IF(B2=””,””,COUNTA($B$2:B2)). Rows with something in column B get the next number, and blank rows stay empty.

Automatic Numbering in an Excel Table

Select your data and press Ctrl + T to make it a table. Then enter =ROW()-ROW(Table1[#Headers]) in the first cell of the number column. Excel fills the formula down automatically, and new rows you add at the bottom get numbered on their own.

Add a Prefix Such as ID-001

Keep the numbers as numbers and change only how they look. Select the column, press Ctrl + 1, choose Custom, and enter “ID-“000. The number 7 then displays as ID-007, but it still sorts and calculates as 7.

Restart Numbering for Each Group

If your list is sorted by a category in column B, such as department, enter 1 in A2 and =IF(B3=B2,A2+1,1) in A3, then fill down. The count goes up while the category stays the same and starts over at 1 when it changes.

Count by 2s, 5s, or 10s

Enter the first two values in the pattern, such as 5 and 10, then select both and drag the fill handle. With a formula, use =SEQUENCE(20,1,5,5) for 20 rows starting at 5 and going up by 5, or =(ROW()-1)*5 in older versions.

Number Across a Row

The same tricks work horizontally. Drag the fill handle to the right, use Home > Fill > Series with Rows selected, or enter =SEQUENCE(1,12) to fill 1 through 12 across a row, which is handy for month numbers.

Tips for Numbering a Column in Excel

1. Use AutoFill Options: After dragging the fill handle, click on the AutoFill Options button for more customization, such as filling only weekdays.
2. Double-click the Fill Handle: If you have data in adjacent columns, double-clicking the fill handle will automatically fill the column down to the last row of the adjacent column.

Troubleshooting

The Fill Handle Copies the Same Number

Excel needs two cells to see a pattern. Enter 1 and 2, select both, and drag. With only one cell selected, hold Ctrl while you drag to make Excel count up instead of copying, or click the AutoFill Options button and choose Fill Series.

There’s No Fill Handle

The option is turned off. Go to File > Options > Advanced and check Enable fill handle and cell drag-and-drop.

Numbers Are Out of Order After Sorting

Typed numbers move with their rows when you sort. If you want the numbers to stay 1, 2, 3 from top to bottom, use =ROW()-1 instead.

The Column Headers Show Numbers Instead of Letters

The R1C1 reference style is on. Go to File > Options > Formulas and clear R1C1 reference style to get A, B, C back.

Frequently Asked Questions about Numbering a Column in Excel

How do I start numbering from a number other than 1?

Type your starting number in the top cell and the next number below it, then select both and drag the fill handle.

Will the numbering update if I add new rows?

Not if you typed or dragged the numbers. Fixed numbers stay the same when you insert, delete, or sort rows. Use a formula such as =ROW()-1 or =SEQUENCE(), or turn the data into an Excel table, and the numbers update automatically.

How do I number rows in a filtered list?

In A2, enter =SUBTOTAL(3,$B$2:B2) and fill it down. The 3 tells SUBTOTAL to count visible non-empty cells, so the numbers restart at 1, 2, 3 for whatever rows the filter shows.

Can I use letters instead of numbers?

Yes, but the fill handle won’t continue A, B into C, D. It repeats the pattern instead. Use the formula =CHAR(64+ROW(A1)) and fill it down to get A through Z. In Excel for Microsoft 365, =CHAR(SEQUENCE(26,1,65)) lists all 26 letters at once.