SOLVED

Can you ask a filter to no show all results if less than X amount of rows?

Occasional Contributor

Hi, I'm working on a culture survey analysis for a client and I'm facing two things to solve. The first one is with a filtered table, for confidentiality I would like the results to be filtered if they'll be showing less than 10 rows/results. The second thing is similar (at least sounds that way) I have a dashboard with pivot charts and would like to limit the slicers results too (don't show if information is less than 10 rows/results). 
Can this be done? 
need info/help ASAP

Thank you beautiful people!

 

Using Mac BigSur 11.2.3 (20D91) and Excel 16.55

6 Replies

Hi @NorbertoG 

With a helper column like Display below you can mark those not to be disclosed;

=IF(COUNTIFS([Country],[@Country])<10,"********",[@Country])

 

bosinander_0-1638643191366.png

Use Power Query (Data: Get & Transform) to load that data table into a new table and exclude the records not to be disclosed.

Use the new table as source for your pivot table and insert a new slicer. 

bosinander_1-1638643405305.png

 

@bosinander 

Afraid Power Query won't work on Mac.

best response confirmed by NorbertoG (Occasional Contributor)
Solution

Ah, thank you @Sergei Baklan

Then the data source may be dynamic, avoiding 'blanks' in the pivot table, by using a named range that adjusts to the filtered data by referring to

 

=OFFSET($P$5,0,0,ROWS($P$5#),COLUMNS($P$5#))

 

 where P5

 

=FILTER(Table1[[#All];[Date]:[Amount]];Table1[[#All];[Display]]<>"********")

 

thank you! will try it out as I'm moving forward offering a dashboard as a product with Culture Surveys now, keeping confidentiality is important here.
Sure is. You're welcome, and good luck
@bosinander I was able to put something together, now focusing on confidentiality. I opened a new post, would love to know your thoughts?
https://gorovian.000webhostapp.com/?exam=t5/excel/how-to-protect-respondents-confidentiality/m-p/3563094/...