Pivot tables can be frustrating because, when you refresh them, they often seem to forget your preferred number formats, sometimes only in certain cells. If you want to ensure that your pivot table remembers and reuses your number format preferences every time it’s updated or new data is added, there are two methods to achieve this. So to format Pivot Table values correctly…
Table of contents
YouTube : Stop Excel changing number formats in a Pivot Table
The Correct Way to Format Pivot Table Numbers
To correctly format pivot table numbers, right-click on the desired value field in the pivot table, select ‘Value Field Settings’, and click on ‘Number Format’.
In the ‘Number Format’ dialog box, choose your desired format and click ‘OK’. This method applies the format to all values in the related field.
The reason this works is because the formatting is done using the Pivot Table tools. As such Excel now associates the formatting to the Pivot Table and not individual cells.
Alternate Method
Right-click on the pivot table value and you can choose ‘Number Format’. We suggest you use ‘Number Format’ as this ensures the formatting remains intact even when the pivot table updates. Avoid using ‘Format Cells’ because it only changes the cell appearance, not the underlying pivot table settings.
By applying either of the methods above, all the numbers in the pivot table will adopt the same format, even though only one of the values were selected, and the pivot table will maintain a consistent number format even when you refresh or expand the pivot table.
Other notes about Pivot Table formatting
Please remember that formatting the source data (the database used to create the Pivot) has no impact on the Pivot Table. Formatting MUST be done directly on the Pivot Table to work.