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.
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.
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 ExcelAuto Numbering In ExcelThere are two approaches for auto numbering. To begin, fill the first two cells with the series you wish to insert and drag it to the table's end. Second, use the =ROW() formula to get the number, and then drag it to the table's end.
- Row Limit in Excel
- Excel Negative NumberExcel Negative NumberIn Excel, negative numbers, i.e. numbers less than 0 in value, are displayed with minus symbols by default.
- IsNumber FunctionIsNumber FunctionISNUMBER function in excel is an information function that checks if the referred cell value is numeric or non-numeric.
- Null in ExcelNull In ExcelNull is a type of error in Excel that occurs when two or more cell references provided in formulas are wrong, or when their position is incorrect. Using space in formulas between two cell references, for example, will result in a null error.