Troubleshooting Excel PivotTables
- My Beautiful Formatting Disappeared!
- I Cannot Pivot the Report
- The Summary Function I Want is Not Available
- Response Is Slow With External Data
PivotTables make certain kinds of data analysis possible. I won’t say they make analysis simple because PivotTables are not necessarily the easiest thing to understand, and a lot of people run into problems when using them. Here are some tips for solving or working around some of the most commonly encountered problems.
My Beautiful Formatting Disappeared!
Have you ever refreshed a PivotTable or changed its layout only to see your carefully designed formatting vanish? To avoid this situation (which you cannot always avoid), do the following:
- If you are using an autoformat, make sure that the AutoFormat Table option is selected in the PivotTable Options dialog box.
- For non-autoformats, such as font and color changes, you should have the Preserve Formatting option checked (also located in the PivotTable Options dialog box).
Some formats—most notably conditional formatting and cell borders—cannot be preserved. If possible, wait until the PivotTable is in its final form before applying these formatting changes.