Pivot tables - Hide data conditionally

[jojones]

Good morning.

I have a pivot table which gets data from an Access db. It sums amount by certain parameters and returns totals. Where that total is = 0 I do not want to see it in my pivot table, but I cannot seem to find how to hide it, as the total is not a column or row field, but derived from the data in Access.

Can anyone help me get a resulting Pivot table which does not show totals = 0?

Thanks
Jo

 

[jpn]

On the Tools menu, click Options, and then click the View tab.
To display zero (0) values in cells, select the Zero values check box.

To display zero values as blank cells, clear the check box.
Then you do the Pivot Table Operations First set this for the entire Document


jp

 

[jojones]

Sorry JP, I didn't explain myself very well. I actually don't want the rows of data to appear at all in the table where the totals = 0.
I did what you said above and that just blanks the cells where the value = 0.

any ideas? other than writing a macro to run through the data and delete those rows.

 

[jpn]

May be you can pull the data from access using a Query check for rows which has zero's and save in a temporary Table and then pull the data from the table in your Pivot Operations.
I will check for any other possibilitis and post it

jp

 

[DaleWatson123321]

Jo,

Another option...

First, bring in your data from Access to a separate sheet. Then, using VBA code related to Data - Filter - Advanced Filter, extract the data with non-zero values to your sheet containing your Pivot Table.

Not many Excel users utilize the powerful "database functionality" related to Data - Filter - Advanced Filter. Part of the reason is due to an ERROR message that tells the user that one cannot extract data from one sheet to another. This is NOT the case - indeed it IS possible to selectively extract data, based on your own set of criteria, to a SEPARATE sheet.

In addition, there are quirks related to the use of formulas in the criteria. For example, in cases where a formula is used, Excel requires that you do NOT use a field name. However, if you use text as your criteria, then you MUST use a field name.

However, after identifying and avoiding the bugs and quirks, this built-in “database functionality” can be VERY useful – in a variety of different situations, and should definitely be “a utility tool in one’s Excel toolkit”.

To give you a better appreciation for those who have been pleasantly surprised by this POWERFUL function, can I refer you to a couple of threads… thread68-272337 and thread707-270238.

I’ve developed a variety of example files for different “database” applications using this Excel-based “database functionality”. If you feel you’d like to consider this option, for this particular task, or for other tasks, please feel free to ask for one or more of these example files. On the weekend, I can be reached at my HOME address.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca