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

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 Use Autofill Formula 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 Repeat Formula Pattern 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.

**Similar Readings:**

**AutoFill Cell Based on Another Cell in Excel****How to AutoFill from List in Excel****How to Apply AutoFill Shortcut in Excel****How to Create a Custom AutoFill List in Excel****How to Fill Down Blanks in Excel**

## 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 Sequential Letters in Excel**

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

**How to AutoFill Numbers in Excel****How to Autofill Numbers in Excel Without Dragging****How to AutoFill Ascending Numbers in Excel****How to AutoFill Numbers in Excel with Filter****How to Auto Number or Renumber after Filter in Excel**

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

**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:Â **

**How to Auto Generate Number Sequence in Excel****How to Add Sequence Numbers by Group in Excel****How to Repeat Number Pattern in Excel****Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows**

## 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 Enter Sequential Dates Across Multiple Sheets 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.

**Read more: How to Increment Month by 1 in Excel**

## 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:Â **

**How to Autofill Dates in Excel Without Dragging****How to Autofill Days of Week Based on Date in Excel****How to AutoFill Months in Excel****How to Create Automatic Rolling Months in Excel**

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

**How to Auto Populate from Another Worksheet in Excel****How to Perform Predictive AutoFill in Excel****Applications of Excel Fill Series****[Fix] Excel Fill Series Not Working****Fix: Excel Autofill Not Working**

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

**Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table**

## 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 a Column in Excel****How to Fill Column in Excel with Same Value****How to Fill Down to Last Row with Data in Excel****How to AutoFill Formula When Inserting Rows in Excel****Filling a Certain Number of Rows in Excel Automatically****How to Turn Off AutoFill in Excel****[Fixed!] Auto Fill Options Not Showing in Excel****[Solved:] Excel Double Click AutoFill Not Working****Excel VBA: Autofill Method of Range Class Failed****How to Use VBA AutoFill in Excel****AutoFill Formula to Last Row with Excel VBA****Drag Number Increase Not Working in Excel**