Number Rows in Excel When Filtered

How-to basics · Updated November 20, 2026

To number only the rows currently visible after filtering, put a SUBTOTAL formula in your numbering column and fill it down. If your records are in B2:B100 and the numbers will go in column A, enter =SUBTOTAL(3,$B$2:B2) in A2, then copy it down. The sequence updates when you change the filter. Use a source column that has a value in every record row.

What You’ll Need

  • A list with a header row and data underneath it. In the example below, the data starts in row 2.
  • A column that is populated for every record, such as an ID, name, or date. The formula counts visible nonblank cells in this column.
  • An empty column for the visible-row numbers. Add a header such as Visible # so it is clear what the numbers mean.

The steps use Microsoft Excel for Microsoft 365 on Windows. The same formula and basic Data tab commands are available in Excel for Mac and Excel for the web, though ribbon layout and keyboard shortcuts can differ slightly.

Step-by-Step

  1. Check the data before adding numbers. Make sure each record occupies one row and that the column you plan to count is filled in on every row. For example, if column B contains an ID for each record, it is a good counting column. Do not use a column with occasional blanks: the formula counts nonblank cells, not rows themselves. Keep the header in row 1 and the first record in row 2 for the example formula.
  2. Make room for the numbering column. Insert or choose an empty column beside the records, and add a heading such as Visible # in its header cell. Putting the numbers outside the original data is especially helpful if you later sort or convert the range to a table. Avoid placing the formula in the same column it counts, because the formula would then be counting its own output rather than a reliable source field.
  3. Enter the formula in the first data row. With IDs in B2:B100 and numbering in A2:A100, select A2 and enter =SUBTOTAL(3,$B$2:B2). Press Enter. The first visible record should display 1. The dollar signs keep the starting cell fixed; the final B2 reference is relative, so it expands as the formula is filled down. In a row where the source cell is blank, this count will not advance.
  4. Fill the formula through the full list. Select A2 and use the fill handle, the small square at the lower-right corner of the selected cell, to drag the formula down through the last data row. You can also copy A2, select the remaining cells in the numbering column, and paste. If the list is an Excel Table, entering the formula in the first data cell of that table column will usually fill it down as a calculated column. Check the last few rows to confirm the formula reached the end of the records.
  5. Turn on filtering if it is not already on. Click a cell in the data range, then choose Data > Filter. Excel adds filter arrows to the header row. If the range includes a blank row or a separate block of unrelated data, select just the intended list before enabling the filter so Excel does not include the wrong cells. In Excel for Mac, use the Data tab and its Filter command as well.
  6. Apply a filter and inspect the sequence. Open a header’s filter arrow and select a value or condition that leaves some records hidden. The visible records should now show consecutive numbers starting at 1. For example, if the filter leaves records on worksheet rows 3, 7, and 9 visible, their numbering cells should show 1, 2, and 3, provided the source cells in column B are all populated.
  7. Change or clear filters to confirm the result. Apply a different filter, or use Data > Clear to remove the filters. The formula recalculates for the visible rows: the remaining records renumber, and clearing the filter restores numbering across the full list. If the sequence does not change, check that the formula was filled down, that the filter is applied to the same rows, and that Excel calculation is not set to Manual.

The first argument, 3, tells SUBTOTAL to perform a COUNTA-style count. SUBTOTAL ignores rows removed by a filter, and the expanding range counts the visible nonblank source cells from the first data row through the current row. This is why it produces a running sequence rather than the original worksheet row numbers.

Common Mistakes

  • Counting a column with blanks: SUBTOTAL counts nonempty cells. If a visible record has no value in the chosen source column, that row may repeat the previous number. Pick a dependable ID or another field that is filled in for every record.
  • Using a regular row-number formula: A formula based on the worksheet row position gives each record a fixed position, so filtered results can show gaps. Use SUBTOTAL for a sequence that responds to filters.
  • Putting the formula in the source column: If the formula counts the same cells where its results appear, the range no longer represents your record data. Keep the numbering and counting columns separate.
  • Filling only the currently visible cells: If a filter is already active while you fill or paste, some hidden rows may not receive the formula as intended. Clear the filter first, fill the entire numbering column, then filter the list again.
  • Including the header in the formula range: Start with the first data row, not the heading. A header included in the count can make the first result start at the wrong number.
  • Expecting manually hidden rows to behave like filtered rows: SUBTOTAL with function number 3 ignores filtered-out rows but still includes manually hidden rows. That distinction matters when a sheet uses both filtering and the Hide Row command.

