You might also be interested in
Replies (10)
Please login or register to join the discussion.
Having got rid of "Sum of"
Just as a side comment to David's note I do the same as him except put a space after all the words too. That way if you centre align the words they truly centre align and arent just off to one side.
You can get rid of "Sum of", you know
A small point, Excel automatically generates "Sum of" or "Count of" in the column heading.
Often this makes the column heading too wide. If you want to get rid of Sum of, just right click on the data to get the pivot table menu, then select Field Settings.
This brings up the Pivot Table field box containing Sum, Count, Average etc. At the top of the box "Sum of Amount" or whatever is highlighted, so you can change it.
Personally, I always click just to the right of the "f" in Sum of, then delete everything to the left. The reason for this is that if you make the field just contain "Amount" Excel gets upset because it is the same as the field name. So make it blank space Amount instead.
Column Widths
I tried Philips method, only to find that on refreshing the widths reverted bach to their previous setting.
A crude alternative I found often works to increase column width is simply to add the required no. of blank spaces to the end of the column heading. Not very scientific but effective.
Why didn't I think of that?
Paul - excellent idea. It might not be very scientific but who cares, it must work. Just make sure all column headings are the same number of characters and allow for "Sum of" to be added.
Then of course reformat the labels as wrapped text fields. And you'll probably be back where you started!
Bordering on suicide...
David,
I see how this all works - except it doesn't work with borders - they're just cleared when I refresh! I'm using Excel 2002 - do you think this is a bug?
'Irritated of Aylesbury'
You're right!
Julian. yes, I'm losing the borders on my version of Excel too.
Looks like a bug. We'll pass this one on to Microsoft Support and come back to you with their comments.
David
Column Widths - I know what you mean!
Yes, I've had the same problem with the column widths. The best solution I've found is to use Enable Selection, highlight all the columns of the data area and then select 'Labels only' (I'm referring to Excel 97). Now, format the labels so that Number is set to Text and Alignment is set to Wrap Text. Then set the columns to the widths you want. And save the table!
It doesn't always work but usually it does and it seems more 'robust' when all columns are the same width - but that might just be my imagination.
PS - if you come up with a better way please let me know!
What about column widths
How can I get the column widths within a pivot table to auto-fit or retain a size I set?
When I refresh the columns go back to their original size.
Tony
Making column widths 'stick' in pivot tables
I've found that I can make all of my formatting preferences 'stick' by unchecking the 'Autoformat table' option within the 'Table Options' menu.
To clarify, right click, anywhere within the pivot table. Select 'Table Options'. Uncheck 'Autoformat table' (3rd option in the list). And ensure the 'Preserve formatting' option is checked (6th option in the list).
You're done.
what version are you on?
Hi Geoffrey It looks like MS have been gradually clearing up the formatting over the years Are you on 2003? Back then I guess most of us were on Excel 2000 or 97.