Subtotal Formula for Serial Number?

Formulas & functions · Updated November 22, 2026

The direct answer: there is no single built-in Excel formula that automatically generates serial numbers for subtotals. Instead, you use a combination of the SUBTOTAL function (for visible rows only) and a helper column with a formula like =IF(SUBTOTAL(103,$B$2:$B2)=1,MAX($D$1:$D1)+1,"") to assign sequential numbers only to visible rows after filtering. This article explains the exact setup, alternatives, and common pitfalls.

Why a Simple Serial Number Formula Fails

If you just type 1 in A2 and =A2+1 in A3, then drag down, you get serial numbers—but they break when you filter or hide rows. The numbers stay static and don’t renumber to show only visible rows. The SUBTOTAL function is designed to ignore hidden rows, so it becomes the foundation for a dynamic serial number system. However, SUBTOTAL alone doesn’t count cumulative rows; you need to pair it with a running total logic.

How the SUBTOTAL-Based Formula Works

Assume your data starts in row 2, with column A for serial numbers, column B for a value that always has text (like a name), and you want column D to hold the serial numbers. In D2, enter:

=IF(SUBTOTAL(103,$B$2:$B2)=1,MAX($D$1:$D1)+1,"")

Drag this down for all rows. Here’s the breakdown:

  • SUBTOTAL(103, range) counts non-empty cells in the range, ignoring hidden rows. The 103 is the COUNTA function code for hidden rows.
  • The range $B$2:$B2 expands as you drag down—it’s a running range from the first data row to the current row.
  • If that count equals 1, it means the current row is the first visible row in the running range, so it assigns the next serial number.
  • MAX($D$1:$D1)+1 finds the highest previous serial number (starting from D1, which should be blank or 0) and adds 1.
  • If the count is not 1 (i.e., the row is hidden or there are earlier visible rows), it returns an empty string.

Example: If rows 3 and 4 are hidden, then for row 5, SUBTOTAL(103,$B$2:$B5) counts only visible cells in B2:B5 (assuming B2, B3, B4, B5—but B3 and B4 are hidden, so count = 2, not 1). So row 5 gets an empty string. Row 2 gets 1, row 3 (if visible) gets 2, etc. When you filter, the numbers renumber automatically.

Setting Up the Helper Column Correctly

You need a helper column (column B in the example) that always contains text or numbers, never blank, for every data row. This is because SUBTOTAL(103) counts non-empty cells. If a row has a blank in column B, it won’t be counted, breaking the logic. Common practice: put a unique identifier like an ID or a name in column B. If you don’t have one, you can use a formula in a helper column to always return a value, like =ROW().

Also, the formula references $D$1—make sure D1 is empty or contains 0. If D1 has text, MAX will ignore it, but it’s safer to leave it blank.

Alternative: Using AGGREGATE for More Control

If you need to ignore error values or use different aggregation, AGGREGATE can replace SUBTOTAL. The equivalent formula is:

=IF(AGGREGATE(3,5,$B$2:$B2)=1,MAX($D$1:$D1)+1,"")

The 3 is COUNTA, and 5 means ignore hidden rows. This works similarly but also allows ignoring errors if you change the option number. However, SUBTOTAL is simpler and more widely known, so stick with it unless you have a specific need.

Common Problems and Fixes

  • Serial numbers don’t renumber after filtering: Make sure you’ve entered the formula as an array? No, it’s a normal formula. The issue is usually that you didn’t drag the formula down far enough, or you used absolute references incorrectly. Check that the range $B$2:$B2 has the first row locked with $ and the second row relative.
  • Numbers appear on hidden rows: If you hide rows manually (right-click > Hide), SUBTOTAL(103) ignores them, so they won’t get numbers. But if you use AutoFilter, hidden rows are also ignored. If you see numbers on hidden rows, you might have used COUNTA instead of SUBTOTAL.
  • Formula returns 0 instead of blank: Change the final argument from "" to 0 if you prefer zeros, but blank is better for aesthetics.
  • MAX function picks up text: If column D has text headers like “Serial”, MAX ignores text, so it’s fine, but ensure no stray numbers in D1.

When to Use a Simple Drag Formula Instead

If you never filter or hide rows, the simplest serial number is just =ROW()-1 in A2 (assuming row 1 is headers). That gives 1,2,3… down. This is faster and doesn’t require a helper column. But the moment you filter, those numbers won’t renumber. So choose based on your workflow: static numbers for printing or dynamic numbers for on-screen filtering.

Frequently Asked Questions

Can I use SUBTOTAL to number only visible rows without a helper column? No, because SUBTOTAL needs a range to count, and if that range includes blank cells, it won’t work reliably. You need a helper column with non-blank values, or you can use a more complex formula with SUMPRODUCT and OFFSET, but that’s overkill.

Does this formula work in Google Sheets? Yes, SUBTOTAL exists in Google Sheets, but the function code for COUNTA is 103 as well. However, Google Sheets treats hidden rows slightly differently; it’s recommended to test. The formula syntax is identical.

What if I want to restart numbering for each group? You’d need to add a condition based on a group column. For example, if column C has group names, use =IF(SUBTOTAL(103,$B$2:$B2)=1,IF(C2<>C1,1,MAX($D$1:$D1)+1),"") but this gets complicated. Better to use a pivot table or a dynamic array formula with SCAN (in Microsoft 365) to reset per group.

Why does my formula show a #NAME? error? That means you typed the function name wrong, or you’re using an older Excel version that doesn’t support SUBTOTAL? Actually, SUBTOTAL has existed since Excel 2003, so it’s likely a typo. Check for a missing comma or parentheses.

Can I use this formula in a table (ListObject)? Yes, but you need to adjust the references. In a table, you can use structured references like [@ColumnB], but the running range is tricky. It’s easier to convert to a range or use a helper column outside the table.

Does the formula slow down on large datasets? Yes, because each cell calculates a SUBTOTAL over a growing range. For 10,000 rows, it may be sluggish. Consider using a dynamic array formula like =IF(SUBTOTAL(103,B2:INDEX(B:B,ROW()))=1,MAX(D1:D1)+1,"") but that’s still heavy. For large data, use a pivot table or Power Query to generate serial numbers.

Final Tips for Accurate Serial Numbers

Always test with a small dataset first. After applying the formula, select all data, then use Data > Filter to verify that the serial numbers renumber correctly. Also, remember that SUBTOTAL ignores rows hidden by filtering, but not rows hidden by grouping (outline) unless you use the option 103? Actually, 103 ignores all hidden rows, including grouped ones. If you want to ignore only filtered rows but not manually hidden ones, use 3 instead of 103. The difference: 3 ignores only filtered-out rows, while 103 ignores all hidden rows. Choose based on your need.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.