Other Microsoft Office Programs always provide users with a way to automatically number their data. On the other hand, Microsoft Excel does not provide its users with a button or command that can automatically do so. But, this should not be a hard task to learn for any excel user as you can easily add numbers sequentially to your rows or columns. Fortunately, Excel has different methods you can apply to add numbers automatically. Below, we guide you on how to pre-number in Excel.
How to pre-number in Excel by dragging cells
1. In an open Excel workbook, select the first cell in the range that you want to number.
2. In that cell, type in the starting value for the series.
3. To make sure you establish a pattern, type in a value in the next cell
4. Next, select the cells that contain the starting values you had typed in.
5. Drag down the fill handle at the bottom right corner of the selected cells to the range you want to fill.
Note:
If you want to pre-number or fill in increasing order, drag down the fill handle, or you can also drag it to the right. If you want to fill in decreasing order, drag the fill handle icon up or to the left.
Pre numbering rows in Excel using the ROW function
1. In an open Excel worksheet, go to the first cell range that you want to number.
2. In that cell, type =ROW(A1)
3. Press the Enter key.
4. Drag the autofill handle icon across the range that you want to pre number
Note:
The =ROW() function will return the number of the row that you reference. For instance, =ROW (A1) will return the number 1.
If your lists starts at row 2 then you can change the formula to =Row()-1 . Replace -1 with whatever the difference of rows the lists starts at.
After dragging the fill handle, the numbers will be updated automatically. Your number sequence may be interrupted when you add, delete, or move numbered rows within the worksheet.
How to pre-number in Excel using the fill feature
The fill feature can be useful when you want to pre-number hundreds, thousands, or millions of Excel rows and columns. Furthermore, you can prenumber following a certain pattern. This feature allows you to set a step number that allows you to create a pattern.
1. Write the first number in a cell
2. Click on the fill icon
Select columns then enter the step value and the stop value
Pre numbering a column in Excel using a formula
You can also pre-number your Excel cells using the formula =OFFSET(A2,-1,0)+1
This formula is used when you want to auto-number the column cells not based on the row number in your Excel worksheet.
1. Therefore, type in the formula above into cell A2 where you want to begin the number series from 1.
Lastly, press the Enter key and drag down the autofill handle icon to the cells you need to be filled.
Pre numbering cells in Excel using the AutoFill Function
The AutoFill function in Excel is an excellent tool that can help your number a column. To do this:
1. Type in ‘1’ in a cell you want to start numbering from. Drag the autofill icon handle that is at the bottom right corner of that cell.
2. Click the fill options to expand the options given. Check the Fill Series option and your cells will automatically be numbered.
Note:
Here when you delete one number of the series, the other numbers will remain as they are. They will not update to a new number series.
Similar read: How to add sign in Excel before a Number
How to create a number Sequence in Excel
If you are working on a very long list, then pre-numbering by dragging the fill handle can be tedious. For this reason you need to pre-number automatically using Excel’s in-built features.
1. Click on the first cell where you wish to create a number sequence and type in your first number
2. Click on Fill under the Home Tab and select
3. A pop up window will appear
4. Under this pop up window, select the step value and stop value. For example in this automatically pre number I want to number the columns up to 10,000. Under series in this is where you choose whether you want to number the rows or columns automatically. Since in this example we are numbering columns I will selects columns.
Step value: This is the difference between each number that you want to auto number. If you wish to skip a number select 2 otherwise leave it as it is.
Stop Value: This is the last number you wish to pre number
5. Click on Okay
6. As you can see from the image above we have pre numbered the columns up to 10,000
Final Thoughts
In this article, we have seen how to pre number rows and columns in Excel. You can do it using the drag to fill option, or the Row function. However, if you wish to pre number a large data set in Excel, its better to use the fill series option. With this feature you can choose either row or column. Additionally, you can choose the step and stop values.