How to Auto Number Cells in Excel (10 Methods)

Get FREE Advanced Excel Exercises with Solutions!

There are various ways to auto number cells in Excel. Auto numbering cells means filling the cells automatically with numbers in Excel. Here we will see 10 different ways of how to auto number cells in Microsoft Excel. I will be using the following dataset to show you the examples.

Here, we are showing 6 Machine Categories and their Operators Salary Range($) in USD.

Table of Contents hide

1. Using Fill Handle to Auto Number Cells in Excel

1.1. Numbering Rows Automatically

1.2. Numbering Columns Automatically

1.3. Numbering Both Rows and Columns Automatically

2. Using Row Function to Auto Number Cells in Excel

3. Applying Column Function to Fill Cells Automatically with Numbers in Excel

4. Using the RANDARRAY Function to Auto Number Desired Cells in Excel

5. Using Series Command to Auto Number Cells in Excel

6. Inserting OFFSET Function for Auto Numbering Cells in Excel

7. Using COUNTA Function to Auto Number Cells in Excel

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

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

10. Using Excel SUBTOTAL Function to Auto Number Cells in Filtered Data

Things to Remember

Conclusion

1. Using Fill Handle to Auto Number Cells in Excel

There are different ways to fill the cells automatically in Excel using the Fill Handle feature. We can fill adjacent cells (along with rows or columns) with numbers with this feature. I will describe them below.

1.1. Numbering Rows Automatically

Suppose we want to put a Serial number in the Machine’s category. We can easily fill the rows automatically by using the Fill Handle feature.

Here, you see the Serial column is empty. We want to put 1 to 6 in the range B5:B10.

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

Now put your Cursor on the Fill Handle This is marked in the following picture.

Now drag it down to cell You will see the numbers 1 to 6 will fill the cells respectively.

This operation fills the cells B5 to B10 automatically.

1.2. Numbering Columns Automatically

You can also fill columns using the Fill Handle feature. Suppose you 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.

Now put the Cursor on the Fill Handle button and drag this to the cell.

This operation fills columns F5 to J5 with day numbers (1 to 5).

1.3. Numbering Both Rows and Columns Automatically

Now, we want to fill both rows and columns with numbers. 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. To fill up the Salary Range column, type 101, 150, 151, and 200 in cells D5, E5, D6 & E6 respectively.

Now put the Cursor on the Fill Handle button and double-click on it or drag it down to the cell.

This process fills the range of cells D5 to E10 with the Salary Range automatically.

Similar Readings:

2. Using Row Function to Auto Number Cells in Excel

We can fill the rows automatically by using the ROW function. Let’s see the process below

Type the following formula in cell B5.

=ROW(A1)

(You may also type B1 or C1 or any other cell reference from row-1 in the formula instead of A1)

Here, the ROW function takes cell reference A1 as 1. When we drag down the Fill Handle, reference changes from A1 to A2, A3, and so on as thus the Serial number.

Now hit ENTER and you will see the output in cell B5.

Now select cell B5, put your Cursor on the Fill Handle button, and drag it down to cell B10 to Auto Numbers Cells.

You can see that cells B5 to B10 are filled with numbers 1 to 6 automatically.

3. Applying Column Function to Fill Cells Automatically with Numbers in Excel

We can also use the COLUMN function to fill cells automatically with numbers. We want to create some columns that indicate day numbers. Let’s see the process below. (The Salary Range column is not shown here)

Type the following formula in cell D5.

=COLUMN(A1)

(You may also type A2 or A3 or any other cell references from column A instead of A1)

Here the COLUMN function takes cell reference A1 as 1. When we drag the Fill Handle to the right, cell reference changes from A1 to B1, C1, and so on as thus the Day Number.

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

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

This operation will fill the cells D5 to H5 automatically with day numbers (1 to 5).

Similar Readings:

4. Using the RANDARRAY Function to Auto Number Desired Cells in Excel

You can also fill the cells with random numbers instantly by using the RANDARRAY function. This function creates an array of some numbers within a range.

Suppose, you want to predict how much profit the factory can achieve if these machines manufacture products within a range of units per day. We are going to show production for the first 5 days of the week. We also omit the salary range column due to convenience.

First, you need to select Formulas >> Calculation Options >> Manual. Because the RANDARRAY function keeps changing the values it generates.

Now, type the following formula in cell D6.

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

Here, the RANDARRAY function will generate a set of integers within the range of 10 to 21. It will create a 6×5 array as there are 6 categories of machines and 5 numbers of days.

Hit ENTER.

Now select the range D6:H11, press CTRL + C to copy, and right-click on any of these cells. Then select Paste Options >> Values

This operation will remove the formula from D6. Hence these data won’t change anymore. You can use these values for your experiment.

Thus, the RANDARRAY function can fill a 2D array with numbers automatically.

Read more: How to Autofill Dates in Excel

5. Using Series Command to Auto Number Cells in Excel

Suppose, you want to label the Machine Category with a sequential series like 1,3,5, and so on. You can use the Series command from the Fill group to do this.

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

Now select Editing >> Fill >> Series

A dialog box for the Series will appear.

