Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table - Calc Item Problem
Hello,
I'm trying to insert a quarterly subtotal (Jan+Feb+Mar) in my pivot table. The problem I'm having is that when I insert the calculated item, my pivot table creates a bunch of blank rows when I only want subtotals for fields that have data. Example: Company Region Jan Sales Feb Sales Mar Sales MSFT South 10 10 10 HPQ North 15 15 15 Here's the output I'm trying to get with "Q1" my calc item: Company Region Jan Sales Feb Sales Mar Sales Q1 MSFT South 10 10 10 30 HPQ North 15 15 15 45 Here's the output I'm getting with unwanted zero/blank cells: Company Region Jan Sales Feb Sales Mar Sales Q1 MSFT South 10 10 10 30 North 0 0 0 0 HPQ South 0 0 0 0 North 15 15 15 45 How do I only get quarterly subtotals for rows that have data? Another way to put it, how do I get rid of all the rows with zeros? Thanks in advance. |
#2
|
|||
|
|||
I've got the same problem as Scott. Lots of rows with unwanted zeros when calculated item inserted. Has anyone found a way of getting rid of them? alison -- alisonb ------------------------------------------------------------------------ alisonb's Profile: http://www.excelforum.com/member.php...o&userid=23923 View this thread: http://www.excelforum.com/showthread...hreadid=350249 |
#3
|
|||
|
|||
You can use programming to hide the rows with a zero total. For example:
'====================================== Sub HidePivotZeroRows() 'hide worksheet rows that contain all zeros Dim rng As Range For Each rng In ActiveSheet _ .PivotTables(1).DataBodyRange.Rows If Application.Sum(rng) = 0 Then rng.EntireRow.Hidden = True Else 'unhide any previously hidden rows rng.EntireRow.Hidden = False End If Next rng End Sub '================================ Sub UnhidePivotRows() 'unhide all rows Dim rng As Range For Each rng In ActiveSheet _ .PivotTables(1).DataBodyRange.Rows rng.EntireRow.Hidden = False Next rng End Sub '==================================== alisonb wrote: I've got the same problem as Scott. Lots of rows with unwanted zeros when calculated item inserted. Has anyone found a way of getting rid of them? alison -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have a Pivot Table Include a "% of an item" | Excel Worksheet Functions | |||
Item wise balance in Pivot Table | Excel Discussion (Misc queries) | |||
Item wise balance in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Problem | Excel Worksheet Functions | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions |