
📎 This article includes 1 downloadable practice file ↓
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.
In this article
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
| Mistake | Result |
|---|---|
| Typed numbers | Gaps after a delete, duplicates after an insert |
COUNTA(B2:B2) without $ |
The range doesn’t expand, so every row shows 1 |
| ROW() numbering under a filter | Shows 4, 9, 15; use SUBTOTAL or AGGREGATE |
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.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.