OmniDocWhy Pivot Table Formatting Changes and How to Stop It
Pivot table formatting resets on refresh or filter because of update settings and manual cell formatting. Here's how to find the cause and keep your layout.
If your pivot table formatting keeps changing, you have probably spent time fixing column widths, number formats, or fills that look right until you refresh the data, filter a field, or move a row. The formatting seems to fall apart for no reason. It usually has a clear cause, and you can fix it without rebuilding the table.
This guide is for anyone who builds summary tables in Excel and needs the layout to hold after the data changes. It covers why pivot table formatting resets, how to tell which of the common causes you have, and how to set up the table so it keeps its look. If you searched for "pivot table losing formatting on refresh" or "pivot table formatting changes when filtered," the steps below address both.
The main idea is simple: pivot tables rebuild their output every time they update, so any formatting you apply directly to the cells can be overwritten or can end up on the wrong rows. Settings control what survives a rebuild, and formatting applied through the pivot itself is much more stable.
Why does pivot table formatting keep changing? The short answer
Pivot table formatting changes because the table rebuilds itself on refresh, filter, or layout change, and your manual cell formatting is either reset by an update setting or stays attached to the wrong cells. Turn on the preserve-formatting setting, turn off autofit on update, and apply formats through the pivot.
| Your situation | What to do | Why |
|---|---|---|
| Column widths reset after every refresh | Turn off column autofit on update in the pivot options | Autofit recalculates widths each time the table updates, which overwrites the widths you set. |
| Fills, fonts, or number formats disappear on refresh | Turn on preserve cell formatting on update | Without it, the table redraws its cells and drops direct formatting. |
| Colors move to different rows after you filter | Replace hand-formatted cells with a pivot style or conditional formatting | Direct formatting is tied to cell positions, and filtering changes which items sit in those positions. |
| Number formats reset when you change the layout | Set the number format on the value field | A value-field format is part of the pivot definition, so it survives layout changes. |
| New data appears unformatted or outside the table | Check and expand the source range | If the source range does not include the new rows, the refreshed pivot ignores them. |
Figure out which problem you have
Several different problems produce the same symptom: the formatting you set is gone. Before you change settings, match what you see to a likely cause.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| Column widths snap back after refresh | Autofit on update is on | Set a column width, refresh, and see whether it changes back. |
| All hand-applied fills and fonts vanish on refresh | Preserve cell formatting is off | Apply a bold font to one data cell, refresh, and check whether it stays. |
| Highlights shift to other rows when you filter | Formatting is on cells, not on the pivot's logic | Filter one item out and see whether the highlight moves to a different item. |
| Number format changes after you drag a field | The format was applied to cells, not to the value field | Open the value field's settings and check whether a number format is set there. |
| New rows have no formatting | The source range is too small, or the new data sits outside it | Compare the source range with the data's full extent. |
| Formatting breaks only in one file | Settings differ between workbooks | Open a new blank pivot to see whether default behavior differs from your file. |
Fix it step by step
Work through these steps in order. The first few fix a common causes and take the least effort.
- Turn off autofit on update. Open the pivot table's options and clear the setting that adjusts column widths when the table updates. Set your column widths again, then refresh to confirm they hold.
- Turn on preserve cell formatting. In the same options area, enable the setting that keeps cell formatting on update. Apply one test format, refresh, and check the result.
- Set number formats on the value fields. Open the value field's settings and choose the number format there, instead of formatting the cells by hand. This format is part of the pivot and carries over when you rearrange fields.
- Replace hand-formatted highlights with a pivot style. Pick a pivot style from the table design options. Styles apply to the table's structure, so they stay with the right rows and headers after filtering.
- Use conditional formatting for highlights that must follow values. If you need a row to turn red when a total is low, create a conditional rule based on the value. The rule applies to whatever value is in the cell, so it does not depend on the cell's position.
- Check the source range. Confirm that the pivot's source covers all rows and columns, including new data. If new rows keep dropping out, convert the source to a table or extend the range, then refresh.
- Test on a copy. Save a copy of the workbook, refresh it, filter a field, and rearrange one item. If the layout holds, apply the same settings to the original.
If the table still changes after these steps, the cause is usually a formatting rule applied to a fixed range that overlaps the pivot. Clear the direct formatting on the pivot area, then reapply only the styles and value-field formats.
When the data itself needs checking, remember that a pivot table summarizes whatever the source contains. Confirm that totals match the source before you trust a formatted report.
Prevent it next time
Small habits stop most resets before they start.
- Set the autofit and preserve settings as soon as you create a pivot table, before you format anything.
- Format values through the value-field settings and use pivot styles. Save hand-formatting for cells outside the pivot.
- Keep the source data in a table or a clearly sized range, so refreshes pick up new rows.
- Avoid merging cells inside or next to the pivot area. Merged cells interfere with layout changes.
- After any change to fields or filters, glance at the headers and totals before you share the file.
- Keep a clean, unformatted copy of the pivot to test changes on.
❓ FAQ
1. Why does my pivot table lose formatting only when I filter?
Filtering removes or reorders items, so any formatting applied to specific cells can end up on different rows or disappear. Pivot styles and conditional rules follow the table's structure or values, so they stay correct when the visible rows change.
2. Will turning off autofit on update affect my data?
No. It only stops the table from resizing columns when it refreshes. Your values and totals stay the same, and you can set column widths again at any time.
3. Can I keep formatting when I rearrange fields?
Yes, if the formatting comes from the value-field number format or a pivot style. Direct cell formatting is more likely to shift or reset when fields move, so use the pivot-level settings for anything you need to keep.
4. What if the formatting resets in a file someone else sent me?
Check the same two settings, autofit on update and preserve cell formatting, in that workbook. The file may have been saved with different defaults. Reapply the formats through the value fields and a pivot style, then save.
📝 Summary
Pivot table formatting changes because the table rebuilds on refresh, filter, or layout change, and direct cell formatting does not always survive that rebuild. Turn off column autofit on update, turn on preserve cell formatting, and move your number formats and highlights into the value-field settings, pivot styles, and conditional rules. Test each change on a copy before you rely on the layout.