Select Columns in Series in and select Linear in Type.
Now put the step value 2 and stop value 11 and click OK.

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

Similar Readings:

6. Inserting OFFSET Function for Auto Numbering Cells in Excel

We can also put serial numbers for the Machines by using the OFFSET function. Let’s view the process.

Type 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.

Now press ENTER and you will see the output in cell B5.

Select cell B5 and drag the Fill Handle to cell B10. The process is shown in method 1. So, I’ll go straight to the result.

Note: There is a problem while using this function. You need to keep the B4 cell blank. Otherwise, it will provide you with an error. So, to put the heading (Serial) back in cell B4, you should select the range B5:B10, copy them by pressing CTRL + C, and do the right click on any of the selected cells.

Now you just go to the Paste Options and select Values. You will see that the formula is not in those cells anymore. Then you can type the heading in cell B4 without error.

7. Using COUNTA Function to Auto Number Cells in Excel

The COUNTA function can also be used to fill the cells automatically with numbers. We can put the serial numbers of the machines with the help of the COUNTA function.

Type the following formula in cell B5.

=COUNTA($C$5:C5)

Here, the COUNTA function will count non-empty cells of Column C (cells C5 to C10). It counts non-empty cells by the range between absolute cell reference of C5 and general cell reference of C5 to C10.

Press ENTER

Now select cell B5 and drag the Fill Handle button down to B10. Then you will see the serial number of the machine categories.

This is another way of filling the cells automatically with numbers.

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

Another interesting method of filling cells with numbers is converting the dataset into a table. Suppose, we want to put a serial on the machine category. We need to cover the following steps to do this.

Select the entire dataset and then select Insert Tab >> Table. A dialog box will show up, Just click OK.

This operation may change the formatting of the text and cell sizes. Adjust them according to your convenience.

Now type this formula in cell B5.

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

Note: Table names may vary for different individuals. In my case, it was Table9. I marked the area where you can find the table name in the following figure.

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.

Now press ENTER.

We get the cells B5 to B10 filled with numbers automatically. But there is a problem with the third We see that it splits and creates a fourth header named Column1. To solve this problem, we need to select cells D4 and E4, and also select Table Design >> Tools >> Convert to Range.

A warning message will appear.

Now click YES on the dialog box.

You will see the Filter sign removed from the 4th row. Now, just click on Merge & Center from the Home tab.

A warning message will appear.

Click OK on the dialog box.

This operation restores the third column like it was before.

Similar Readings:

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

Another easy method of filling cells automatically with numbers is adding 1 to adjacent rows or columns. Let’s discuss the method below.

Type 1 in cell B5.
Then type the following formula in cell B6.

=B5+1

Now hit ENTER and you will see that the cell value increased to 2.

Now select cell B6 and drag the Fill Handle button down to cell B10.

As a result, we see that the cells B6 to B10 are filled automatically with numbers 1 to 6.

Similar Readings:

10. Using Excel SUBTOTAL Function to Auto Number Cells in Filtered Data

We can use the SUBTOTAL function to put filtered data serially. 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. To solve this problem, we use the SUBTOTAL function.

First, I will terminate rows 5 and 6 entirely by filtering out machine categories A and B.

Open Home tab >> from Sort & Filter >> select Filter.

Now, unmark A and B like the following figure and click OK.

Category A and B will be filtered out. Now type the following formula in cell B7.

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

Here, the arguments in SUBTOTAL functions are 3 and a range $C$7:C7. This 3 means that the SUBTOTAL function will execute a COUNTA operation through column C (C7 to C10). It cumulatively counts the non-empty cells from cell C7 to C10.

Hit ENTER.

Now select cell B7 and drag the Fill Handle button down to cell B10. This will fill cells B7 to B10 with serial numbers 1 to 4.

So, this is the way we can fill cells with numbers automatically for filtered data using the SUBTOTAL function.

Things to Remember

While using the OFFSET function, the cell above the formula cell must be empty.
Don’t forget to turn on Manual Calculation before using the RANDARRAY function.

Download Practice Workbook

Auto Number Cells.xlsx

Conclusion

This article provides some basic methods on how to auto number cells in Excel. We can use these methods in different aspects. The bottom line is when we need to work with a huge dataset in Excel, and if we need to consider a sequence of numbers or random numbers, these methods can be significantly helpful. I hope this article will be helpful for you to understand some basics of Excel. Please leave your valuable feedback questions or ideas in the comment box.

How to Auto Number Cells in Excel (10 Methods)

1. Using Fill Handle to Auto Number Cells in Excel

1.1. Numbering Rows Automatically

1.2. Numbering Columns Automatically

1.3. Numbering Both Rows and Columns Automatically

2. Using Row Function to Auto Number Cells in Excel

3. Applying Column Function to Fill Cells Automatically with Numbers in Excel

4. Using the RANDARRAY Function to Auto Number Desired Cells in Excel

5. Using Series Command to Auto Number Cells in Excel

6. Inserting OFFSET Function for Auto Numbering Cells in Excel

7. Using COUNTA Function to Auto Number Cells in Excel

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

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

10. Using Excel SUBTOTAL Function to Auto Number Cells in Filtered Data

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply