Solved

Pivot Table - supress display of subvalue where no label exists

Posted on 2011-03-02
4
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

728 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