Numbering in Excel

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.

1. Using Fill Handle
2. Using Fill Series
3. Using ROW Function

#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.

#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.

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.

• Click on the "Fill" dropdown. It has many options. Click on "Series," as shown in the below screenshot.

• It will open a dialog box, as shown below screenshot.

• Click on "Columns" under the "Series in" section. Then, refer to the below screenshot.

• 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.

#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:

• 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.

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

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.

Recommended Articles

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: -

• Auto Numbering in Excel
• Row Limit in Excel
• Excel Negative Number
• IsNumber Function
• Null in Excel