How to Keep Pivot Chart Formatting in Excel After Refresh

To keep a PivotChart looking consistent in Excel, finish arranging the PivotTable fields first, format the chart, and avoid changing its structure unnecessarily. In PivotTable Options, turn on Preserve cell formatting on update for the PivotTable, but note that this setting protects cell formatting, not every chart choice. If refreshes or changing categories keep disrupting a carefully styled chart, use a regular chart fed by a stable helper range instead.

The steps below apply to Excel for Microsoft 365 on Windows. A PivotChart is linked to its PivotTable, so refreshing data or rearranging fields can change the chart’s series and categories. Excel generally retains chart formatting when the chart structure remains the same, but it cannot always preserve formatting when the underlying series or layout changes.

What You’ll Need

An Excel workbook containing a PivotTable and its PivotChart, or a source data range from which you can create them.
Permission to refresh the PivotTable and edit the chart. If the workbook is shared or protected, you may need the owner to allow changes.
A saved copy of the workbook before making broad layout changes, especially if the current chart formatting is important.

Step-by-Step

Settle the PivotTable layout before styling the chart. Click inside the PivotTable and review the fields in the PivotTable Fields pane. Decide which fields belong in Rows, Columns, Values, and Filters, and confirm that the desired categories and value calculations are present. Adding or removing a field later can create or remove chart series, change category labels, or alter the chart type’s usefulness. If the PivotChart already exists, you can still adjust the layout, but do that before applying detailed formatting.
Set the PivotTable update options. Click inside the PivotTable, then choose PivotTable Analyze > Options. In the PivotTable Options dialog, open the Layout & Format tab. Select Preserve cell formatting on update. If you do not want column widths to keep changing when the PivotTable refreshes, clear Autofit column widths on update. Click OK. These options help preserve the PivotTable’s cell appearance and widths; they are useful for a tidy report, but they do not promise to retain every PivotChart style, series color, or axis setting.
Format the chart after the field layout is settled. Select the PivotChart and use the Chart Design and Format tabs. Add or edit a title with Chart Design > Add Chart Element > Chart Title. Use Chart Design > Add Chart Element for items such as axis titles, data labels, and a legend. To format a particular series, select it in the chart, right-click, and choose Format Data Series. To edit an axis, right-click the axis and choose Format Axis. Make one change at a time and check that the selection is the chart element you intend to format.
Choose colors and number formats that fit the data. Pick a chart style or colors from Chart Design, then check each series if you need specific colors. For values such as percentages or currency, right-click the value axis, select Format Axis, and use the Number options in the formatting pane. If values are displayed as percentages in the PivotTable, confirm that the chart axis is also understandable as a percentage. Avoid relying only on a color’s position in the default palette: a changed series order can make the same color represent a different category.
Hide chart controls if they interfere with the finished design. PivotCharts can display field buttons that let viewers filter or rearrange fields. To remove them from the chart display, select the chart and choose PivotChart Analyze > Field Buttons > Hide All. Hiding buttons changes the presentation, not the PivotTable field layout. Keep them visible if users need to filter from the chart itself; slicers are another option when you want visible, deliberate filter controls.
Test a refresh before relying on the chart. Save the workbook, then refresh using Data > Refresh All or click the PivotTable and choose PivotTable Analyze > Refresh. Check the title, axes, legend, number formats, series colors, and labels. Also test the filters or slicers that readers will use. If the chart changes, note whether the change came from new data values or from a structural change such as a new category or field. That distinction helps you choose the right fix.
Keep the PivotChart structure stable where possible. If the chart must keep a particular series order or set of categories, avoid moving fields between the Rows and Columns areas after styling. If new categories are expected, consider whether the chart should show them, whether the legend needs to accommodate them, and whether the axis scale should remain automatic. A refresh that only updates existing values is less likely to disturb the chart than a refresh that introduces new series or changes the fields shown.
Use a regular chart when formatting must remain independent. Create a small helper range on a report sheet and link it to the PivotTable results. Build a standard chart from that range with Insert > Recommended Charts or the relevant chart type on the Insert tab. A regular chart is not controlled by PivotChart field buttons and can be easier to style consistently. Keep the helper range’s labels and value links stable, and allow room for the categories you expect. If categories appear or disappear, update the helper range and chart source as needed. This is the more dependable choice when the chart’s exact appearance matters more than direct PivotChart filtering.

Common Mistakes

