Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Number Columns in Excel Automatically (3 Suitable Ways)

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.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Suitable Ways to Number Columns in Excel Automatically

Here we have a data set with the sales record of some products for some years in a company called Jupyter Group.

Sample Dataset to Number Columns Automatically in Excel

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.

Insert First Column Number

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

Drag Fill Handle to Number Columns Automatically in Excel

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

Choose the Fill Series Option to Number Columns Automatically in Excel

Thus, you will find your columns numbered automatically 1, 2, 3, …., etc. And the result would look like this.

Numbered Columns Automatically in Excel

Read more: How to Number Rows Automatically in Excel


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 do this.

📌 Steps:

  • First and foremost, Enter 1 in the first cell (cell C4 here).

Insert First Column Number

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

Choose the Series Option to Number Columns Automatically in Excel

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

Series Window to Number Columns Automatically in Excel

As a result, you will find your columns numbered automatically 1, 2, 3, …, etc. And, the final outcome should look like this.

Numbered Columns Automatically in Excel

Read more: How to AutoFill Numbers in Excel


Similar Readings


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)

COLUMN Function to Number Columns Automatically in Excel

  • Afterward, drag the Fill Handle rightward up to the last column.

Drag Fill handle Rightward

Thus, you will find all your columns numbered 1, 2, 3, …, etc. And, the output should look like this.

Numbered Columns Automatically in Excel


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

Newly Customized Dataset

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

So, in this example, it will be:

=OFFSET(B5,0,0,1,1)+1

OFFSET Function to Number Columns Automatically in Excel

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

Automatically Numbered Columns in Excel


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.

Dataset with Blank Column

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.

Merging IF, ISBLANK, COUNTA Functions to Number Columns Automatically in Excel

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

Automatically Numbered Columns with Blank Columns in Excel

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.

Automatically Numbered Columns with Non Blank Column in Excel

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 Autofill a Column 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.

Sample Dataset to Number Rows

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.

Insert First Row Number

  • Afterward, place your mouse cursor in the bottom right position of the cell and drag the fill handle below upon its appearance.

Drag Fill Handle Below

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

Choose the Fill Series Option

Thus, all your rows will be numbered automatically as per your desire. And, the outcome would look like this.

Automatically Numbered Rows in Excel


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.

And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo