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

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

**Read More: **How to AutoFill Numbers in Excel

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

**Read more: **How to AutoFill Ascending Numbers in Excel

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

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

**Read More:** How to AutoFill Numbers in Excel with Filter

## 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, the cell reference changes from **A1 **to **B1, C1, **and so on 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 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 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 anymore. You can use these values for your experiment.

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

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

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

**Read More: **How to Auto Number or Renumber after Filter in Excel

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

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

**Download Practice Workbook**

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

## Related Articles

- How to Autofill Numbers in Excel Without Dragging
- Drag Number Increase Not Working in Excel
- [Fix] Excel Fill Series Not Working

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