Pivot table format changes on refresh

A common complaint/ query is why the pivot table format changes on refresh and whether there is a way to stop this. This would equally apply to traditional Pivot Tables and the Pivot tables generated out of PowerPivot.

There are actually three separate issues here and all can be adjusted and controlled. So if your pivot table autofits the columns each time you refresh or removes the cell colouring you can read the rest of the page. If the format of the numbers doesn’t work well (you format it as 2 decimals and after refresh some are correctly formatted but others have reverted back to no formatting) then you need to go to  ‘how to format values in a pivot‘.

For the autofit Pivot table issue, if you click on a pivot table and right click and choose PivotTable Options you will see the following:

pivot table format changes on refresh

The bottom 2 options are the key. By default they are both ticked which means that each time you refresh, the columns autofit (and if you have a long heading this can be annoying), and any specific cell formatting is maintained (e.g. I make a cell yellow).

If you switch off both of these the autofit will stop and your column width will be maintained even after a refresh. Your pivot table will also revert to a plain format for the cells.

You can now choose how you want your pivot tables to react to a refresh.

Pivot Table showing deleted data?