PlayInfoBox

Home /Utilities /How to Number Filtered Rows Sequentially in Excel (SUBTOTAL)

How to Number Filtered Rows Sequentially in Excel (SUBTOTAL)

Tap a step to see how it works.

  1. The problem with regular numbering

    Even after filtering hides some rows, numbers created by dragging the fill handle still count the hidden rows, so the visible sequence looks broken.

  2. Enter a SUBTOTAL formula

    Type something like =SUBTOTAL(103,$B$2:B2), and it counts only the rows currently visible on screen.

  3. Fill down

    Drag the fill handle on the cell with the formula down to copy it to the remaining rows.

  4. Apply a filter to check the result

    Now, filtering to hide some rows still renumbers the visible rows in order β€” 1, 2, 3, and so on.

  5. Why this helps

    It's useful whenever you want a printed or shared list to always keep a clean, uninterrupted sequence, regardless of which rows a filter is currently hiding.

Keeping numbers clean even after filtering

Applying a filter to hide some rows makes ordinary sequential numbers look sparse and out of order. Using the SUBTOTAL function keeps the numbering based only on the rows currently visible, so it always counts cleanly from 1.

Pair it with your auto-filter workflow

This technique works especially well alongside a standard auto-filter setup, since you can leave the numbering formula in place permanently and it will keep adjusting correctly no matter how the filter criteria change.

Frequently Asked Questions

What does the number 103 in SUBTOTAL actually mean?

103 is a function code that means 'calculate COUNTA while ignoring hidden rows' β€” it excludes both rows hidden by a filter and rows hidden manually.

Does the numbering stay correct if I sort the data?

Yes. After sorting, the formula recalculates and renumbers rows according to whatever order is currently visible on screen, so the sequence is always accurate.