Assuming Preserve cell formatting on update protects the chart. The option is about PivotTable cells. It can help keep the table tidy, but chart colors, labels, axis settings, and series formatting may still change when the chart structure changes.
Applying detailed formatting before completing the field layout. Changing a field from Rows to Columns, adding a new value field, or switching the chart type may change which chart elements exist. Format after the report structure is mostly final, then test any planned changes.
Confusing a refresh with a chart-format reset. A refresh can bring in new categories or values, while a layout edit can change the chart’s series. Check the PivotTable and field list to identify what changed before reapplying formatting.
Using chart colors as the only way to identify categories. A reordered or newly added series can affect how colors appear. Keep a clear legend, use readable labels, and choose colors with enough contrast.
Deleting or rebuilding the chart without checking its connection. A replacement chart may be a regular chart rather than a PivotChart, or it may point at an incomplete range. Confirm the chart type and its source before sharing the workbook.

Tips

Save a baseline copy after the chart is styled and before changing PivotTable fields. This gives you a reference if a later edit changes the layout.
Use a consistent workbook theme and restrained chart palette. A coherent theme is easier to maintain than individually coloring many categories by hand.
Keep chart titles descriptive but not tied to a filter choice that changes. If a title needs to explain a selected period or region, make sure it remains accurate as users filter.
Use slicers for a clear, user-facing filter experience when appropriate. Select the PivotTable or PivotChart, then use PivotTable Analyze > Insert Slicer and choose the relevant fields. Slicers change the displayed PivotTable data and therefore the chart, but do not guarantee preservation of formatting if the chart structure changes.
For a report that will be viewed but not interacted with, consider whether a standard chart or a static exported image is more suitable. A static image will not update with refreshed data, so use it only when that trade-off is acceptable.

Troubleshooting

Some colors or series formatting changed after refresh: Compare the chart’s series and legend with the PivotTable fields. If a new category or value series appeared, Excel may have created a new chart element without the formatting you applied to existing elements. Format the new series, or remove unwanted categories from the report design. If series order changes repeatedly, use a helper range and regular chart so the chart series are not directly rearranged by PivotChart field changes.

The chart type changed or the chart now looks unsuitable: Select the chart and inspect Chart Design > Change Chart Type. Choose an appropriate type for the current data, then review the axis, labels, and series formatting again. Changing chart type can affect formatting, so make that decision before the final styling pass where possible.

The axis number format reverted or looks wrong: Right-click the axis and choose Format Axis, then review the Number settings and the axis bounds. A new data range can make automatic bounds look different even if the format remains. Set bounds only when there is a clear reporting reason; fixed bounds can hide changes outside the chosen range.

New categories do not appear as expected: Refresh the PivotTable and check its source data range or source table. Confirm that the new records are included in the source, then refresh again. If the new categories appear in the PivotTable but the chart does not show them as you expect, check the PivotChart filters and the chart type. In a regular chart built from helper cells, expand the helper range and its chart source to include the new category.

Formatting options are unavailable: Make sure the chart itself is selected rather than the PivotTable, and check whether the workbook is protected or opened with limited editing permissions. Excel for the web supports many workbook tasks but may not expose every desktop PivotChart editing control. Open the workbook in Excel for Microsoft 365 on Windows for the full ribbon paths described here. Excel for Mac has PivotTable and chart tools too, but ribbon labels and dialog placement can differ; look for the PivotTable and Chart tabs and the equivalent options.

Frequently Asked Questions

Does Preserve cell formatting on update keep PivotChart formatting?
Not by itself. It preserves PivotTable cell formatting during updates. Chart formatting is affected by the chart structure, and series or category changes can still alter the result.

Why does a PivotChart change when I refresh the data?
A refresh can add or remove categories, update the series, or change the values shown. When the chart’s structure changes, Excel may not retain every custom setting in the same way as it does when only values update.

Can I make a PivotChart keep the exact same colors?
You can apply colors to its series, but a changing series list or order can make the result different. For a fixed visual mapping, keep the series structure stable or chart a stable helper range with a regular chart, then check the result after updates.

Will a slicer change my chart formatting?
A slicer filters the PivotTable and its connected PivotChart. Filtering among existing items usually changes what is displayed rather than the chart’s design, but the appearance can vary if the filter changes which series or categories are present.

Is a regular chart better than a PivotChart for a report?
Use a PivotChart when direct PivotTable filtering and field interaction are important. Use a regular chart linked to a helper range when you need more control over a stable chart layout and are willing to maintain that range as categories change.

Do the steps work in Excel for Mac or Excel for the web?
The general approach is similar in Excel for Mac, although ribbon and dialog labels can differ. Excel for the web has fewer editing controls for some PivotChart tasks; use desktop Excel when a control described here is missing.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.