Forum Discussion

Harun24HR's avatar
Harun24HR
Bronze Contributor
Oct 10, 2025
Solved

How to filter visible cells by formula after applying filter to a dataset.

I have a dataset from A1:B11 (in real case many more). I apply a filter on dataset, for example filter data for Retailer1 and Retailer3. I want on a separate sheet to filter these visible cells using...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 10, 2025

    Or with exactly same idea

    = LAMBDA(range,
    LET(
        IsVisible, LAMBDA(v, AGGREGATE(3,5,v)),
        FILTER( range, TAKE( MAP(range, IsVisible ),,1) )
) )
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 10, 2025

I would add a helper column that check if the row is visible or not and then a regular FILTER formula as demonstrated in the picture below. In your real model, move the FILTER formula away from the data table as it will otherwise be partially hidden when you apply a filter to the data table.

 

Harun24HR's avatar
Harun24HR
Bronze Contributor
Oct 10, 2025

Riny_van_Eekelen​ SUBTOTAL() is important here. After few research, I am able to make it as a generalize LAMBDA() formula. Here is lambda.

FILTERVISIBLECELLS=LAMBDA(Arr,LET(CheckVisible,SUBTOTAL(103,OFFSET(Arr,ROW(Arr)-ROW(TAKE(Arr,1,1)),0,1)),FILTER(Arr,CheckVisible)))

And use of the function.

=FILTERVISIBLECELLS(A2:B11)
Screenshot of formula

 

Resources