Function Problems

How to Identify Hidden Rows in Excel Without Unhiding Them

Kushani Nimanthika Oct 14, 2026 872 views

Question details

The user needs a method to extract and list the row numbers of hidden rows within a worksheet cell without changing their hidden status.

Product: Excel
Device & OS: not provided
Scenario: Auditing or analyzing a worksheet where knowing exactly which data rows are hidden is necessary, but the current layout and visibility settings must be preserved.
Observed behavior: The goal is to dynamically generate a comma-separated list of hidden row numbers using specialized formulas or VBA macros directly in the worksheet.

Before you start

Ensure you are using a newer version of Excel that supports Dynamic Array formulas (Microsoft 365 or Excel 2021) for the formula method, and verify that macros are enabled if you choose the VBA solution.

Solution 1Recommended

Use a Dynamic Array Formula (For Populated Ranges)

Extract a list of hidden row numbers using a combination of FILTER, BYROW, and SUBTOTAL functions.

This formula method requires every cell in your inspected range to contain data (text or numbers) because it relies on the SUBTOTAL function to check for visible, non-empty cells.

Select the target cell

Click on an empty cell in your worksheet where you want the list of hidden row numbers to be displayed.

Input the array formula

Type the following dynamic array formula into the formula bar: =ARRAYTOTEXT(FILTER(ROW(A2:A15),BYROW(A2:A15,LAMBDA(rng,SUBTOTAL(103,rng)))=0))

Adjust the target range

Change the range references 'A2:A15' in the formula to match the actual column range you want to inspect, then press Enter.

Use a Dynamic Array Formula (For Populated Ranges)

Empty Cells Limitation: If the hidden rows contain empty cells in the specified column, this formula may return inaccurate results since SUBTOTAL(103) counts non-empty cells.

Solution 2

Create a Custom VBA Function (For Flexible Inspection)

Use a VBA macro to create a custom function that directly checks the EntireRow.Hidden property, bypassing formula limitations.

WPS Office Feature

Manage Hidden Data Efficiently with WPS Spreadsheet

WPS Spreadsheet provides powerful support for advanced array formulas and built-in VBA/Macro capabilities, allowing you to easily identify hidden rows without altering your data layout.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the hidden rows.
2. Select a target cell: Click on an empty cell where you want to output the hidden row data.
3. Enter your formula: Type your preferred array formula or use the Developer tab to access the Macro editor for custom scripts.
4. Execute and view: Press Enter to evaluate the formula and instantly view the extracted hidden row numbers.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xlsm)Built-in advanced formula processing for complex arraysFree, lightweight design with a familiar user interface

Frequently Asked Questions

Why doesn't the formula detect empty hidden rows?

The dynamic array formula uses the SUBTOTAL function with the 103 argument, which acts like COUNTA (counting non-empty cells). If a hidden cell is empty, the function evaluates it as 0 regardless of its visibility, leading to false results.

Do I need to recalculate the worksheet when I hide a new row?

Yes. Hiding or unhiding a row does not trigger an automatic recalculation event in Excel. You will need to press F9 to force the custom VBA function to update its results.

Can I format the output to show consecutive ranges like '4-6' instead of '4,5,6'?

Yes, but this requires significantly more complex VBA logic to loop through the array, identify sequential numbers, and group them with hyphens. The standard function provided above only generates a basic comma-separated list.

Does the VBA function work across different worksheets?

Yes. By defining the 'SheetName' argument in the custom VBA function (e.g., =HiddenRows("Sheet2", 500)), you can inspect the hidden rows of any specific worksheet within the same workbook.

Excel & Data Problems