Number Visible Rows in Excel After Filtering

Quick answer: use SUBTOTAL with COUNTA

To number visible rows in Excel, put this formula in the first data row of a helper column, then fill it down:

=SUBTOTAL(103,$C$5:C5)

Here, column C contains a task name on every record. When you filter the list, SUBTOTAL ignores rows the filter removes and counts the nonblank task names that are still visible. The first visible task is 1, the second is 2, and so on. The number changes with the current view, so keep the Task ID as the permanent identifier.

What the workbook shows

The sample list is a small finance team’s task register. It has 20 records, from REQ-101 to REQ-120, with a task name, owner, status, priority and due date. The visible number sits in column A; the ID in column B stays fixed even when you filter or sort. With Status set to Open, 10 tasks remain and the formula numbers them 1 through 10.

Excel’s SUBTOTAL function supports 11 operations. Function number 3 runs COUNTA, which counts nonempty cells. Function number 103 runs the same count while also excluding manually hidden rows. Both 3 and 103 ignore rows excluded by an AutoFilter. See the Microsoft SUBTOTAL reference for the full function-number table.

Step 1: Set up a clean list

Put one task on each row. Keep the column headings in row 4 and the records directly below them, as in the sample workbook. The example uses A4:G24:

1. Enter Visible #, Task ID, Task, Owner, Status, Priority and Due date in A4:G4.
2. Enter one complete record per row in rows 5 through 24. Do not leave the Task cell in column C blank.
3. Select A4:G24 and turn on filters with Ctrl+Shift+L on Windows. On a Mac, use the Data > Filter command. The filter shortcut guide covers turning AutoFilter on and off.

Step 2: Add a helper column for the visible number

Use column A for the temporary sequence and leave column B for your real Task ID. This distinction matters: a filtered list can show REQ-101, REQ-103 and REQ-105 as visible rows 1, 2 and 3. If you used the row number as an ID, those records would appear to change identity every time someone filtered the list.

1. In A4, type Visible #.
2. Keep the permanent identifier in B, such as REQ-101.
3. Freeze the header if helpful: click A5, then choose View > Freeze Panes > Freeze Panes.

Step 3: Enter the SUBTOTAL formula

Click A5, the first data cell under Visible #, and enter:

=SUBTOTAL(103,$C$5:C5)

There are three important pieces:

In the unfiltered workbook, A5 returns 1. The dollar signs lock only the starting cell, while the last reference remains relative. In A6 the formula becomes =SUBTOTAL(103,$C$5:C6), so Excel counts from the first task through the current row.

Step 4: Fill the formula down the list

Copy A5 to A24. You can drag the fill handle down, double-click it when the adjacent Task column is filled, or select A5:A24 and press Ctrl+D. On a Mac, use Command+D.

For a list that grows, convert the range to an Excel Table before entering the formula. Press Ctrl+T, confirm that the table has headers, and enter the formula in the first cell of the Visible # column. Excel fills the calculated column as records are added. For ordinary ranges, extend both the filter range and the formula when you add rows.

Step 5: Filter the list and watch the numbers reset

Open the filter arrow in E4 (Status), clear Select All, select Open, and confirm. Ten rows remain. The visible numbers in column A now run from 1 to 10, even though their original worksheet rows are not consecutive.

Try another filter, such as Priority = High. The sequence recalculates for the visible records. Clear the filter to restore the complete 1-to-20 list. If your filter controls are missing, use the ribbon’s Data > Filter command or toggle AutoFilter with the shortcut above.

When you need to copy only the filtered results to a new sheet, Excel has a separate Visible cells only command. The numbering formula does not hide or delete records; it only recalculates the helper values.

Step 6: Decide whether manually hidden rows count

Use 103 when a row hidden with Home > Format > Hide & Unhide > Hide Rows should disappear from the numbering too. If you want manually hidden rows to remain in the count while filtered-out records are still ignored, use 3 instead:

=SUBTOTAL(3,$C$5:C5)

The sample workbook uses 103. Its what-if check hides worksheet row 5; the next visible row’s formula returns 1. With 3, that manually hidden task would still count. Both codes always ignore rows excluded by an AutoFilter.

Common problems and quick fixes

If you work with long worksheets, the Ctrl+Arrow navigation guide helps you jump to the edge of a contiguous data block. For a dynamic sorted output, see how to sort data with SORTBY.

Frequently asked questions

Final checklist

• Each task occupies one row and the chosen count column is populated.
• The helper formula starts at the first record and locks the range start.
• Function number 103 is used when manual hidden rows must be excluded.
• The sequence changes when a filter is applied and restores to 1 through 20 when cleared.
• A separate permanent ID identifies each task.