This tutorial will demonstrate the steps of how to add 1 to each cell in a column in Excel. When you are making a list of different products or a list of student data, in that case, you put serial. But manually putting serial is very hectic work and there is also a chance of making mistakes. So, we will use add 1 to each cell in a column to serial them easily.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 5 Easy Methods to Add 1 to Each Cell in a Column in Excel

Our aim is to add **1** to each cell in a column in Excel. We can do this with **5** methods. If you follow the steps correctly then you should excel add 1 to each cell in a column on your own. The steps of every method are described below:

### 1. Using Simple Drag to Add 1 to Each Cell in a Column

The quickest formula is to use the dragging way to add 1 to each cell in a column. The steps of this method are:

**Steps:**

- First, arrange the dataset you want to use.

- Next, in the first two cells(in this case
**B5**and**B6**) of the column put**1**and**2**serially into the cells.

- Afterward, select the two cells and drag them downward.

- Last, you will find the result below.

**Read More: How to Add Number to Existing Cell Value in Excel**

### 2. Applying Excel Formula to Auto-Increment by 1 to Each Cell in a Column

We can also add **1 **to each cell in a column by applying the auto-increment formula. It is the easiest and most efficient way among all the methods. The steps are given below:

**Steps:**

- Initially, select the first cell(in this case
**B5**) of the desired column and put**1**in it.

- Next, insert the following formula in the next cell(in this case
**B6**cell):

**=B5+1**

- Afterward, you will get value in that particular cell.
- Then, select the cell and drag the
**Fill Handle**.

- Last, you will get the result below.

### 3. Use of COUNTA Function to Auto-increment

Another process of adding **1 **to each cell in a column is using **the COUNTA Function**. The steps are described below.

**Steps:**

- At first, select the first cell of the desired column(in this case
**B5**). - Then, insert the following formula in the cell:

**=IF(ISBLANK(C5),””,COUNTA($C$5:C5))**

- After getting a result for that cell, drag the
**Fill Handle**.

- Last, you will get results similar to the below image.

**🔎** How does the Formula Work?

**ISBLANK(C5):**This portion of the formula is a logical argument test. At first, it checks if the cell is blank or not, and if it finds blank then returns a**True****COUNTA($C$5:C5)**: In this case, it checks if the cell is**False**or not. It checks all the non-blank cells within our selected range.

### 4. Utilizing ROW Function

By utilizing **the ROW Function**, we can add **1** to each cell in a column. The steps of using this process are described below:

**Steps:**

- First, select the first cell of the desired column(in this case
**B5**). - Then, insert the following formula in the cell.

**=ROW()-4**

- After achieving a result for that cell, drag the
**Fill Handle**.

- Finally, you will get the below result.

### 5. Using SUBTOTAL Function

To fulfill our goal to add **1** to each cell in a column, now we will use **the SUBTOTAL Function**. The steps of this method are.

**Steps:**

- First, select the first cell of the desired column(in this case
**B5**). - Then, insert the following formula in the cell.

**=SUBTOTAL(3,$C$5:C5)**

- Next, you will get results for this cell.
- Then drag the
**Fill Handle**downward from that cell.

- Lastly, you will get the below result on your display screen.

## Things to Remember

- The easiest and most efficient way is using method
**2**for large numbers of data. - In the case of using any of the last
**3**methods, it should be remembered that the functions are put in a certain cell at first and then drag downward to apply the same formula in every cell.

## Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to excel add 1 to each cell in a column. Let us know if you have more ways to do the task. Follow the **ExcelDemy** website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.