Type 1, 2, 3 and drag, then delete a row, filter or insert one, and the numbers break. A formula keeps the S.No. column right on its own.

Basic auto numbering

=ROW() - 1                   ' row 2 shows 1; survives deleting rows
=ROW() - ROW($A$1)           ' safer if rows get inserted above the table
=SEQUENCE(COUNTA(B2:B500))   ' Excel 365: one formula numbers every filled row

In a Table, use =ROW()-ROW(Table1[#Headers]) and it fills new rows automatically.

Number only filled rows (skip blanks)

=IF(B2 = "", "", COUNTA($B$2:B2))

The expanding range $B$2:B2 counts filled cells from the top down to this row.

Numbering that stays 1, 2, 3 after a filter

=SUBTOTAL(3, $B$2:B2)

SUBTOTAL function 3 (COUNTA) ignores rows hidden by a filter, so the visible rows renumber 1, 2, 3. Use 103 to also ignore rows hidden by hand.

Restart numbering for each group

Invoices with several lines each, numbered 1, 2, 3 within each invoice (invoice number in column C):

=COUNTIF($C$2:C2, C2)

A group number that goes up whenever the invoice changes (data sorted by invoice): =IF(C2=C1, A1, A1+1), with 0 in A1.

Codes like INV-0001

="INV-" & TEXT(ROW()-1, "0000")
="SO/" & TEXT(A2, "yy") & "/" & TEXT(COUNTIF($A$2:A2, ">=" & DATE(YEAR(A2),1,1)), "000")

Where people go wrong

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.