Home  ›  How to Make Continuous Numbering in Excel

How to Make Continuous Numbering in Excel

Written By Thursday, October 27, 2022 Add Comment Edit

While performing a simple numbering in Excel, we manually provide a cell number for the serial number; we can also do it automatically. To do auto numbering, we have two methods. First, 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. The second method is to use the =ROW () formula which will give us the number, and then drag the formula similar to the end of the table.

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 row number of the cell, 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.

Table of contents
  • Automatic Numbering in Excel
    • How to Auto Number in Excel?
    • Top 3 ways to get Auto Numbering in Excel
      • #1 – Filling the Column with a Series of Numbers
      • #2 – Use ROW() Function
      • #3 – Using Offset() Function
    • Things to Remember about Auto Numbering in Excel
    • Auto Numbering in Excel Video
    • Recommended Articles

How to Auto Number in Excel?

We need to remember that ourautofill function AutoFill in excel can fill a range in a specific direction by using the fill handle. read more is always turned on to create auto numbering in Excel. By default, it is turned on. But in any case, if it is not enabled or mistakenly disabled "AutoFill," here is how we can re-enable it.

Now we have checked that 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.

The following 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, check the "Enable fill handle and cell drag and drop" in Excel under the editing options.


    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 – Filling the Column with a Series of Numbers

We have the following data:

Fill the Series with Number Example 1

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.
Fill the Series with Number Example 1-1
  • 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.
Fill the Series with Number Example 1-2
  • The arrow shows the pointer (dot) in the selected cell. Then, click on it and drag it to the desired range, 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 therow() function The 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
  • We select the specific cell where we want to start our automatic numbering in Excel (cell A2 in this case).
Use Row Function Example 1-1
  • We type ROW() in cell A2 and press "Enter."
Use Row Function Example 1-2

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.
Use Row Function Example 1-3
  • 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.
Use Row Function Example 1-4
  • 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 also achieve auto numbering in Excel using the Offset() function The 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:

Using Offset() Function Example 1

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.

Using Offset() Function Example 1-1

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

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

  • Press the "Enter" key. It will insert the first number.
Using Offset() Function Example 1-3
  • Now, we select cell A2 and drag it down to cell A11.
Using Offset() Function Example 1-4
  • So, 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. Do not forget to check for the enabled "AutoFill" option.
  3. While filling a column with a series of numbers, we must make a pattern. We can either 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 get sequential numbers in Excel using 1 Automatically) Fill the Column 2) ROW() Function and 3) Offset Function along with Excel examples and downloadable Excel templates. You may also look at these useful Excel tools: –

  • Excel VBA OFFSET
  • Numbering in Excel
  • WEEKDAY in Excel The WEEKDAY function in excel returns the day corresponding to a specified date. The date is supplied as an argument to this function. read more
  • Equations in Excel

hodgesallation.blogspot.com

Source: https://www.wallstreetmojo.com/auto-numbering-in-excel/

0 Response to "How to Make Continuous Numbering in Excel"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel