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.
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.
- 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.
- 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.
- After clicking the Auto Fill Options drop-down menu, you will find a few options. Click on Fill Series.
- You will find your columns numbered automatically 1, 2, 3, …., etc.
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.
- Click on the drop-down menu attached with the tool Fill. You will find a few options. Click on Series.
- 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.
- Click on OK. You will find your columns numbered automatically 1, 2, 3, …, etc.
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:
Then drag the Fill Handle rightward up to the last column. You will find all your columns numbered 1, 2, 3, …, etc.
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.
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:
Then drag the Fill Handle rightward up to the last column. You will get all your columns numbered 1, 2, 3, …, etc.
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.
To number the columns of these types of data sets, enter this formula in the first cell:
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.
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.
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.