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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

M
  • Solved
Loop through unfiltered Pivot Table items
Replies
1
Views
304
mikeymay
M
M
  • Question Question
Pivot Table Calculated Item Crashing Workbook
Replies
1
Views
380
cooleomter
C
S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
159
ShadowRider
S
D
  • Question Question
Macro to drilldown on a specific pivot table row item
Replies
1
Views
772
ddander54
D
B
  • Question Question
Pivot table: iterating through each item takes forever
Replies
2
Views
361
RoryA
RoryA

Forum statistics

Threads
1,223,924
Messages
6,175,415
Members
452,640
Latest member
steveridge

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