Excel Pivot Table Format Keeps Changing? Fix It

Charts & pivot tables · Updated November 21, 2026

If your Excel PivotTable formatting keeps changing after a refresh, open PivotTable Options and check Preserve cell formatting on update. Right-click a PivotTable cell, choose PivotTable Options, go to Layout & Format, and select that checkbox; you can also clear Autofit column widths on update if the columns keep resizing.

Why PivotTable formatting changes

A PivotTable is a report generated from source data, not a fixed range of ordinary cells. Refreshing it can add, remove, or rearrange cells as the source data or field selections change. Excel may then update the report’s appearance, including number formats, borders, fills, and column widths. A format that looks stable before a refresh may not remain attached to the same displayed values afterward.

Several different settings can be involved, so the right fix depends on what is changing. The PivotTable style controls the report’s overall design; a value field has its own number-format setting; conditional formatting uses rules; and the update options determine whether certain cell formatting and widths are retained. Changing one setting does not necessarily fix the others.

  • Formatting disappears after refresh: check Preserve cell formatting on update.
  • Columns change width: clear Autofit column widths on update.
  • Numbers revert to General or another format: set the format through the value field’s settings.
  • Colors or bands change: review the PivotTable style and its style options.
  • Rules color the wrong cells: inspect the conditional formatting rule and its range.

Keep cell formatting when you refresh

In Excel for Microsoft 365 on Windows, right-click anywhere inside the PivotTable and choose PivotTable Options. On the Layout & Format tab, select Preserve cell formatting on update, then choose OK. The setting is intended to keep formatting applied to PivotTable cells when the report updates. It is a sensible first fix when custom fills, fonts, or borders vanish after refreshing.

In the same dialog, look at Autofit column widths on update. If it is selected, Excel can resize columns to fit their contents each time the PivotTable updates. Clear it to keep your chosen widths, then adjust the columns manually if needed. This is separate from preserving cell formatting: a PivotTable can retain its colors while still changing column widths, or keep its widths while losing a manual format.

These options do not turn a PivotTable into a static report. If fields move, rows are added, or the report’s shape changes, cells can represent different items after an update. A format applied to a particular cell may therefore not appear beside the same item in every possible layout. Check the report after changing fields or refreshing, especially if it is used for a presentation or shared report.

Set number formats in the value field

A common frustration is that currency, percentage, or date formats revert after refresh. Applying a number format directly to visible cells can be less reliable than setting it on the PivotTable field, because the report rebuilds its displayed values during updates.

  1. Right-click a number in the relevant PivotTable value column.
  2. Choose Value Field Settings.
  3. In the dialog, choose Number Format.
  4. Select a category, such as Currency, Percentage, or Date, choose the required decimal places or display options, and click OK.
  5. Click OK again to close Value Field Settings.

For example, if a field sums sales amounts, set that field to Currency or Number through Value Field Settings rather than formatting only the cells currently visible. If the same source field appears in the Values area more than once—for example, once as Sum and once as Average—each instance can have its own settings. Repeat the process for each one that needs a different display.

Also distinguish the stored value from its display. A percentage format changes how a number is shown, not the underlying calculation. If a source value is 0.25, Percentage displays it as 25%; it does not convert an unrelated whole-number value into the intended percentage. Check the source data and the summarized calculation if the displayed result seems wrong.

Review PivotTable styles and banding

PivotTable styles provide coordinated formatting for headings, totals, and alternating rows or columns. Click inside the PivotTable to show its contextual ribbon tabs, then open the Design tab and choose a style from PivotTable Styles. Depending on the Excel ribbon layout, this contextual tab may be described as PivotTable Design. Style options such as row headers, column headers, and banded rows can also affect what the report looks like.

If you apply a style and then manually change a few cells, the result can look inconsistent: the style may control some areas while direct formatting affects others. Try choosing a different PivotTable style before adding individual cell formatting. If you want a plain report, choose a light or neutral style and review the style options rather than layering many manual fills and borders on top.

A style is not the same as preserving formatting on update. The style controls the PivotTable’s design; the option in PivotTable Options concerns retention of cell formatting during updates. If a refresh restores a style-like appearance, check both the selected style and the preservation setting. Avoid relying on a fill color alone to communicate a critical status if a refresh or layout change could alter where that fill appears.

Make conditional formatting follow the right values

Conditional formatting is useful for highlighting exceptions, such as sales below a target or negative balances. But a rule created for a fixed set of cells may not automatically cover new PivotTable rows or may apply to a different set of cells after the layout changes. That can look like the PivotTable changed its formatting even when the rule itself is still present.

To inspect the rules in Excel for Microsoft 365 on Windows, select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. Review the rule’s formula or threshold, its range, and the Show formatting rules for setting in the Rules Manager. Where Excel offers PivotTable-specific application choices for a rule, choose the option that applies it to all cells showing the relevant value field when that matches your goal, rather than restricting it to the current selection.

