How to auto-number cells in Excel
You can watch a video tutorial here.
Excel has many tools and shortcuts that can be used to automate repetitive tasks or tasks that span a large number of rows and columns. One such task is numbering cells in a column or row. Not only is this a time-consuming task if done by typing the number in each cell, but the possibility of errors is high. Here we will look at different ways of auto-numbering the cells in Excel:
- Fill handle: this uses the fill tool from the cells
- Fill menu option: this uses the fill tool from the ribbon
- ROW() function: this returns the row number of the cell reference
- Syntax: ROW(cell reference)
- cell reference: the reference of the cell for which you want the row number. If this is left blank, it takes the row number of the cell it is in
- Syntax: ROW(cell reference)
- SEQUENCE() function: this generates a matrix of numbers according
- Syntax: SEQUENCE(rows, columns, start, step)
- rows: the number of rows in the matrix
- columns: the number of columns in the matrix
- start: the start number
- step: the number by which each number should increment
- Syntax: SEQUENCE(rows, columns, start, step)
Note: These same methods can be used to generate numbers across rows.
Option 1 – Use the fill handle
Step 1 – Type the numbers
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-1-–-How-to-auto-number-cells-in-Excel-951x1024.png)
- In the first two rows of the column where the series is to be filled, type the first two numbers of the sequence i.e. 1,2
Step 2 – Use the fill handle
![](https://spreadcheaters.com/wp-content/uploads/Option-1-Step-2-–-How-to-auto-number-cells-in-Excel-954x1024.png)
- Select the rows and use the fill handle at the lower right corner of the second cell to drag the box down
- The sequence of numbers being generated will be displayed as you drag the handle down
- Release the handle when you reach the last number
Option 2 – Use the fill menu option
Step 1 – Type the first number
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-1-–-How-to-auto-number-cells-in-Excel-933x1024.png)
- In the first row of the column in which the numbers are to be filled, type the first number i.e. 1
Step 2 – Open the Series box
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-2-–-Open-the-Series-box-1024x398.png)
- Select the column where the series is to be filled
- Go to Home > Editing and click the Fill button
- Select Series from the drop-down menu
Step 3 – Set the parameters
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-3-–-Set-the-parameters-1024x549.png)
- In the Series box, set the following:
- Series in: Columns
- Type: Linear
- Step value: 1
- Click OK
Step 4 – Check the result
![](https://spreadcheaters.com/wp-content/uploads/Option-2-Step-4-–-Check-the-result.png)
- The list of numbers is created
Option 3 – Use the ROW() function
Step 1 – Type the formula
![](https://spreadcheaters.com/wp-content/uploads/Option-3-Step-1-–-Type-the-formula-1024x827.png)
- Select the first cell of the column where the series is to be filled
- Type the formula:
=ROW(cell reference of the previous row)
- Press Enter
Step 2 – Copy the formula
![](https://spreadcheaters.com/wp-content/uploads/Option-3-Step-2-–-How-to-auto-number-cells-in-Excel-1024x867.png)
- Using the fill handle from the first cell, drag the formula to the remaining cells
OR
- Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
- Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
Step 3 – Check the result
![](https://spreadcheaters.com/wp-content/uploads/Option-3-Step-3-–-How-to-auto-number-cells-in-Excel.png)
- The list of numbers is created
Option 4 – Use the SEQUENCE() function
Step 1 – Type the formula
![](https://spreadcheaters.com/wp-content/uploads/Option-4-Step-1-–-How-to-auto-number-cells-in-Excel-1024x713.png)
- In the first cell of the column where the series is to be filled, type:
=SEQUENCE(10,1,1,1)
- This is for 10 rows, 1 column, starting at 1 and incrementing by 1
- Press Enter
- The sequence of numbers is created