OmniDocHow to automatically generate Excel serial numbers: How to use the serial number function
Use ROW, COUNTA and SUBTOTAL functions to generate Excel serial numbers. The serial numbers remain continuous after rows are deleted, inserted, and filtered. Explain the applicable scenarios, operation steps and inspection methods of each writing method.
Key points of this article
- Directly enter 1, 2, 3 and then pull down the copied sequence number. After deleting the row, a broken number will be left and will not be automatically rearranged.
- The serial number generated by the ROW function follows the position of the row, and will be automatically renumbered after the row is deleted or inserted.
- When there are blank rows in the table, use COUNTA to count non-blank cells, and the serial numbers can skip the blank rows to maintain continuity.
- For the filtered table, use the SUBTOTAL function to count only the visible rows. After filtering, the serial numbers are still arranged continuously from 1.
When many people create Excel tables, they manually enter 1, 2, and 3 in the serial number column, or enter the first two numbers and then pull down and copy. This is no problem when the content of the table remains unchanged, but as long as a row is deleted or inserted, the serial number will be broken or even repeated. Manually modifying each line is too time-consuming and easy to miss.
This article is for anyone who needs to regularly maintain lists, checklists, expense reports, or catalogs. After reading, you will know how to use a function to generate a serial number that will automatically update, and you will be able to choose the appropriate writing method based on whether the table has blank rows and whether it is filtered. The formulas in this article take common Excel operations as an example. The header is in row 1, the data starts from row 2, the serial number is placed in column A, and the text content is placed in column B.
How to generate a serial number so that it can be continued after deleting a row? One sentence conclusion
Use a function to generate serial numbers, and the serial numbers will be automatically rearranged after deleting a row; enter numbers directly, and a broken number will be left after deleting a row. For most tables, the ROW function is sufficient. When there are blank rows or you want to filter, change to COUNTA or SUBTOTAL.
| your situation | Recommended practices | reason |
|---|---|---|
| The table is continuous with no blank rows and no filtering. | Enter =ROW()-1 in A2 to fill down | The sequence number is determined by the position of the row, and is automatically rearranged after row deletion and insertion. |
| There are blank lines in the middle. Blank lines do not need to be numbered. | Enter =IF(B2="","",COUNTA($B$2:B2)) in A2 | Only count non-empty content, leave blank lines blank, and skip empty lines with serial numbers. |
| Filter frequently, hoping that the sequence number will still start from 1 after filtering | Enter =IF(B2="","",SUBTOTAL(103,$B$2:B2)) in A2 | Only visible rows are counted, and the sequence numbers of the filtered results are consecutive. |
| The table will continue to append new rows | First convert the data area into a table, and then fill in the serial number formula | Formulas are usually automatically extended when adding new rows, so there is no need to copy them row by row. |
Before you start: Confirm these points first
Before you start, check the structure of the table clearly. Choosing the wrong formula will cause the serial number to look correct but actually not.
- Which row does the data start from. The minus 1 in ROW()-1 is because the data starts from row 2 and the header occupies row 1. If the header occupies two lines, it must be changed to minus 2, otherwise the beginning sequence number will start from 2 or 3.
- There are no blank lines. When there are blank lines in the middle, ROW() will also number the blank lines, such as 1, 2, 4, and 5. If you need to skip empty lines, use COUNTA.
- Whether it will be filtered. Ordinary ROW() and COUNTA are still numbered according to the original position when filtering, and discontinuous serial numbers will appear after filtering. For tables that are frequently filtered, SUBTOTAL should be used.
- Whether formulas can be placed in the serial number column. If someone else wants to paste the serial number into a numerical value, or export it to another system, the formula may not work. You can copy the serial number and selectively paste it as a numerical value before submitting, but it will not be updated automatically afterwards.
- Whether to keep the manual serial number. If the serial number has special meaning, such as contract number and invoice number, it is not suitable to use line number to generate it. Such numbers should be recorded separately and not tied to the row position.
Steps to use ROW function to generate consecutive serial numbers
This method is suitable for most tables without blank rows, and the operation only requires a few steps.
- Open the table and enter the formula =ROW()-1 in the cell (A2) below the column A header. This formula returns the current row number minus 1, so A2 displays 1.
- Press Enter to confirm, A2 displays 1.
- Select A2, drag the fill handle in the lower right corner of the cell down to the last row, or double-click the fill handle to let it fill automatically.
- Check whether column A is arranged continuously starting from 1, especially the last row.
- Test the row deletion effect: delete a row in the middle, observe whether the serial number below automatically moves forward, and confirm that there is no broken number.
- Test the row insertion effect: insert a row in the middle, leave the new row blank, and observe whether the serial numbers are consecutive again. After inserting a row, column A of the new row needs to copy the formula from the previous row.
- After the test is completed, undo the row deletion or row insertion operations just now and confirm that the table is restored to its original state.
If there is a blank row in the middle of the table, change the formula in A2 to =IF(B2="","",COUNTA($B$2:B2)), and then fill it down. In this way, the serial number corresponding to the empty row is left blank, and the other rows are numbered sequentially.
If you filter often, change the formula to =IF(B2="","",SUBTOTAL(103,$B$2:B2)). Function code 103 of SUBTOTAL means counting non-empty cells and ignoring hidden rows, so the visible rows after filtering will be renumbered as 1, 2, and 3.
Check each item after completion
After filling in the formula, don't just look at the top rows of the form. Check each item in the table below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Starting sequence number | Does A2 display 1 | If 0 or other numbers are displayed, check that the subtraction after ROW () is consistent with the number of header rows |
| Continuity | Check from top to bottom to see if the serial number does not have a jump or repetition. | When there is a break, check whether the corresponding cell is a manually entered value, and change it back to the formula. |
| Deletion test | Whether the sequence number below is automatically moved forward after deleting a line | If you don't move forward, it means that the cell is not a formula. Just repopulate it. |
| Blank line processing | Whether the sequence number corresponding to the blank line is blank | If the blank position is incorrect, check whether the column judged by IF belongs to the body. |
| Screening results | After filtering, you can see whether the rows are continuous starting from 1. | In case of discontinuity, SUBTOTAL (103, …) is used for confirmation. Not ROW or COUNTA. |
| New data | Add a new line at the end, whether the serial number appears automatically | When it does not appear, manually copy the formula from the previous line to the new line |
# Core comparison
| Method | After deleting a row | Suitable for the scene |
|---|---|---|
| Enter numbers directly or copy numeric values | Leave a dash, which needs to be modified manually | Only for tables that are generated at one time and no longer modified |
| =ROW()-1 | Automatic rearrangement | Continuous tables, no blank lines, no filtering |
| =IF(B2="","",COUNTA($B$2:B2)) | Automatically reschedule and skip blank lines | There are blank lines in the middle, and blank lines do not need to be numbered. |
| =IF(B2="","",SUBTOTAL(103,$B$2:B2)) | Automatic rescheduling, continuous after screening | A table that often filters or hides rows |
# which method is suitable for you?
If your table is just a normal list with no blank lines and no filtering, use = ROW ()-1. The formula is simple and not error-prone.
If you are doing reimbursement details or purchase lists, there are often grouped blank lines in the middle, it is recommended to use the COUNTA version, blank lines will not be numbered.
If you often filter to find data, and you want to print or send the filter results to others, the SUBTOTAL version is more appropriate, which allows the sequence number after each filter to start with 1 in succession.
If the table needs to be continuously appended, first convert the data area into a table, and then write the serial number formula, and the new row-time formula will be extended, saving the trouble of copying line by line.
# frequently asked questions (FAQ)
1. What if the sequence number after the drop-down fill is all the same number?
It is usually the wrong cell format or filling method. Check that the formula for A2 is correct, and then drag with the fill handle instead of copying and pasting the formula into an area. If it remains the same, verify that the line number reference in the formula is not locked as an absolute reference.
2. The serial number formula returns 0, why?
ROW () returns the line number of the current line. When the header is on line 1, the ROW () of A2 is 2, minus 1 to get 1. If the result of the formula is 0, the subtraction is 1 more than the actual line number, or the formula is written on the row in the header.
3. Will the sequence number generated by ROW be messy after sorting?
After sorting, the sequence number is recalculated according to the new position, and the result is still 1, 2, 3 in succession, but the original sequence number changes with the position of the row. If the serial number needs to be fixed to follow a record, it cannot be generated with a line number, and another fixed number should be recorded.
4. How to put the serial number in the PDF form opened by Excel?
If the table is from PDF, you can first export the page to Word, Excel, or PowerPoint format in the PDF editor on the desktop client, and then add the serial number in Excel as described above. Please check the table row and column for completeness page by page after export, as the layout of the original file may affect the export result.
5. Why is it broken in the middle of the serial number when there is a blank line?
The normal ROW function numbers blank lines the same way, so a number is left in the blank line, and the sequence number after it looks like a jump sign. Instead of using the COUNTA formula, you can only count non-empty cells, blank lines are not numbered, and the sequence number can be kept continuous.
# Summary
If the Excel serial number is still correct after deletion, insertion and filtering, the key is to use a formula instead of manual input. The ROW function is used when the table is continuous, COUNTA is used when there are blank rows, and SUBTOTAL is often used when filtering, each of which is actually tested after deletion and filtering.