Test the rule after refreshing and after expanding or collapsing the report. For example, a red-fill rule intended to highlight low sales should continue to highlight the sales values—not an adjacent label column—when new items appear. If you copied formatting from elsewhere, check for overlapping rules as well: multiple rules can produce an unexpected result, and rule order or stopping behavior can matter.

Check whether the report layout is changing

Not every formatting change is caused by the refresh option. A PivotTable’s structure depends on which fields are in Rows, Columns, Values, and Filters. Adding a field, removing one, changing the report layout, expanding a group, or filtering out items can change which cells exist and where totals appear. Excel cannot preserve the meaning of a manual format if the cell’s role has changed.

For instance, suppose you manually color the cells in the third visible row because they represent a particular product. After sorting or adding a new product, that product may move to another row, while the color remains tied to the old cell—or the PivotTable may rebuild the area differently. Use field-level number formats, PivotTable styles, and appropriately scoped conditional formatting for formatting that should follow the data or value field.

Also check the source data and its headers. Blank or duplicated-looking headers, inconsistent data types, and new categories can affect the report’s structure or display. If a source range has grown, confirm that the PivotTable’s source includes the new records; a refresh only updates from the source range or connection it already uses. If the report is based on an Excel Table, adding rows to that table generally allows its range to expand, but refresh the PivotTable to bring the new records into the report.

A practical troubleshooting order

Work through the cause in a deliberate order instead of repeatedly repainting cells. First identify whether the change happens after refresh, after a field move, or simply when opening the workbook. Then check the relevant setting or rule and make one change at a time.

  1. Save a copy if the workbook contains important reporting or carefully customized layouts.
  2. Refresh once using PivotTable Analyze > Refresh, and note exactly what changes. To update multiple PivotTables, use Data > Refresh All.
  3. Check update options through right-clicking the PivotTable and choosing PivotTable Options. Turn on preservation of cell formatting and turn off automatic column-width adjustment if those symptoms apply.
  4. Set value formats through Value Field Settings > Number Format for each value field that needs a stable number display.
  5. Inspect styles and rules if the issue involves colors, banding, or highlights. Check the PivotTable style and the scope of conditional formatting.
  6. Test structural changes by adding or expanding a field, then refresh again. Confirm that formatting follows the intended field or value rather than merely staying in a particular cell.

If only one workbook has the problem, compare its PivotTable Options and rules with a working PivotTable. If several workbooks behave differently from expected, verify that you are editing the same PivotTable you refresh, and check whether the report is connected to an external source or data model. In shared files, another person’s field changes or refresh can alter the report layout, so agree on a layout before applying detailed cell-by-cell formatting.

Windows, Mac, and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. In Excel for Mac, the contextual PivotTable tabs and dialogs can be arranged or labeled differently, but look for PivotTable Options and its layout or formatting settings; right-clicking, or Control-clicking, a PivotTable cell can open its context menu. Ribbon layouts may vary by update, so the dialog’s setting names are more useful than relying on a particular icon location.

Excel for the web can handle many common PivotTable tasks, but the desktop app provides the fullest set of PivotTable controls. If you cannot find a specific preservation or layout option in the browser, open the workbook in the desktop Excel app and check the PivotTable Options dialog there. After changing the setting, refresh and verify the workbook in the environment where it will be used.

Frequently Asked Questions

Why does my PivotTable lose formatting when I refresh?
Excel may rebuild the report during refresh, and formatting can be lost or moved when its cells change. Right-click inside the PivotTable, choose PivotTable Options, and select Preserve cell formatting on update on the Layout & Format tab. Use field-level number formatting for values.

How do I stop PivotTable columns from changing width?
Open PivotTable Options > Layout & Format and clear Autofit column widths on update. Set the widths you want after refreshing. This setting is separate from preserving the rest of the cell formatting.

Should I format PivotTable numbers before or after creating the table?
For a PivotTable value field, set the number display through Value Field Settings > Number Format. That associates the display format with the value field and is generally more reliable on refresh than formatting only the currently visible cells.

Will Preserve cell formatting on update keep formatting after I change the layout?
It helps retain cell formatting during updates, but it cannot make every manual format follow the same item through any structural change. Adding fields, sorting, or changing the report layout can move or replace cells. Use styles, field settings, and suitable conditional formatting when appearance should follow the report’s data.

Why does my PivotTable conditional formatting stop covering new rows?
The rule may apply only to the original cell range. Open Home > Conditional Formatting > Manage Rules, inspect its range and application options, and choose an option that follows the relevant PivotTable value field when available and appropriate. Test it after a refresh that adds rows.

Related

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.