How to Add 1 to Each Cell in a Column in Excel (5 Easy Methods)

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.

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

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 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):

• 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:

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

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

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.

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

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

Download Practice Workbook

You can download the practice workbook from here.

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Add Digits to a Number in Excel
• Adding a Number in Front of a Number in Excel
• How to Add 2 Digits to a Number in Excel

<< Go Back to Add to Cell Value | Sum in Excel | Calculate in Excel | Learn Excel