How to automatically generate serial numbers in Excel? Summary of popular tips across the internet
In daily office work, the serial number generation function of Excel is one of the high-frequency needs. Whether you are making tables, statistics, or organizing lists, automatically generating serial numbers can greatly improve efficiency. This article will combine the popular discussions on the Internet in the past 10 days to summarize5 practical methods, and comes with structured data comparison to help you quickly master the skills!
Directory
1. Drag and fill method (basic operation)
2. ROW function method (dynamic serial number)
3. SUBTOTAL function method (continuous serial numbers after filtering)
4. Customized formula method (complex scenarios)
5. Table tool method (structured citation)
1. Drag and drop filling method
This is the most basic way to generate serial numbers, suitable for simple lists:
| steps | Operating Instructions |
|---|---|
| 1 | Enter the initial sequence number (such as 1) in the starting cell |
| 2 | Select the cell, drag the fill handle in the lower right corner and drag it downwards |
| 3 | After releasing the mouse, click "Autofill Options" and select "Fill Sequence" |
Disadvantages:The serial number will not be updated automatically when a row is deleted and needs to be adjusted manually.
2. ROW function method
Dynamic serial numbers are implemented through functions, and rows are automatically rearranged after deletion:
| formula | Description | Example |
|---|---|---|
| =ROW()-1 | Generate serial numbers starting from line 2 (the first line is the title) | If the formula is in cell A2, 1 will be displayed |
| =ROW(A1) | Reference cell row number | Automatically increment when filling down |
3. SUBTOTAL function method
Keep serial numbers continuous when filtering data, suitable for dynamic reports:
| formula | Parameter description |
|---|---|
| =SUBTOTAL(3,$B$2:B2) | 3 represents the COUNTA function, counting non-empty cells |
Effect:After filtering, the serial numbers of hidden rows will be automatically skipped, and the visible rows will remain continuous.
4. Custom formula method
Complex scenarios (such as merging cells) can be combined with the IF function:
| scene | Formula example |
|---|---|
| Skip blank lines | =IF(B2="","",MAX($A$1:A1)+1) |
| Group number | =IF(B2<>B1,1,A1+1) |
5. Form tool method
Convert range to table (Ctrl+T), automatically enabling structured references:
| Advantages | Description |
|---|---|
| auto-expansion | The serial number is automatically extended when new data is added. |
| Formula simplification | Use column names instead of cell references (such as [serial number]) |
Top 5 most popular questions on the Internet (last 10 days)
| Ranking | question | solution |
|---|---|---|
| 1 | Serial numbers are not consecutive after deleting rows | ROW function or table tool |
| 2 | Serial numbers are confused after filtering | SUBTOTAL function |
| 3 | Merge cell numbers | Custom IF formula |
| 4 | Classification grouping number | =IF(condition,1,previous cell+1) |
| 5 | Reference serial numbers across worksheets | INDIRECT+ROW combination |
Summary
Choose the appropriate method according to actual needs:
•simple list:Drag fill or ROW function
•dynamic report:SUBTOTAL function
•complex structure: Custom formula + conditional judgment
•long term maintenance: Table Tool Structured Reference
After mastering these skills, generating Excel serial numbers will no longer be a problem! If you need further optimization, you can pay attention to advanced gameplay such as conditional formatting and VBA macros.
check the details
check the details
en
