Forum Discussion
Pivot Table Data Centering
On my pivot table, I can format to have the data centered in my columns, but once I exit that table it all goes back to un-centered. Is there a command that makes it stay centered. The Worksheet I get the pivot table from stays centered.
2 Replies
HelloWTF1,
Yes. This happens because Microsoft Excel resets PivotTable formatting unless you tell it not to.
To keep your data centered, click inside the PivotTable, open PivotTable Options, and enable “Preserve cell formatting on update.” Then apply center alignment again.
Microsoft confirms this behavior and setting here:
https://support.microsoft.com/en-gb/office/pivottable-options-27c02eb7-27de-4b3f-9677-c48e3fe7637bOnce that option is turned on, your centering will remain instead of reverting when you leave or refresh the PivotTable.
FIrst, I've not been able to replicate your situation. If I center the data, it stays centered through restarting the workbook, through refreshing the data.
Second, though, and perhaps more important, I can't imagine why you'd want to center the data. Usually (I've never seen an exception) the data in a Pivot Table consists in counts or totals or averages or .... always some numeric figure. And numbers are clearer if you know that the number on any given row is arrayed in the same manner as the rows above and below. I.e., numbers in the tens of thousands will always line up differently than numbers in the hundreds.
So help me (and anybody else interested in chiming in) understand: why do you want your Pivot Table columns to be centered?
