How to Preserve PivotTable and PivotChart Formatting After Refresh
Question details
The user needs a way to prevent custom formats, borders, and data labels from resetting when updating a PivotTable and its linked PivotChart.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Refreshing or modifying an existing PivotTable that has customized formatting and an associated PivotChart.
- Observed behavior
- Number formats, cell borders, and chart data-label formatting are lost whenever the PivotTable is refreshed or altered.
Ensure your PivotTable fields are fully organized before applying detailed formatting, as making major structural changes to rows or columns can sometimes override custom rules regardless of your settings.
Enable Formatting Preservation and Apply Custom Styles
Adjusting the built-in PivotTable options and applying a named style is the most reliable way to keep table formats intact during a data refresh.
By default, Excel may rebuild the PivotTable layout when refreshed, dropping manual adjustments. Enabling the formatting preservation setting and utilizing a named custom style ensures your borders, fonts, and number alignments are locked in.
Right-click any cell inside your PivotTable and select 'PivotTable Options' from the context menu.
In the dialog box, navigate to the 'Layout & Format' tab. Check the box labeled 'Preserve cell formatting on update' and click 'OK'.
To guarantee borders and shading remain, click inside the PivotTable, go to the 'Design' tab under PivotTable Tools, and click the 'More' dropdown in the PivotTable Styles gallery. Select 'New PivotTable Style' to create and apply a custom saved style to your table.
Use a VBA Macro to Reapply Chart Formatting
If specific PivotChart data labels and number formats still disappear after refreshing, use a simple VBA script to automate the reapplication of these elements.
Effortlessly Manage PivotTables with WPS Office
WPS Spreadsheet provides robust and highly compatible PivotTable features that mirror your familiar workflow. You can easily summarize complex data and lock in your custom formatting without losing styles after a refresh.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Insert a PivotTable: Navigate to the 'Insert' tab and click 'PivotTable' to generate your data summary.
- 3. Preserve Formatting: Right-click the generated PivotTable, choose 'PivotTable Options', navigate to the Layout & Format tab, and check 'Preserve cell formatting on update'.
- 4. Refresh with Confidence: Format your table using the Design tab, and right-click to 'Refresh' your data anytime knowing your formatting will remain intact.
Frequently Asked Questions
Why does my PivotChart lose its formatting when the PivotTable updates?
When a PivotTable refreshes, the underlying data structure is dynamically rebuilt. If 'Preserve cell formatting on update' is unchecked, or if standard formatting is manually applied over dynamic cells rather than using a custom named style, the software reverts to the default layout, wiping custom chart elements like data labels.
Does 'Preserve cell formatting' apply to column widths as well?
No, preserving cell formatting primarily affects colors, borders, and number formats. To keep column widths from changing automatically during a refresh, you must also uncheck the 'Autofit column widths on update' box located in the same 'Layout & Format' tab of the PivotTable Options.
How do I create a custom PivotTable style?
Click anywhere inside your PivotTable, navigate to the 'Design' tab, and click the 'More' dropdown arrow in the PivotTable Styles gallery. Select 'New PivotTable Style' at the bottom. A dialog box will appear allowing you to define specific borders, fonts, and fill colors for each structural element of the table.
Will saving my file as a Macro-Enabled Workbook affect its security?
Yes, Macro-Enabled Workbooks (.xlsm) can run automated VBA scripts, which poses a potential security risk if the file is shared. If you use VBA to preserve chart formatting, ensure you only run macros from trusted sources and keep your software's macro security settings properly configured.
