Pivot tables - Questions on formatting, sorting

[Ann Van de Velde]

I enclosed a file to illustrate my questions.

In the enclosed pivot table I managed to display not only the absolute
2007 and 2008 turnover figures but also the difference in % as well as
the difference in €.

This leads to my next problems :
QUESTION 1
For the extra added calculated fields (difference in % and difference
in €) I get 2 sets of each 2 columns : each set contains a blank column
(here for the year 2007). How can I avoid these blank columns ? Is there
any way other than change the width of the column to 'hide' each 2007
column ? I tried hiding the columns but then I loose the title
information (here % Diff 2007), so I left the empty column as a width of
1 and thus kept title information.

QUESTION 2
New customers for 2008 (so with turnover 2007 zero) result in an
"error" in the % difference column : “#DEEL/0” (in an English version
this is"#DIVIDEBY/0"). Can this be avoided in any way ? Conditional
formatting ? But how do I do this ?

QUESTION 3
I would like to use conditional formatting for the results in the
calculated fields, as I do outside of pivot tables : if the result >=
0,1%, then show the result in green ; if the result <100%, then show the
result in red.

QUESTION 4
When I try sorting the pivot table by “sorting & top 10” in the Pivot
Table menu and pick the Sort on one of the calculated fields the sort is
not executed properly : say I would like to have the sort to be with on
top the company with the biggest % rise in turnover. Is this possible ?

I hope I can get help through this forum. It will be highly appreciated
!

Kind regards, Ann


+-------------------------------------------------------------------+
|Filename: Pivot Table_Problem.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=125|
+-------------------------------------------------------------------+

 

[Pecoflyer]

Hi and welcome to the board

*Question 2*

Right-click on a cell in the pivot table
Choose Table Options
Add a check mark to 'For error values, show'
Leave the text box empty
Click OK

I'll be back for the other questions if they were not solved

 

[Ann Van de Velde]

Thanks for your reponse .
I did exactly what you told and saw that the 'For error values, show'
box was already left open and ticked, (didn't work with an empty box)
but when I added a value "n.a." this was shown and this is acceptable.
So thank you for your help !

In the meantime the Question 3 has also been resolved !

Please please an answer to Question 1 and 4... ?

Kind regards,
Ann

 

[Herbert Seidenberg]

Excel 2007
PivotTable of PivotTable.
Sort by %.
Reformat blank columns.
Hide errors.
Conditional Formatting with Icons.
http://www.mediafire.com/file/zkaigdjnwmn/05_04_09a.xlsx

 

[Ann Van de Velde]

Thanks for the help Herbert !
BUT, the problem seems to be that I work with Excel 2003 and my screen
looks completely different than what I see in your enclosed file wko.
Please have a look at my new enclosed file, as I don't get the needed
result emb1

Thank you !!!


+-------------------------------------------------------------------+
|Filename: Pivot Table_Problem_bis.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=128|
+-------------------------------------------------------------------+

 

[Herbert Seidenberg]

Ann,
My file uses features only found in Excel 2007
and translating it to 2003 would violate
my aesthetic sensibilities.
Herb

 

[Pecoflyer]

Ann,
as we seem to be stuck ( it's all probably possible in VBA but I'm no
good at that) maybe we could use an alternative to Pivot Tables.
Would this be acceptable ? ( no "dynamics" of course)
Do you have a list of the sales people?