How to set the Excel serial number Automatic Update feature

Is there a way to automatically update the serial number if no action is required after the row is deleted or inserted? More than this, this site small series of methods provided today, but also to achieve the hidden line after the serial number can also be automatically updated. Hey!! This effect can let you completely satisfied!

Effect Demo

Original table

Insert line number, serial number is still continuous

After the row is deleted, the serial number is still continuous

After filtering, the serial number is still continuous

See here, I guess you can't help but ask the way.

Operation Steps:

1, Insert auxiliary column, enter formula = 1. Adding this column is convenient for using a formula to count the number of rows in an ordinal column.

2, in the Ordinal column input formula

=subtotal (2,B$1:B2)

Formula Description:

The SUBTOTAL function is the only statistic function in Excel that can ignore hidden rows

2 indicates the number of numbers in column B

B$1:B2 is the use of $ to set an area that stretches continuously

3, Insert tab-table. The purpose of this step is to have the formula automatically copied to a blank line after the row is inserted.

At this point, the function of a powerful serial number automatic Update function settings, after you can wayward delete rows, insert rows, filter rows, and do not worry about serial number.