Where we can do a simple numbering in excel where we manually provide a cell a number for the serial number we can also make it to do automatically, to do an auto numbering we have two methods, first is we can fill the first two cells with the series of the number we want to be inserted and drag it down to the end of the table and second method is to use the =ROW() formula which will give us the number and then drag the formula in the similar way to the end of the table.
Automatic Numbering in Excel
Numbering means giving serial numbers or numbers to a list or data. In excel, there is no special button provided, which gives numbering for our data. As we already know, excel does not provide a method or tool or a button to provide the sequential number to a list of data, which means we need to do this by ourselves.
How to Auto Number in Excel?
To do auto numbering in excel, we need to remember that our autofill functionAutofill FunctionAutofill is a function where excel identifies the series and fills the data automatically for us when we drag down the data. For example, if a cell value 1 and another cell below have value 2, we can select both the cells and drag them down, and it gets automatically filled. is turned on. By default, it is turned on but in any case, if we do not have enabled or by mistakenly disabled autofill, here is how we can re-enable it.
Now we have checked the autofill is enabled, there are three methods for excel auto-numbering,
- Fill a column with a series of numbers.
- Using row() function.
- Using offset() function.
Below are the steps for enabling fill handle and cell drag and drop –
- In the file, tab go-to options.
- In the advanced section, under the editing options, check the Enable fill handleEnable Fill HandleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications. and cell drag and drop in excel.
Top 3 ways to get Auto Numbering in Excel
There are various ways to get automatic numbering in excel.
- Fill a column with a series of numbers.
- Use Row() function
- Use Offset() Function
Let us discuss all the above methods by examples.
#1 – Fill the Column with Series of Numbers
We have the following data,
We will try to insert automatic numbers in excel Column A.
- Select the cell in which we want to fill in this example, cell A2.
- Write the number we want to start with, letting it be 1, and fill the next cell in the same column with another number, let it be 2.
- We did the numbering 1 in cell A2 and 2 in cell A3 to start a pattern. Now select the starting values, i.e., cell A2 & A3.
- The pointer ( dot) in the selected cell is shown by the arrow; click on it and drag it to the desired range, i.e., cell A11.
Now we have sequential numbering for our data.
#2 – Use ROW() Function
We will use the same data to demonstrate the sequential numbering by row() function.
- Below is our data,
- In cell A2 or where we want to start our automatic numbering in excel, we select the specific cell.
- Type =ROW() in cell A2 and press enter.
It gave us the numbering from number 2 because the row function throws the number for the current row.
- To avoid the above situation, we can give the reference row to row function.
- The pointer or the dot in the selected cell click on it and drag it to the desired range for the current scenario to cell A11.
- Now we have our automatic numbering in excel for the data using the row() function.
#3 – Using Offset() Function
We can do auto numbering in excel by using the Offset() functionUsing The Offset() FunctionThe offset function in Excel displays the range of parts from the cell's start point. It takes five arguments, all of which are mandatory. This function is used in the following way: =offset(reference, rows, columns, height, width), where height and width refer to the referred reference. also.
Again we use the same data to demonstrate the offset function. Below is the data,
As we can see, I have removed the text written in cell A1 “Serial Number” as while using the offset function, the reference needs to be blank.
The above screenshot shows the function arguments used in the offset function.
- In Cell A2 type =offset(A2,-1,0)+1 for the automatic numbering in excel.
A2 is the current cell address, which is the reference.
- Press Enter, and the first number is inserted.
- Select Cell A2, Drag it down to cell A11.
- Now we have sequential numbering using the offset function.
Things to Remember about Auto Numbering in Excel
- Excel does not auto provide auto-numbering.
- Check for AutoFill option checked enabled.
- When we fill a column with a series of numbers, we make a pattern; we can either use the starting values as 2, 4 to create even sequential numbering.
Auto Numbering in Excel Video
This has been a guide to Auto Numbering in Excel. Here we discuss how to Automatically get sequential numbers in Excel using 1) Fill the Column, 2) ROW() Function, and 3) Offset Function along with excel example and downloadable excel templates. You may also look at these useful excel tools –