Excel Pivot Table Chart - How NOT to plot any Top 5 items with a zero value OR only plot the first 5 regardless of value

I have created a Pivot Table in Excel and used the Value Filters > Top 10 filter to only show the Top 5 entries based on a particular value.

I have created a Pivot Table Chart based on the above pivot table.

The pivot table and chart work fine so long as the top 5 values are above zero. That is, the pivot table only displays 5 rows of data and the chart only displays 5 bars. See examples below:

However, when multiple items have the same value and make it into the top 5, then ALL of those items are listed. For example, let's say the first value is 12, the second value is 6, the third value is 2 and all other values are the same, then I get a chart similar to this:

Now, by a happy coincidence in my case, the items with the same value always happen to be zero. So I assumed I could set the pivot table/chart to not display items of a zero value. However, nothing I have tried works.

So far I've tried:

• different settings for the "For empty cells show" in pivot table options
• setting a value filter on the value column to only display values above zero
• gone into Excel's Advanced options to ensure that the "Show a zero in cells that have zero value" is not selected

I assume part of the problem is that the values are based on a "Sum" calculation and not a direct cell entry.

Obviously, my approach with using the zero values to fix my issue is only workable because of the happy coincidence that exists with my data, but ideally what I want is to chart the Top 5 items and when there's more than 5 items because they share a value, to only chart the first 5 and forget the rest.

Any suggestions would be most welcome.

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.