While working in Excel, we often have to number our rows and columns for quick glancing and sophistication in analyzing. You can do this by using several ways. In this article, I will show you how to number columns in Excel automatically.
Automatically Number Columns in Excel: 3 Suitable Ways
Here we have a data set with the sales record of some products for some years in a company called Jupyter Group.
Our objective today is to enter values inside the row called Product No., that is, to number the columns.
1. Using Fill Handle Tool to Number Column in Excel
You can use the Fill Handle of Excel to number the columns automatically. Follow the steps below to accomplish this.
📌 Steps:
- First, select the first cell (C4 here) and enter 1.
- Afterward, move your mouse cursor over the rightmost bottom corner of cell C4. You will find a small plus (+) sign. This is called Fill Handle.
- Following, drag the Fill Handle rightward up to your last non-blank row. You will find all cells of the column having the number 1 automatically.
- Then you will find a small drop-down menu in the rightmost bottom corner of the column called Auto Fill Options. Click it.
- Consequently, the Auto Fill Options drop-down menu will appear.
- Subsequently, click on the Fill Series option.
Thus, you will find your columns numbered automatically 1, 2, 3, …., etc. And the result would look like this.
2. Using the Fill Series Tool from Excel Toolbar
Besides, if you want, you can number your columns using the Fill Series tool from Excel Toolbar. Go through the steps below to create a number sequence in Excel without dragging.
📌 Steps:
- First and foremost, Enter 1 in the first cell (cell C4 here).
- Afterward, select the whole row.
- Following, go to the Home tab >> Fill option (as seen in the image below)Â in the Excel toolbar under the section Editing.
- Afterward, click on the drop-down menu attached with the tool Fill. You will find a few options. Click on Series.
- Consequently, you will get the Series dialogue box.
- Following, from the Series in menu, select Rows.
- Afterward, from the Type menu, select Linear.
- And in the Step value box, enter 1.
- Last but not least, click on the OK button.
As a result, you will find your columns numbered automatically 1, 2, 3, …, etc. And, the final outcome should look like this.
Read More: How to Auto Generate Number Sequence in Excel
3. Using Excel Functions to Automatically Number Columns
In case you don’t like the procedures mentioned above, you can use Excel Functions to number your columns.
3.1 Using the COLUMN Function
You can use the COLUMN function of Excel to number your columns. Follow the steps below to do this.
📌 Steps:
- At the very beginning, select the first cell (cell C4 here) and enter this formula in the Excel Formula Bar:
=COLUMN(Relative Cell Reference of the Cell)-COLUMN(Absolute Cell Reference of the Previous Cell)So, in this example, it will be:
=COLUMN(B4)-COLUMN($A$4)
- Afterward, drag the Fill Handle rightward up to the last column.
Thus, you will find all your columns numbered 1, 2, 3, …, etc. And, the output should look like this.
3.2 Using the OFFSET Function
You can also use the OFFSET function of Excel to number columns in Excel.
But you can use the OFFSET function for only those types of data sets where the immediate right cell of the first cell is blank.
In our data set, the immediate right cell of our first cell (cell B4) is not blank and it contains the text “Product No”.
But look at the following data set. Here the immediate right cell is blank. So, you can apply this function here. Follow the steps below to do this.
📌 Steps:
- First, for these types of data sets, enter this formula in the first cell (cell C5 here):
=OFFSET(Relative Cell Reference of the Previous Cell,0,0,1,1)+1So, in this example, it will be:
=OFFSET(B5,0,0,1,1)+1
- Afterward, drag the Fill Handle rightward up to the last column.
Thus, you will get all your columns numbered 1, 2, 3, …, etc. And, the final result would look like this.
3.3 Using the IF, ISBLANK, and COUNTA Functions (For Columns with Blank Cells)
This method will be useful when you have any blank columns in your data set.
For example, let’s think that Jupyter Group has stopped selling Smartphones, and that’s why they have cleared the column Smartphone from their data set.
You can use a combination of the IF, ISBLANK, and COUNTA functions of Excel for numbering the columns of these types of datasets. Follow the steps below to accomplish this.
📌 Steps:
- Initially, to number the columns of these types of data sets, enter this formula in the first cell (cell C4 here).
=IF(ISBLANK(C5),"",COUNTA($C$5:C5))Note:
Here, C5 is the cell reference of the cell just below my first cell. You use your one.
🔎 Formula Breakdown:
- =ISBLANK(C5)
This finds if the C5 cell is blank or not. Upon this finding, it would return TRUE or FALSE.
Result: False
- =COUNTA($C$5:C5)
This would count how many non-blank cells are in the specified range C5:C5.
Result: 1.
- =IF(ISBLANK(C5),””,COUNTA($C$5:C5))
This returns a logical operation. If the first breakdown result here is TRUE, it would return a blank cell. If the first breakdown result is FALSE, it would return the second breakdown result here.
Result: 1.
- Following, drag the Fill Handle rightward up to the last column. You will find your columns numbered excluding the blank column.
Thus, you will get your desired result which should look like this.
Special Note:
This is a dynamic formula.
That means, if you have a data set with all the columns non-blank, then it will number all the columns like this.
Then if you suddenly clear one column, it will automatically adjust and re-number them excluding the blank column.
You will not need to do anything.
Read More: How to Add Automatic Serial Number with Formula in Excel
How to Number Rows Automatically in Excel
Now, sometimes, it might occur to you that, you need to number rows automatically instead of columns. This is quite the same as numbering columns. Still, for a better understanding, say, you have 5 products in row by row.
Now, you want to number them, automatically. Now, follow the steps below to number rows in Excel automatically.
📌 Steps:
- First, click on the first cell (cell B5 here) and insert 1.
- Afterward, place your mouse cursor in the bottom right position of the cell and drag the fill handle below upon its appearance.
- As a result, all the cells below will now have 1 inside the cells.
- Then you will find a small drop-down menu in the rightmost bottom corner of the column called Auto Fill Options. Click it.
- Consequently, the Auto Fill Options drop-down menu will appear.
- Subsequently, click on the Fill Series option.
Thus, all your rows will be numbered automatically as per your desire. And, the outcome would look like this.
Download Practice Workbook
You can download our practice workbook from here for free!
Conclusion
So, in this article, I have shown you 3 suitable ways to automatically number columns in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
Further Readings
- How to Autofill in Excel with Repeated Sequential Numbers
- How to Do Automatic Numbering in Excel
- Auto Numbering in Excel After Row Insert
- Auto Serial Number in Excel Based on Another Column
- Auto Generate Invoice Number in Excel
- Auto Generate Serial Number in Excel VBA
<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
