Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
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| +-------------------------------------------------------------------+ -- Ann Van de Velde ------------------------------------------------------------------------ Ann Van de Velde's Profile: http://www.thecodecage.com/forumz/member.php?userid=291 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94099 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
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 -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94099 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
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 Pecoflyer;336619 Wrote: 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 ------------------------------------------------------------------------ Ann Van de Velde's Profile: http://www.thecodecage.com/forumz/member.php?userid=291 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94099 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
Excel 2007
PivotTable of PivotTable. Sort by %. Reformat blank columns. Hide errors. Conditional Formatting with Icons. http://www.mediafire.com/file/zkaigd...05_04_09a.xlsx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
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| +-------------------------------------------------------------------+ -- Ann Van de Velde ------------------------------------------------------------------------ Ann Van de Velde's Profile: http://www.thecodecage.com/forumz/member.php?userid=291 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94099 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
Ann,
My file uses features only found in Excel 2007 and translating it to 2003 would violate my aesthetic sensibilities. Herb |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot tables - Questions on formatting, sorting
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? -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94099 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting in Pivot Tables | Charts and Charting in Excel | |||
Pivot Tables-2 questions | Excel Discussion (Misc queries) | |||
Pivot Tables-2 Questions | Excel Worksheet Functions | |||
Sorting in pivot tables | Excel Discussion (Misc queries) | |||
Sorting data in Pivot Tables | Excel Discussion (Misc queries) |