Tips

  • Use a unique identifier column when possible. Names can be blank or duplicated, while IDs are usually consistent and make a dependable count source.
  • Give the number column a descriptive heading. These are visible-row sequence numbers, not permanent record IDs; they can change whenever a filter changes.
  • If you regularly add records, convert the range to a Table with Insert > Table and confirm that the table has headers. Tables make it easier to filter and typically extend formulas to new rows. Verify the formula appears in a newly added row before relying on it.
  • Keep a separate permanent ID if other sheets or colleagues need to refer to a record. The visible sequence is for reading or reviewing a filtered list; it should not replace an identifier used in lookups or reports.
  • After sorting, check that the numbering formulas still run from the first data row to the current row. The formula is designed to reflect the current visible order, while the source values remain the basis for the count.

Troubleshooting

The numbers skip or repeat. Inspect the source column on the affected rows. A blank source cell will not increase the COUNTA-style subtotal, so choose a column that is populated on every row. Also check that a copied formula has an expanding end reference: on the next row it should refer to the next source cell, not keep ending at B2.

Hidden rows still seem to count. If rows were hidden manually rather than excluded by a filter, function number 3 includes them. With IDs in B2:B100 and the corresponding visible-row numbering formula in A2, use =SUBTOTAL(103,$B$2:B2) instead if manually hidden rows should also be excluded. Function number 103 excludes manually hidden rows as well as filtered-out rows. Choose 3 when you want to ignore filters but count manually hidden records; choose 103 when both kinds of hidden rows should be omitted.

The formula displays the text instead of a number. Select the cell and check whether it is formatted as Text. Change the format to General or Number, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also confirm that the entry begins with an equals sign and that it was not prefixed with an apostrophe.

The sequence does not update after changing a filter. Confirm that the filter arrows belong to the same contiguous range as the records and that you changed a filter rather than simply hiding a row. In Excel, check Formulas > Calculation Options and choose Automatic if calculation is set to Manual. You can also press F9 to recalculate. On a Mac, keyboard shortcuts can differ; use the Formulas tab or Excel menu to check calculation settings.

The first visible number is not 1. Check whether the source cell in the first visible record is blank, whether the formula starts on the first data row, and whether the formula’s starting reference is fixed. In the example, the starting reference is $B$2. If your data starts on another row or the source field is in another column, adjust both references consistently.

Frequently Asked Questions

Will the numbers change when I select a different filter?

Yes. SUBTOTAL recalculates when rows are included or excluded by a filter. The first visible record should show 1, and the following visible records should continue the sequence, as long as the chosen source column is filled in.

Can I use this in an Excel Table?

Yes. Add a numbering column to the table and enter the formula in its first data row. Excel usually propagates it through the calculated column. The example’s ordinary cell references work for a normal range; if you edit a table or move columns, check that the formula still counts the intended nonblank field.

Does this renumber rows hidden with Hide Row?

Not with function number 3: it ignores filtered-out records but counts manually hidden rows. Use function number 103 if you also want manually hidden rows left out of the sequence.

Can I keep the numbering fixed after filtering?

Yes, but that is a different goal. Copy the numbers and use Paste Special > Values to replace the formulas with fixed values. They will no longer update when filters change, so keep a copy of the workbook or the original formulas if you may need a dynamic sequence later.

Can I use the same method in Excel for the web or Mac?

The SUBTOTAL formula and ordinary filters work in Excel for the web and Excel for Mac. The menus may be arranged differently, but look for Filter on the Data tab. Keyboard shortcuts vary by platform, so use the ribbon command if a Windows shortcut does not apply.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.