Solved

Hide zero value rows on a pivot table

Posted on 2014-12-10
4
156 Views
Last Modified: 2014-12-10
Hi,

I have data in a pivot from Columns C-M - if all values are zero, can that row be hidden?

Can I do that within the PT or using VBA?

Thanks
0
Comment
Question by:Seamus2626
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
You can do it as a filter. Put the value field in the Filter section (as well as the Values field), and then:

1. Click on the arrow.
2. Select "Select Multiple Items".
3. De-select 0 and blank.

That's one way - it's dangerous, as it would also exclude any new values that are not already there...
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
A better way would be to create a new column in your source data saying "Is Zero Value", and have a simple formula

=if(or(g3="",g3=0),"Yes","No")

where g3 is the value for a particular row.

Then refresh (and if necessary, enlarge the source of) the PivotTable, add "Is Zero Value" into the Filters section, and select "No".
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Are columns C:M separate fields, or separate items in the same field?
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

6 Experts available now in Live!

Get 1:1 Help Now