How to eliminate % totals in a Pivot Table

D

Doria/Warris

Hi,
In my Pivot Table, I have a field in the Column part which contains
different items (from a DB).
I try to simplify with an example: 3 months containing volume data measured
in Tons (3 columns), then different additional columns measuring the Actual
Vs budget in %
All the % are calculated automatically into the source DB, so no calculation
is made in the pivot.
The issue is that in the Subtotals and Totals I get the sum for the volumes
of every month, which is fine, but I also get the sum of the % Vs budget,
which of course is not a good thing. So, I end up to have:

FIELD
Jan Feb March Actual Vs Budget
10 5 15 98%
10 20 30 90 %
---------------------------- -----------------------------
20 25 45 188%

To my knowledge, I can only format the Sub or Totals per FIELD and not per
single item.
How can I eliminate or hide the % summing up (188 in my example?)

I was thinking to conditional formatting, but I cannot say when Cell X is
showing "Hide it", because the cells where the totals appear change every
month.

If you think the only solution I have is to let the pivot table calculate
the Actual Vs Budget, resulting in adding another FIELD, please, let me know
where I should look at to enter the formula.

Thanks for any help
Alex
 
D

Debra Dalgleish

You could use conditional formatting, based on the headings:

Select all the cells on the worksheet (Ctrl+A)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:

=AND(ISNUMBER(SEARCH("Total",$A1)),ISNUMBER(SEARCH("Actual",A$4)))

-- this assumes your column headings are in row 4

Click the Format button, and on the Font tab, choose white as the
font colour
Click OK, click OK
 
D

Doria/Warris

Hi Debra,

Thanks for your suggestion.
However, the formula gives me an error, could you please recheck it?

Thank you
Alex
 
P

Peo Sjoblom

What kind of error? You realize that you should use format>conditional
formatting, right?
 
D

Doria/Warris

Hi,

Of course I'm aware that I am using conditional formatting, I just change
the 4 with a 7, which is actually where the Heading is and then an error
message pops up:

The formula you typed contains an error

When then I click OK on the error window, the cursor highlights this part of
the formula: "Total",$A1

I don't understand why.

Alex
 
D

Debra Dalgleish

Depending on your regional settings, you may need to replace the commas
in the formula with semi-colons.

=AND(ISNUMBER(SEARCH("Total";$A1));ISNUMBER(SEARCH("Actual";A$4)))
 
D

Doria/Warris

Debra,

You're great, the problem is solved, at least temporarily.
Maybe, I will work to find a better solution by having the formula
calculated in the pivot itself.

May I ask you if you are aware of a good "pivot tutor" website, or book or
CD, so that I can improve myself?

Thank you!
Alex
 
Top