In Google Sheets, we use Data > Create a Filter or manually hide rows to focus on specific parts of a table or to print only relevant sections. When you have a serial number column, hidden rows can cause the numbering to lose continuity. So, how do you resolve this? Specifically, how do you skip hidden rows in sequential numbering to get the correct numbering in the visible rows? You can use the following formula for this:
=SUBTOTAL(103, $A$1:A1)Sequential Numbering in Visible Rows – Example
Assume you have text in column C in the range C2:C. You can use the following formula in cell B2 and drag it down to get sequential numbers in visible rows:
=SUBTOTAL(103, $C$2:C2)
The SUBTOTAL function with the number 103 represents the COUNTA function. It returns the count of values in visible rows up to the current row. When you drag the formula down, it will assign sequential numbers in visible rows.
The numbers in hidden rows will be the same as the number of the visible row above them. Since the hidden rows are not visible, this won’t cause any issues.
If all the rows are visible, you will get regular serial numbers. This ensures that you always have continuous numbering, regardless of whether rows are hidden or not.
Fill Sequential Numbers While Skipping Hidden Rows: Key Considerations
Important:
When using this formula, make sure that the column for which you are numbering the rows is not empty. For instance, in the above example, the formula expects values in all cells in the range C2:C13. If you expect empty cells, you should consider using a helper column.
Addressing Empty Rows Using a Helper Column
For example, you can enter the following formula in cell D2:
=ArrayFormula(SEQUENCE(XMATCH(TRUE, C2:C<>"", 0, -1)))Then, hide column D. Afterward, use the following formula in cell B2 and drag it down:
=SUBTOTAL(103, $D$2:D2)This will allow you to skip hidden rows and include empty rows while maintaining sequential numbering.
Skip Hidden Rows Dynamically in Sequential Numbering in Google Sheets
In our example, we used a drag-down formula. However, it may break if you insert rows between existing data. The best way to handle numbering rows while skipping hidden rows is to use an ArrayFormula.
The SUBTOTAL function won’t expand when used with ARRAYFORMULA, so we can use the MAP lambda helper function to achieve this:
=ARRAYFORMULA(IF(C2:C="",,MAP(C2:C, LAMBDA(Σ, SUBTOTAL(103, C2:Σ)))))Insert this formula in cell B2 after clearing the range B2:B to allow it to expand.
We transformed =SUBTOTAL(103, $C$2:C2) into an unnamed custom lambda function as follows:
LAMBDA(Σ, SUBTOTAL(103, C2:Σ))Then, we applied this to each element in the array C2:C using the MAP lambda helper function.
In the above formula, Σ represents the current element in the array.
Resources
- Auto Number Rows While Skipping Blanks in Google Sheets
- Adding N Blank Rows to SEQUENCE Results in Google Sheets
- Date Sequence Every Nth Row in Excel (Dynamic Array)
- Auto Serial Numbering in Google Sheets with Row Function
- Group-Wise Serial Numbering in Google Sheets
- Group-Wise Dependent Serial Numbering in Google Sheets
