Auto Numbering in Excel

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 in excel can fill a range in a specific direction by using the fill more 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,

  1. Fill a column with a series of numbers.
  2. Using row() function.
  3. Using offset() function.

Below are the steps for enabling fill handle and cell drag and drop –

  1. In the file, tab go-to options.

    auto numbering example 1

  2. In the advanced section, under the editing options, check the Enable fill handle and cell drag and drop in excel.

    auto numbering example 1-1

Top 3 ways to get Auto Numbering in Excel

There are various ways to get automatic numbering in excel.

You can download this Auto Numbering Excel Template here – Auto Numbering Excel Template
  1. Fill a column with a series of numbers.
  2. Use Row() function
  3. 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,

Fill the Series with Number Example 1

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.
Fill the Series with Number Example 1-1
  • 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.
Fill the Series with Number Example 1-2
  • 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.
Fill the Series with Number Example 1-3

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() 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,
Use Row Function Example 1
  • In cell A2 or where we want to start our automatic numbering in excel, we select the specific cell.
Use Row Function Example 1-1
  • Type =ROW() in cell A2 and press enter.
Use Row Function Example 1-2

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.
Use Row Function Example 1-3
  • 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.
Use Row Function Example 1-4
  • Now we have our automatic numbering in excel for the data using the row() function.
Use Row Function Example 1-5

#3 – Using Offset() Function

We can do auto numbering in excel by 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.

Again we use the same data to demonstrate the offset function. Below is the data,

Using Offset() Function Example 1

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.

Using Offset() Function Example 1-1

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.
Using Offset() Function Example 1-2

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

  • Press Enter, and the first number is inserted.
Using Offset() Function Example 1-3
  • Select Cell A2, Drag it down to cell A11.
Using Offset() Function Example 1-4
  • Now we have sequential numbering using the offset function.
Using Offset() Function Example 1-5

Things to Remember about Auto Numbering in Excel

  1. Excel does not auto provide auto-numbering.
  2. Check for AutoFill option checked enabled.
  3. 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


Recommended Articles

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *