Excel Pivot Table: How to Keep Formatting on Refresh

To keep formatting when an Excel PivotTable refreshes, select the PivotTable, open PivotTable Analyze > Options, and on the Layout & Format tab check Preserve cell formatting on update. If column widths also change, clear Autofit column widths on update in the same dialog.

What the preserve-formatting setting does

Refreshing a PivotTable updates its results from the underlying source data or connection. Depending on the changes and the PivotTable’s settings, Excel may rebuild parts of the displayed table. That can make manually applied number formats, fills, fonts, borders, or other cell formatting appear to disappear. The preserve-formatting option tells Excel to retain cell formatting when it updates the PivotTable.

The setting is stored with the PivotTable, so it is worth checking the relevant table rather than assuming a workbook-wide preference applies. A workbook can contain multiple PivotTables, each with its own options. The setting helps with formatting during refresh; it does not prevent every visual change. For example, new rows or columns may appear when the source gains categories, and the PivotTable’s layout or style can affect how the expanded result looks.

Turn on the setting in Excel for Windows

In Excel for Microsoft 365 on Windows, use these steps:

Click a cell inside the PivotTable you want to protect.
On the ribbon, select PivotTable Analyze > Options. In some ribbon layouts, the Options control is a small button or drop-down in the PivotTable group.
In the PivotTable Options dialog, open Layout & Format.
Check Preserve cell formatting on update.
If you want to keep your chosen column widths, clear Autofit column widths on update.
Select OK, then refresh the PivotTable to confirm that the appearance is retained.

You can refresh the selected PivotTable from PivotTable Analyze > Refresh, or refresh workbook data connections with Data > Refresh All. Refresh All can also update other connections and PivotTables in the workbook, so use it when that broader update is intended. If the Options command is not visible, first click inside the PivotTable; contextual PivotTable tabs appear only when a PivotTable is selected.

Format values at the field level

For numbers, setting the format through the PivotTable field is often more reliable than formatting displayed cells one by one. A cell-level format is attached to the current displayed cells. A field-level number format is associated with the value field, so it is more likely to be applied consistently when items move or new records are included.

For example, to show a Sales value field as currency, right-click a number in that field and choose Value Field Settings. Select Number Format, choose a suitable category such as Currency, set the decimal places and symbol, and confirm the dialogs. Avoid choosing only Format Cells for this purpose: that formats the selected cells, rather than defining the number format for the PivotTable value field.

Use this approach for percentages, dates, counts, and other numeric results as well. Check the source data too: if a field contains text that resembles numbers or inconsistent date values, a number format cannot turn those entries into meaningful numeric values. Formatting changes how valid values display; it does not repair the underlying data.

Keep column widths from changing

Cell formatting and column width are separate concerns. The preserve-formatting checkbox protects cell formats, but Excel can still resize columns during an update if Autofit column widths on update is enabled. For a report with deliberately sized columns, clear that option in the same Layout & Format tab.

After clearing it, set the widths you want by dragging the boundary between column letters or by using Home > Format > Column Width. A long new label may then be clipped or displayed on multiple lines rather than expanding the column. That is a trade-off: fixed widths create a stable report, while automatic sizing can make newly added labels easier to read. If the PivotTable adds columns or rows, you may need to adjust the layout yourself.

Understand PivotTable styles and conditional formatting

A PivotTable style is different from direct formatting. Styles are selected from the Design tab and apply coordinated formatting to areas such as headers, totals, and banded rows. They are useful when you want a consistent overall appearance and can be easier to manage than coloring many individual cells. Direct formatting, such as manually changing a specific cell’s fill or font, can take precedence visually in some cases, but it may be harder to maintain as the PivotTable changes shape.

Conditional formatting is another option when the color should depend on the value rather than stay attached to a particular cell. For example, a rule can highlight high sales values as the PivotTable is updated. When creating a rule for PivotTable data, inspect the rule’s application scope. Excel may offer a choice to apply it to all cells showing values for a particular value field, instead of only the current selected range. Choosing the field-based scope helps the rule cover new or rearranged PivotTable results. Exact choices depend on the selected cells and rule type.

These approaches solve different needs: use a number format for how values display, a PivotTable style for a coordinated table design, conditional formatting for value-driven emphasis, and direct cell formatting for deliberate one-off presentation. The preserve-formatting option is useful alongside them, but it does not replace choosing the right kind of formatting.

Why formatting can still appear to change

When a refresh seems to undo formatting even after the option is checked, identify what changed. A refresh can add a new item, remove an item, change the number of rows, or bring in a different value. Those changes can make the same formatting look different without Excel having erased the setting. For instance, a new category may create another row that did not exist when the original cell formatting was applied.

Check these common causes:

The wrong PivotTable was changed. Select the affected table and verify its own PivotTable Options.
Column autofit remains enabled. This changes widths independently of cell formatting.
The value field’s format was set only on cells. Use Value Field Settings > Number Format for a durable numeric format.
A style or rule changes the appearance. Review the selected PivotTable style and conditional-formatting rules.
The PivotTable’s structure changed. New items, subtotals, or field rearrangements may produce cells with different roles or formatting.
The source values or types changed. A text entry, blank, or newly formatted date can display differently even when the PivotTable’s formatting options are intact.

A practical check is to note the affected field and formatting, refresh once, and compare the same field rather than relying on the same cell address. PivotTable results can move, so cell C8 before a refresh may not represent the same category afterward. If only the number display is wrong, reset the field’s Number Format. If only widths change, adjust the autofit option. If a rule no longer covers added results, revise its application scope.

Excel for Mac and Excel for the web

On Excel for Mac, select the PivotTable and look for its contextual PivotTable options or the PivotTable menu; the available ribbon arrangement can differ from Windows. If the PivotTable Options dialog is available, locate the equivalent Layout & Format controls and enable preservation there. Names and placement can vary between Mac releases, so use the dialog labels rather than assuming every Windows ribbon step is identical.

Excel for the web has a different feature set and interface from the Windows desktop app, and not every PivotTable option is exposed in every web workflow. If you cannot find the preserve-formatting control in the browser, open the workbook in desktop Excel, set the option there, save the workbook, and then check the result after refreshing. When a workbook is shared, ensure that collaborators refresh it in an app that supports the needed PivotTable options.

Choose a stable approach for recurring reports

If you refresh the same report regularly, make the formatting strategy part of the report setup rather than a last-minute repair. A reliable setup commonly combines the preserve-formatting option, a field-level number format, a suitable PivotTable style, and a deliberate column-width choice. Conditional formatting can handle thresholds or exceptions, while direct formatting is best reserved for specific emphasis that must remain visually distinct.

For example, suppose a monthly sales PivotTable has region names in rows and total sales in values. Set the sales field to the required currency format using Value Field Settings, choose a style for headers and totals, and disable column autofit if the report must fit a fixed-width page. Then refresh after adding a month of source records and inspect the new and existing regions. This separates the task of preserving presentation from the task of validating the new results.

If presentation requirements are strict, consider keeping the PivotTable on a calculation or analysis sheet and building a separate report view that references its results. That adds setup and maintenance, but it can give you more control over a fixed layout. A normal PivotTable is usually simpler for interactive analysis; a separate report layout is more suitable when the final output must have a carefully controlled shape.

Frequently Asked Questions

Why does my PivotTable lose formatting every time I refresh?
Most often, Preserve cell formatting on update is not enabled for that PivotTable, or the formatting was applied only to cells that moved or disappeared. Enable the option and use the value field’s Number Format setting for numeric results.

How do I stop a PivotTable from changing column widths?
Select the PivotTable, open PivotTable Analyze > Options, and clear Autofit column widths on update on the Layout & Format tab. Set the desired widths afterward.

Does Preserve cell formatting on update keep number formats too?
It is intended to preserve cell formatting during updates, including formatting such as number formats. For a number format that should follow a value field as the table changes, also set it through Value Field Settings > Number Format.

Does the setting apply to every PivotTable in my workbook?
No. Check the options for each PivotTable you need to protect. A workbook may have several PivotTables with different layouts and formatting requirements.

Can a PivotTable style replace manual formatting?
A style can provide consistent formatting for the table, but it is not the same as a specific number format or a value-based conditional-formatting rule. Choose the tool that matches the requirement, and check the result after refresh if the PivotTable’s fields or shape change.

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.