MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Center data in PIVOT table ?

Posted by Steven on December 11, 2001 7:55 PM


Short question: is is possible to center data in a pivottable ? I have wide headings per column and my values are now aligned at the right hand side of the datafields (which is not very easy to read).

Or do I need VBA for to align the values in the datafield ?

Note that the column headings (!) can be aligned simply by using the center option in Excel. The data unfortunately are NOT centered with this option.

Thanks in advance


Posted by anno on December 11, 2001 8:43 PM

select the pivot table areas you want to align, right click in the pivot table and select 'format cells' from the shortcut menu. select the 'alignment' tab which will show all alignment options, including vertical and horizontal.

Posted by Steven on December 11, 2001 9:11 PM


Thanks for your (quick !)feedback and you're right it does work that way. I have tested with a new pivot table and it worked. The only weird thing is that the p-table I am using does not (?!) react on the "center" instruction (only the header is centered but not the data .....). Very strange. I might need to rebuild my table to get rid of this error (I suppose).

Thanks again


Posted by Chris D on December 12, 2001 5:59 AM

It could be that you have an option to refresh upon opening, which will always refresh the data...however, if you do not have the selection button depressed, it will renew the formatting each time, appearing to forget your centering

try right click on the pivot and choose "select" then choose "enable selection"..... this should preserve any formatting you carry out (ie centering) when you manually refresh or refresh upon opening the pivot table


Posted by Paul Johnson on December 12, 2001 6:20 AM

It could also be the number format chosen. If you selected the "," from the toolbar - it will align it right. Try changing the number format as well.

Posted by Steven on December 12, 2001 3:07 PM

Thanks guys, you were right, it had to do with the Enable Selection option. The table looks much better now !