In Excel, when you use the filter function, the original line number may be hidden, resulting in a discontinuous appearance of the sequence number. In order to generate new sequential numbers after filtering, you can use some functions of Excel. Here's a common method:
Insert Help Column:
Insert a new column next to your data, which will be used to place the new ordinal number.
Use the formula to generate the ordinal number:
Enter the following formula in the first cell of the help column (let's say A2:
subtotal(3, $a$2:a2)
Here, the first argument of the subtotal function, 3, represents counta, which is used to count the number of non-empty cells. $a$2:a2 is the corresponding range of cells, note that a mixed reference ($ symbol) is used here to fix the starting cell.
Drag the fill handle:
Drag the lower-right corner of the cell to all the cells you want to fill with the ordinal number.
Apply Filter:
If you haven't applied filtering yet, you can now select the appropriate column to filter.
Check out the results:
When the filter is applied, you will notice that a continuous ordinal number is generated in the help column, even if some rows are hidden due to the filter.
Precautions. The advantage of this approach is that no matter how the data is filtered, the ordinal numbers in the help column are always sequential.
The reason to use the subtotal function instead of the row function is that subtotal can dynamically calculate the number of rows displayed when filtering.
By using this method, you can keep a continuous sequence number after filtering the data in Excel, which is very useful for managing and analyzing the filtered data.