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.


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.

Simple Drag to Add 1 to Each Cell

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

Simple Drag to Add 1 to Each Cell

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

Simple Drag to Add 1 to Each Cell

  • Last, you will find the result below.


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

Methods to Add 1 to Each Cell

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

Methods to Add 1 to Each Cell

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

Methods to Add 1 to Each Cell

🔎 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

Methods to Add 1 to Each Cell

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

Methods to Add 1 to Each Cell

  • Finally, you will get the below result.

Methods to Add 1 to Each Cell


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)

Methods to Add 1 to Each 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.

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.

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo