How to Automatically Number Columns in Excel

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.


Get the low down

Receive the latest news & updates from our team.

Recent Updates

Adobe Photoshop is by far the most popular graphic design software. Professionals, students, and hobbyists use it for everyday image editing. Photoshop is so popular…

There is a prediction that small businesses that use VoIP can reduce their startup costs by 90%. Surprising right? As the marketplace moves quickly, small…

The meteoric rise in the development of AI (Artificial Intelligence) is here.People want to know what the best AI tools are to use. We have…

USED! Doodly is a whiteboard animation software that is used to create videos. The best thing about this software is that you do not need…

AutoCAD is one of the oldest, popular and reliable computer aided drawing software. This software has been available since 1982 and is often referred to…