Solved

Pivot Table - supress display of subvalue where no label exists

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now