Table Of Contents
How to Add Serial Number in Excel Automatically?
There are many ways to generate the number of rows in Excel.
- Using Fill Handle
- Using Fill Series
- 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.
For the above dataset, we have to fill the serial no record-wise. Follow the below steps:
- First, we must enter 1 in cell A3 and insert 2 in cell A4.
- Then, select both the cells as per the below screenshot.
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. - It will automatically fill all the cells until the end of the dataset. For example, refer to the below screenshot.
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.
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.
