How to Number Columns in Excel Automatically (5 Easy Ways)

Automatically Number Columns in Excel Using IF, ISBLANK and COUNTA Functions

While working in Excel, we often have to number our rows and columns for quick glancing and sophistication in analyzing. Today I will be showing how you can number columns in Excel automatically.


Download Practice Workbook


How to Number Columns in Excel Automatically

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

Data Set to Number Columns in Excel Automatically

Our objective today is to enter values inside the row called Product No., that is, to number the columns.


1. Using the Fill Handle

You can use the Fill Handle of Excel to number the columns.

  • Select the first cell and enter 1.

Entering 1 in the First Cell

  • Move your mouse cursor over the rightmost bottom corner of the first cell. You will find a small plus (+) sign. This is called Fill Handle.

Fill Handle in Excel

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

Auto Fill Options in Excel

  • After clicking the Auto Fill Options drop-down menu, you will find a few options. Click on Fill Series.

Selecting Fill Series from the Auto Fill Options in Excel

  • You will find your columns numbered automatically 1, 2, 3, …., etc.

Automatically Numbered Columns in Excel


2. Using the Fill Series Tool from Excel Toolbar

If you want, you can number your columns using the Fill Series tool from Excel Toolbar.

  • Enter 1 in the first cell, then select the whole row.
  • Go to the Home>Fill option (as seen in the image below)  in the Excel toolbar under the section Editing.

Fill Option in Excel Toolbar

  • Click on the drop-down menu attached with the tool Fill. You will find a few options. Click on Series.

Drop-down Menus with the Fill Option

  • You will get the Series dialogue box.

From the Series in menu, select Rows.

Then from the Type menu, select Linear.

And in the Step value box, enter 1.

Series Dialogue Box in Excel

  • Click on OK. You will find your columns numbered automatically 1, 2, 3, …, etc.

Automatically Numbered Columns in Excel


3. Using Excel Functions

In case you don’t like the procedures mentioned above, you can use Excel Functions to number your columns.

Method 1: Using the COLUMN Function

You can use the COLUMN function of Excel to number your columns.

Select the first cell and enter this formula in the Excel Formula Bar:

=COLUMN(Relative Cell Reference of the Cell)-COLUMN(Absolute Cell Reference of the Previous Cell)

In this example, it will be:

=COLUMN(B3)-COLUMN($A$3)

COLUMN Function to Number Columns Automatically in Excel

Then drag the Fill Handle rightward up to the last column. You will find all your columns numbered 1, 2, 3, …, etc.

Dragging the Fill Handle in Excel.


Method 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 B3) is not blank and it contains the text “Product No”.

But look at the following data set. Here the immediate right cell is blank.

Data Set to Number Columns in Excel Automatically

For these types of data sets, enter this formula in the first cell:

=OFFSET(Relative Cell Reference of the Previous Cell,0,0,1,1)+1

In this example, it will be:

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

OFFSET Function to Number Columns in Excel Automatically

Then drag the Fill Handle rightward up to the last column. You will get all your columns numbered 1, 2, 3, …, etc.

Dragging the Fill Handle in Excel.


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

Data Set to Number Columns in Excel Automatically

You can use a combination of the IF, ISBLANK, and COUNTA functions of Excel for numbering the columns of these types of datasets.

To number the columns of these types of data sets, enter this formula in the first cell:

=IF(ISBLANK(C4),"",COUNTA($C$4:C4))

IF, ISBLANK, and COUNTA Functions to Number Columns in Excel

Note: Here, C4 is the cell reference of the cell just below my first cell. You use your one.

Then drag the Fill Handle rightward up to the last column. You will find your columns numbered excluding the blank column.

Dragging the Fill Handle 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 Number Columns in Excel Using IF, ISBLANK and COUNTA Functions

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.

Dragging the Fill Handle in Excel.


Conclusion

Using these methods, you can number columns in Excel automatically. You can number them using either the tools or functions of Excel, the one you prefer. Do you have any questions? Feel free to ask us.

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