• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get rid of blank or 0 as slicer option

A

Aubade

New Member
Hi All. This is my 1st post but I'm an avid chandoo reader. So many great tips here! I hope to post more now that I've registered.


My current question has to do with slicers and pivot tables. I'm wondering why does a row for "blank" always show up in pivot tables? Before it was never a problem for me, since I wasn't using slicers I'd just uncheck the option using the filter and no one would ever see it.


But now I am trying to use a slicer for program name on a report with a bunch of charts. This way the user can just select which program they want to see, and all of the charts will automatically just show that one program. Fantastic!


But my problem is now the slicer shows an option for "blank". Of course they can just not choose that option, so it doesn't really bother the chart data, but I don't like there being an unnecessary option listed in the slicer to confuse them.


Sometimes it also shows as a 0 instead of "blank". In the current chart I'm working on it is 0, I think because I have only calculated fields in the pivot.


How can I get rid of that unnecessary blank or 0 option in the slicer??!! I've googled everywhere but cannot find an answer and it is driving me crazy!
 
Here's a little more info. I believe it is there for items with no data, but there are no items without data in that particular field (Agency Name). It also isn't that the pivot table refers to an extra row with no data - it refers to Table1. So I don't see any blank or missing data anywhere, yet it is still giving me 0 as a column label.


I unchecked "show items with no data" so the 0 doesn't show in the pivot table, but the problem is it still shows up as an option when I use Agency Name as a slicer.
 
Derp how embarrasing, I figured it out.


In the case when it showed blank, it was because my data source was a range that included blank rows.


In the case where it showed 0, for some reason in the Table it was automatically adding another row for 0. As soon as I changed it back to a range instead of formatted as a table, the 0 column label disappeared.


Sorry for the interruption. You may now return to your regularly scheduled programming.
 
You must log in or register to reply here.
Back
Top