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:$B2expands 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)+1finds 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:$B2has 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 usedCOUNTAinstead ofSUBTOTAL. - Formula returns 0 instead of blank: Change the final argument from
""to0if you prefer zeros, but blank is better for aesthetics. - MAX function picks up text: If column D has text headers like “Serial”,
MAXignores 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
- Physics Wallah Balance Sheet: Key Financials
- Balloons with Metallic Numbers: Sizes, Filling, and Safety
- Split a String in BigQuery? SQL Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
- Concatenate First Letter and Last Name in ExcelIf first names are in A2 and last names are in B2, enter =LEFT(TRIM(A2),1)&" "&TRIM(B2) to combine the…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- Excel Named Range Not Appearing in Formula? FixesIf an Excel named range is not appearing as you type a formula, first confirm that the name…
- How to Filter by Year in Excel: A Clear, Practical GuideTo filter a date column to one calendar year in Excel, select your data, choose Data > Filter,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.