can i avoid repeating rows when adding calculated item in pivot ta

  • Thread starter Thread starter sophie
  • Start date Start date
S

sophie

I need to add a calculated item for a pivot table
I tried to add a caculated item to field F3. Formula1 = C + H (see below)
The resulting PT has some added unnecessary rows.
This is a simplified version of the table. The original table has 500 rows
under f2 field and they are all repeated for each of field F1 and given a
formula1 item with zero values.
Is there a way to avoid these unnecessary rows.
Many thanks for your time.

original Pivot table


Data
F1 F2 F3 oct nov dec
R1 m mn C 2 1 4
R2 G 5 6 3
R3 H 9 5 5
R4 ms C 3 5 1
R5 G 5 4 3
R6 H 4 3 2
R7 w wn C 6 8 7
R8 G 10 13 15
R9 H 6 8 9



Pivot table after adding item to field F3. Row R9,R10,R11 are unneeded.

Data
F1 F2 F3 oct nov dec
R1 m mn C 2 1 4
R2 G 5 6 3
R3 H 9 5 5
R4 Formula1 11 6 9
R5 ms C 3 5 1
R6 G 5 4 3
R7 H 4 3 2
R8 Formula1 7 8 3
R9 wn Formula1 0 0 0
R10 w mn Formula1 0 0 0
R11 ms Formula1 0 0 0
R12 wn C 6 8 7
R13 G 10 13 15
R14 H 6 8 9
R15 Formula1 12 16 16
 
Hi,

Please do the entries in the source data and refresh the pivot tabe. The
table will display the data as disired.
While entering any data in the source data be sure that the entries are made
with in the selected table array for pivot table.
 
No need for a Calculated Item
Delete Formula1.
Field settings for F1, F2 and F3 respectively:
None, Automatic, None
In the dropdown list for F3
uncheck G
The subtotals will show C+H.
 
thank you for the reply, but in the actual listing is too long to go in and
insert the formula line for each group.
 
Thank you for your answer. however, i still need to display the data for all
of the catagories in F3 (C, G , and H) in addition to the the total for C+H
where applicable. my difficulty is in not being able to find a quick way to
avoid such formula rows where it does not make sense ( as when there is no
data for C and H).
 
Back
Top