For example, suppose you have a data set and want to start numbering them from 3 and step up by 4 until 40. First, you may select the cells containing the 3 and the 7. Then, click on the handle and drag to the other cells where you can see 40. You may then release and view that the numbers are filled in automatically.

You may also use the Excel ROW function = ROW() formula that returns the row number for reference. For example, ROW(A3) returns 3 as A3 is the third row in the spreadsheet. If not given any reference, ROW returns the cell’s row number, which consists of the formula.

Automatic Numbering in Excel

Numbering means giving serial numbers or numbers to a list of data. In Excel, no special button is provided that offers numbering for our data. As we already know, Excel does not provide a method, tool, or button to provide the sequential number to a list of data, which means we need to do it manually.

How to Auto Number in Excel?

Now we have checked that the “Autofill” is enabled. There are three methods for Excel auto-numbering:

  • Fill a column with a series of numbers.Using the Row() function.Using the Offset() function.

Top 3 ways to get Auto Numbering in Excel

There are various ways to get automatic numbering in Excel.

  • In the “File” tab, go to “Options.” In the advanced section, check the “Enable fill handle and cell drag and drop” in Excel under the editing options.

  • Fill a column with a series of numbers.Use Row() functionUse Offset() Function

Let us discuss all the above methods by examples.

#1 – Filling the Column with a Series of Numbers

We have the following data:

We will insert automatic numbers in Excel “column A” following the below steps:

  • We can select the cell we want to fill. In this example, we will choose cell A2.We will write the number we want to start with. Let it be 1, fill the next cell in the same column with another number, and let it be 2.

  • To start a pattern, we have made the numbering 1 in cell A2 and 2 in cell A3. Next, we will select the starting values, i.e., cells A2 and A3.

  • The arrow shows the pointer (dot) in the selected cell. Then, click on it and drag it to the desired range, 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 the Row() functionRow() FunctionThe row function in Excel is a worksheet function that displays the current row index number of the selected or target cell. The syntax to use this function is as follows: =ROW( Value ).read more.

  • Below is our data:

  • We select the specific cell where we want to start our automatic numbering in Excel (cell A2 in this case).

  • We type ROW() in cell A2 and press “Enter.”

As a result, it gave us the numbering from number 2 since the Row function throws the number for the current row.

  • To avoid the above situation, we can give the reference row to the Row function.

  • Next, we click on the pointer or the dot in the selected cell and drag it to the desired range for the current scenario to cell A11.

  • We have automatic numbering in Excel for the data using the Row() function.

#3 – Using Offset() Function

We can also achieve auto numbering in Excel using the Offset() functionUsing The Offset() FunctionThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. read more also.

Let us use the same data to demonstrate the Offset function. Below is the data:

As we can see, we have removed the text written in cell A1 “Serial Number,” as the reference needs to be blank while using the Offset function.

The above screenshot shows the function arguments used in the Offset function.

  • In cell A2, we type offset(A2,-1,0)+1 for the automatic numbering in Excel.

A2 is the current cell address, which is the reference.

  • Press the “Enter” key. It will insert the first number.

  • Now, we select cell A2 and drag it down to cell A11.

  • So, now we have sequential numbering using the Offset function.

Things to Remember about Auto Numbering in Excel

  • Excel does not auto-provide auto-numbering.Do not forget to check for the enabled “AutoFill” option.While filling a column with a series of numbers, we must make a pattern. We can use the starting values as 2 or 4 to create even sequential numbering.

Auto Numbering in Excel Video

This article is a guide to Auto Numbering in Excel. Here, we discuss how to auto-number in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful Excel tools: –

  • Excel VBA OFFSETNumbering in ExcelWEEKDAY in ExcelWEEKDAY In ExcelThe WEEKDAY function in excel returns the day corresponding to a specified date. The date is supplied as an argument to this function. read moreEquations in Excel