Zehad Rian Jim
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.
- 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.
🔎 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.
- 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.
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.
