How to Auto Number Cells in Excel (10 Methods)

We will be using the following dataset to show how to auto-number cells.

Method 1 – Using the Fill Handle to Auto Number Cells in Excel

Case 1.1 – Numbering Rows Automatically

We want to put a Serial number in the Machine’s category.

Type 1 and 2 in cells B5 and B6, respectively, and then select them.

Put your Cursor on the Fill Handle. It’s the small plus icon on the bottom-right corner of the selection or cell.

Click and drag it down to cell B10.

Read More: How to AutoFill Numbers in Excel

Case 1.2 – Numbering Columns Automatically

We want to put some data on these machines which will show how many products they manufacture in the first 5 days of a week.

Type 1 and 2 in cells F5 and F6, respectively, and select them. This will represent the first 2 days of the week.

Put the cursor on the Fill Handle button and drag it to the right.

Case 1.3 – Numbering Both Rows and Columns Automatically

Let’s assume that the operators of these machines get salaries in sequential ranges. For instance, the minimum and maximum wage of machine A operators are 101 dollars and 150 dollars respectively. Machine B operators get a salary in the range of 151 to 200 dollars.

Type 101, 150, 151, and 200 in cells D5, E5, D6, and E6, respectively.

Put the Cursor on the Fill Handle button and double-click on it or drag it down.

Method 2 – Using the Row Function to Auto Number Cells in Excel

Use the following formula in cell B5.

=ROW(A1)

When we drag down the Fill Handle, the reference changes from A1 to A2, then to A3, and so on, creating the Serial numbers.

Hit Enter and you will see the output in cell B5.

AutoFill to the rest of the column.

Method 3 – Applying the Column Function to Fill Cells Automatically with Numbers in Excel

We want to create some columns that indicate day numbers.

Use the following formula in cell D5.

=COLUMN(A1)

When we drag the Fill Handle to the right, the cell reference changes from A1 to B1, then to C1, and so on, counting the Day Number.

Press Enter and you will see the output in cell D5.

AutoFill through the row.

Method 4 – Using the RANDARRAY Function to Auto Number Cells in Excel

Let’s put random production values into the table between 10 and 21.

Select Formulas and Calculation Options, then check Manual. The RANDARRAY function keeps changing the values it generates.

Use the following formula in cell D6.

=RANDARRAY(6,5,10,21,TRUE)

The RANDARRAY function will generate a 6-by-5 array of integers within the range of 10 to 21.

Hit Enter.

Select the range D6:H11.
Press Ctrl + C to copy.
Right-click on any of these cells.
Select Paste Options and choose Values.

This will remove the formula from D6 so the data won’t change anymore.

Method 5 – Using the Series Command to Auto Number Cells in Excel

Let’s label the Machine Category with a sequential series of odd numbers.

Type 1 in cell B5 and select cells from B5 to B10.

Select Editing, then choose Fill and pick Series.

A dialog box for Series will appear.
Select Columns in Series in and select Linear in Type.
Input step value 2 and stop value 11 and click OK.

This will fill the cells B5 to B10 with the series numbers automatically.

Method 6 – Inserting the OFFSET Function for Auto Numbering Cells in Excel

Use the following formula in cell B5.

=OFFSET(B5,-1,0)+1

The OFFSET function takes the B5 cell as a base reference, -1 is the row reference which actually refers to cell B4 and 0 represents column B. We increase the number gradually by adding 1.

Press Enter and you will see the output in cell B5.

AutoFill the column as shown in Method 1.

Note: You need to keep the B4 cell blank. Otherwise, it will provide you with an error. To put the heading (Serial) back in cell B4, select the range B5:B10, copy it and paste the values back like in Method 4.

Method 7 – Using the COUNTA Function to Auto Number Cells in Excel

Use the following formula in cell B5.

=COUNTA($C$5:C5)

The COUNTA function will count non-empty cells of Column C (cells C5 to C10).

Press Enter.

AutoFill the column.

Method 8 – Creating an Excel Table to Automatically Fill the Cells with Numbers

Select the entire dataset and go to the Insert tab.
Choose Table.
A dialog box will show up. Click OK.

Use this formula in cell B5.

=ROW()-ROW(Table9[#Headers])

Note: Table names may vary. For the sample, it was Table9. You can find the table name on the top-left under Properties.

ROW() returns the value of the selected row number and ROW(Table9[#Headers]) returns the value of the header’s row number which is constant. When we hit ENTER, the ROW() function keeps returning the row value and subtracting it from the header row number. Hence, it provides us with a serial number instantly.

Press Enter.

The table created a fourth header named Column1.
Select cells D4 and E4.
Go to Table Design and select Tools, then pick Convert to Range.

A warning message will appear. Click YES on the dialog box.

Click on Merge & Center from the Home tab.

A warning message will appear.
Click OK on the dialog box.

This restores the third column like it was before.

Method 9 – Adding 1 to the Previous Row Number to Fill Cells Automatically in Excel

Type 1 in cell B5.
Use the following formula in cell B6.

=B5+1

Hit Enter.

AutoFill from B6 to B10.

Method 10 – Using the SUBTOTAL Function to Auto Number Cells in Filtered Data

If we want machines A and B out of consideration, we don’t need rows 5 and 6 anymore. But if we Filter them out, the serial number won’t start from 1. Rather, it will start from 3. We’ll get around that:

Open the Home tab.
From Sort & Filter, select Filter.

Unmark A and B and click OK.

Use the following formula in cell B7.

=SUBTOTAL(3,$C$7:C7)

The argument 3 means that the SUBTOTAL function will execute a COUNTA operation through visible cells in column C (C7 to C10).

Hit Enter.

Select cell B7 and drag the Fill Handle button down to cell B10.

Things to Remember

While using the OFFSET function, the cell above the formula cell must be empty.
Turn on Manual Calculation before using the RANDARRAY function.

Download the Practice Workbook

Auto Number Cells.xlsx

<< Go Back to Autofill Numbers | Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!