PIvot table calculated item, hiding zero rows

Johnny C

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
I've got a pivot table based on a large s/s with lots of blank rows. I could write a macro to strip rows out but I'd rather no resort to that for various reasons.

Before it was only showing items where there was non-zero data

My problem is that now I've added some calculated items, it's showing them for all the combinations including those that have all zero values.

How do I stop it doing that? It makes the PT a bit too big, I was hoping to fit it onto a single page but at the moment it's taking up 10 pages of mostly zeroes.

TIA
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
PS, in field settings 'Show items with no data' is not checked
 
Upvote 0
Hi, Johnny.

What about when you create the pivot table taking the external data option. If you get a message about no visible cells, try taking 'options' and then 'system tables' so you can then select the appropriate worksheet. When you have the choice - I don't remember exactly - to use MS Query do some filtering to exclude records that are null in one of the fields. Complete the PT: it will have only the records you want.

And, maybe you could do your calculations in SQL and not use the PT calculated field.

HTH, Fazza
 
Upvote 0
The data comes as a CSV file. If I could use that as an external source I could get it to work but this needs to be inherited and maintainable by people who've never heard of SQL.

I'll persevere and post back if I find an easy answer.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Macro to drilldown on a specific pivot table row item
Replies
1
Views
262
ddander54
D
B
  • Question
Pivot Table Zeros
Replies
0
Views
713
bethschmidt0726
B
D
  • Question
Calculated Item - Pivot Table
Replies
5
Views
928
GlennUK
G
C
  • Question
Pivot table Calculated item error
Replies
0
Views
409
Crazyjoe06
C
P
  • Solved
Pivot Table Calculated Field on a specific value
Replies
1
Views
2K
purplefox
P

Forum statistics

Threads
1,214,665
Messages
6,120,804
Members
448,990
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top