There are various ways to auto number cells in Excel. Auto numbering cells mean 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.

## Download Practice Workbook

## 10 Ways to Auto Number Cells in Excel

### 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 by this feature. I will be describing 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
**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 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 on the
**Cursor**on**Fill Handle**button and double-click on it or drag it down to cell

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

### 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 **which 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)**.

### 4. Using 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 f**irst 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 any more. You can use these values for your experiment.

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

### 5. Generating Series to Auto Number Cells in Excel Using Series Command

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** of **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.

### 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 **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 was 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 a Table to Fill the Cells Automatically in Excel

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

### 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**.

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

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 ou**t **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.

## Practice Section

Here Iâ€™m giving you the dataset. You can practice the methods mentioned in this article using this worksheet. Try to create a series by the **Fill **command, generate random numbers using the **RANDBETWEEN **function, etc.

## 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 or questions or ideas in the comment box.