Automatic Serial Numbers in Excel That Survive Filtering, Deleting and Blanks

Automatic Serial Numbers in Excel That Survive Filtering, Deleting and Blanks featured illustration

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

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.

Advertisement
In this article
  1. Basic auto numbering
  2. Number only filled rows (skip blanks)
  3. Numbering that stays 1, 2, 3 after a filter
  4. Restart numbering for each group
  5. Codes like INV-0001
  6. Where people go wrong
  7. Practice
  8. Continue learning

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.

Advertisement
⚠️ Excel sometimes treats the last SUBTOTAL row as a total row and keeps it visible when filtering. =AGGREGATE(3,5,$B$2:B2) avoids that.

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")
💡 Formula codes change if rows move. For invoice numbers that must never change, convert them to values (Copy › Paste Values) once issued.

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.

Advertisement

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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong

Leave a Comment