Solved

Pivot Table - supress display of subvalue where no label exists

Posted on 2011-03-02
4
285 Views
Last Modified: 2012-05-11
I am attempting to format a pivot table (which I am quite familiar with), but am having a problem suppressing a particular portion.

The data analyzed is three levels deep. It will always be coded at the first level, and may, or may not be coded at the second or third levels. (If coded at the third level it MUST be coded at the second level as well.)

My problem is that, when not coded at the third level the table still shows a value of "blank" of course, with the sum of the amount. I want it just to stop at the second level. See pictures below:
 
 As it appears now
 As I'd like it
However, when I filter to suppress  label = (balnk), the second level also gets suppressed, so that no data shows.

To be clear: I want to suppress the lowest level(s) of blank data, but preserve their presentation at the next-highest level if also non-blank.

Can't figure out how to do this!!
0
Comment
Question by:taxbuster
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 35025457
The only way I think you can do that is with an autofilter on the third level. See attached workbook where I set the third level to appear in its own column and then applied the autofilter. The downside is you'll need to refresh the filter if the data changes.
FilterBlanks.xlsx
0
 

Author Comment

by:taxbuster
ID: 35341110
It's not really a solution, but it IS an answer. Not sure otherwise what to do here!!
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35356961
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question