How to Automatically Number Columns in Excel

Written by Grace

Excel is a Microsoft spreadsheet program which is useful in performing calculations. It is used to create formulas for calculations, texts, and numbers. This app is used by lots of professionals such as financial analysts, business analysts, project managers, managers, accountants, data analysts, and so on. It might be difficult for a newbie to know how things work in this program. Therefore, this post is going to guide you on how to automatically number columns in Excel.

This program is very helpful to record income, expenditures plan budgets, and present fiscal results. It can do simple numbering and also autonumbering. There are methods in Excel that you can follow to achieve this.

 How to automatically number columns in excel

How to automatically number columns in Excel

Automatically numbering columns in Excel

There are different methods you can use to auto number columns in Excel. You can go for any of these methods and get your desired result

How to auto number in Excel using Formula (Row)

To begin numbering in Excel, you can type this formula into the column =ROW() and then press the enter button. Once the first number has appeared, click on the little icon just below at the right side of the first column. Drag it down to all the cells you want to number.

How to auto number in Excel using Formula (OFFSET)

The second way you want number automatically is to type this =OFFSET(A2,-1,0)+1  into the cell A2. Press enter and then drag the cell down so that it will number automatically. To use this method, you need to start typing from cell A2. The cell above it must either be blank or zero. If you want to use this familiar in cell A9, then the cell above it must be blank or zero.

Using AutoFill function to auto number a column

Another method in Excel to auto number a column is using this function. Type 1 into the first cell you want to start numbering from, then drag the handle at the bottom right corner of the cell. Continue dragging until you get to the number you want. Now, click on the little icon appearing at the bottom of your last cell to display the various options. Now select the fill options and tick Fill series.

Even if you delete any number of the cell, it will not affect the numbering in any way.

Using Kutools to automatically number in Excel

Another way to automatically number columns in Excel is by installing the Kutools. Once you have done that, you will be able to insert a sequence number into a column. You will make use of its Insert Sequence Number tool. All you have to do is click on Kutools, then select Insert and then click on Insert Sequence Number.

After this, choose the New button and the dialog will expand, then type in the custom sequence you need. You can define the number of digits, specify prefix and suffix. Now, click on Add to create the sequence and then select the range if the column you want to insert the numbers. Now select Fill Range to fill these numbers


Finally, if you follow any of the methods above, you will be able to automatically number columns while using Excel. It is a simple and easy task to perform while using the spreadsheet program.


About the author


Leave a Comment