Solved

Pivot Table - supress display of subvalue where no label exists

Posted on 2011-03-02
4
282 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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