• 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 remove zero value categories from chart

DannyV

DannyV

New Member
Hello all,

I have a pivot table pie chart to display team scores. I want to display only data with values which are greater than zero.

I have selected to display values as well as categories in the label options. I have used the custom number format #;; to hide the number values, but this does not remove the corresponding categories. Does anyone know if it is possible to remove the categories as well?

I am using Excel 2013.

Thanks for your help.
 
Hi Danny, and welcome to the forums! :awesome:

In the PivotTable controlling the chart (or the series filter, if visible), you can apply a filter based on the values to only include data > 0.
Applying filter:
upload_2016-2-1_10-14-17.png

Will change charts from this:
upload_2016-2-1_10-12-33.png

to this:
upload_2016-2-1_10-13-27.png
 
Hi Luke,

Thanks for the quick response!! I was able to reproduce what you are doing, and I did get the same results. Your response also allowed me to see what my problem is, perhaps you can give me a solution for this as well:

My data is not set up as straight-forward as your's. See my screen capture. I arrive at the same results as you, however when I create my PivotTable I cannot filter my numbers.
 

Attachments

  • Capture.JPG
    Capture.JPG
    24.4 KB · Views: 16
Ouch. Is there any chance we can re-design how the initial data is laid out? The current format will give you lots of trouble down the road for data analysis.

If not, an alternative might be to do a Pivot of a Pivot. That's where we'll create a PivotTable based off the cells the first table uses. It can get tricky if your PT can change in size, but w/ your layout, we might be safe. Here's an example. You can see final Pivot still creates the chart we want.

In short, I'd first suggest rearranging your data source if possible. If not, go with Pivot of a Pivot.
 

Attachments

  • Pivot of a Pivot.xlsx
    17.1 KB · Views: 9
A redesign may be possible, however I don't see how I could do it.

This information is being scraped from a database, which I am dumping into Excel. I was not aware that we can do a pivot table with a pivot table, so I will investigate that closely. Thanks for your help.
 
Hi Luke,

I've been playing around with the PivotTables of the PivotTables and have noticed that they don't update automatically. Is there any way to get them to? I've been trying to emulate what Chandoo does is his dashboards by using segments, but the charts don't seem to update when I make changes to the segment selections.

I would also like to have your opinion on how I can redesign my data. I have users entering data into userforms. The data from the userforms is being sent to an Access database which I then basically do a data dump from Access to Excel where I have a dashboard set up to display the results of what the users entered in the userforms. The two main filters I use are users and dates, so it will be difficult (I think) to amalgamate the information into either of the two categories.

Do you have anything to suggest?

D
 
Hi Danny,

To the first part, you are correct. PivotTables do all the calculations at one point in time at the beginning. This saves on calculation time downstream, but it means that it's not constantly looking back at raw data to make sure it has latest and greatest. You can go to Data - Connections - Refresh All (or right-click on each PT and hit refresh), and that will refresh that PTs and bring in the latest data.

For your dashboard, are you wanting to go a strict formula-only route, or are macros allowed? If the latter, we could always put in some code somewhere with a line to
Code: 
ThisWorkbook.RefreshAll

Otherwise, we'll have to get creative.

To best help you figure out a redesign, or at least re-arrangement of the data, can you upload an example workbook, showing roughly the columns you're working with and how they're filled in?
 
Hi Luke,

Thanks for the information. I knew I could refresh the PTs with VBA, but I wanted to know if there was any other way. I had done some previous searches online but didn't come across anything conclusive. I can usually find the missing pièces here though, that's why I asked.

I am quite comfortable with VBA, so I'll do it that way.

Thanks again for your help Luke.

D
 
You must log in or register to reply here.
Back
Top