Numbering in Excel

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Numbering in Excel means providing a cell with numbers like serial numbers to some table. We can also do it manually by filling the first two cells with numbers and dragging them down to the end of the table, which Excel will automatically load the series. Else, we can use the =ROW() formula to insert a row number as the serial number in the data or table.

Numbering in Excel

When working with Excel, some small tasks need to be done repeatedly. However, if we know the right way to do it, they can save a lot of time. For example, generating the numbers in Excel is a task that is often used while working. Serial numbers play a very important role in Excel. It defines a unique identity for each record of your data.

One of the ways is to add the serial numbers manually in Excel. But it can be a pain if you have the data of hundreds or thousands of rows and must enter the row number for them.

This article will cover the different ways to do it.

How to Add Serial Number in Excel Automatically?

There are many ways to generate the number of rows in Excel.

You can download this Numbering in Excel Template here – Numbering in Excel Template
  1. Using Fill Handle
  2. Using Fill Series
  3. Using ROW Function

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#1 – Using Fill Handle

It identifies a pattern from a few already filled cells and then quickly uses that pattern to fill the entire column.

For example, let us take the below dataset.

Number in Excel Example 1

For the above dataset, we have to fill the serial no record-wise. Follow the below steps:

  1. First, we must enter 1 in cell A3 and insert 2 in cell A4.

  2. Then, select both the cells as per the below screenshot.


    Number in Excel Example 1-1

    As we can see, a small square is shown in the above screenshot, which is rounded by a red color called “Fill Handle” in Excel.

    Place the mouse cursor on this square and double-click on the fill handle.

  3. It will automatically fill all the cells until the end of the dataset. For example, refer to the below screenshot.


    Number in Excel Example 1-2

    As the fill handle identifies the pattern, fill the individual cells with that pattern.

    If you have any blank row in the dataset, the fill handle would only work until the last contiguous non-blank row.

#2 – Using Fill Series

It gives more control over data on how the serial numbers are entered into Excel.

For example, suppose you have below the score of students subject-wise.

Number in Excel Example 2

Follow the below steps to fill series in the Excel:

  • We must first insert 1 in cell A3.
  • Then, go to the “HOME” tab. Next, click on the “Fill” option under the “Editing” section, as shown in the below screenshot.
Number in Excel Example 2-1
  • Click on the “Fill” dropdown. It has many options. Click on “Series,” as shown in the below screenshot.
Number in Excel Example 2-2
  • It will open a dialog box, as shown below screenshot.
Number in Excel Example 2-3
  • Click on “Columns” under the “Series in” section. Then, refer to the below screenshot.
Number in Excel Example 2-4
  • Insert the value under the “Stop value” field. In this case, we have 10 records; enter 10. If we skip this value, the Fill “Series” option may not work.
  • Enter “OK.” It will fill rows with serial numbers from 1 to 10. Refer to the below screenshot.
Number in Excel Example 2-5

#3 – Using the ROW Function

Excel has a built-in function, which also can be used to number the rows in Excel. To get the Excel row numbering, we must enter the following formula in the first cell shown below:

Number in Excel Example 3
  • The ROW function gives the Excel row number of the current row. We have subtracted 3 from it as we started the data from the 4th. If the data starts from the 2nd row, we must remove 1 from it.
  • See the below screenshot. Using =ROW()-3 formula.
Number in Excel Example 3-1

Drag this formula for the rest of the rows. The final result is shown below.

Number in Excel Example 3

Using this formula for numbering will not damage the numbers if we delete a record in the dataset. Furthermore, since the ROW function does not reference cell addresses, it will automatically adjust to give the correct row number.

Things to Remember about Numbering in Excel

  • The “Fill Handle” and “Fill Series” options are static. Therefore, if we move or delete any record or row in the dataset, the row number will not change accordingly.
  • The ROW function gives the exact numbering if we cut and copy the data in Excel.

This article is a guide to Numbering in Excel. We discuss how to automatically add serial numbers in Excel using the fill handle, fill series, and ROW function, along with examples and downloadable templates. You may learn more about Excel functions from the following articles: –

Reader Interactions

Leave a Reply

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