Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to filter the data inside pivot table?

Hi Community,

         I am using Pivot table chart to show the summarized data of infection data. However, there are huge number of cells, rows and columns are empty - there are no data. Is there any way I can filter out these empty rows or columns? I just want to show the rows or columns with data. One step further, is this possible to filter out the rows and colums whose maximum data in the cells is equal to a given number? For example, I only want to show the rows or columns whose maximum number (count of records) is equal to 5?



4 Replies

You can use below for first requirement

Null values.jpg

(2) For second requirement you can use input filed and set analysis by using that value in expressions.

Not applicable

Hi Haijun,

Query 1:

In Pivot table -

Layout tab - Conditional show text box

You can hide/show an object by using "Conditional" on Layout tab.

In the Conditional, use any of the expression from your table and make them non zero.

For Ex: Amount column

Sum(Amount) <> 0

or Dimension tab - select  suppress when value is null

Query 2:

To show the rows or columns whose maximum number (count of records) is equal to 5

Expression tab - expression


Not applicable

Hi Nitha, It seems your solution works, but I am not clear about what will be displayed when the number is to 5. Top 5 department whose customer numbers are over 5? If there are multiple departments, how does this work? Thanks

Not applicable

Hi Haijun,

Please can you explain with sample file,what do you expect?

when you select multiple departments in listbox,it should select top 5 customers.

for top five departments,Try to find rank of Department as well same like CustomerID