Format a Pivot Table So It Survives a Refresh
By Mark Fulton · 2026-10-02 · 13 min read
Most pivot table formatting advice is about how the table looks on the day you build it. The part that costs people an afternoon is what happens on the next refresh, when the widths snap back, the number format disappears and the colour you painted on row 14 is now on the wrong customer.
A pivot table keeps a format when you attach it to the pivot's own settings, and loses it when you paint it onto cells. Set number formats through Value Field Settings, turn off "Autofit column widths on update" and keep "Preserve cell formatting on update" ticked, pick the layout (tabular form with repeated labels reads best as a report), and build colours into a PivotTable style instead of filling cells by hand. Do those four things once and the table looks the same after every refresh. The rest of this post says where each one lives and why the order matters.
Why does refreshing a pivot table undo your formatting?
Because a pivot table is not a block of cells you own. It is a report Excel redraws from the source data every time you refresh, and the cells it draws are placed by the pivot, not by you. When the data changes, the pivot rebuilds the grid. A new product shows up in the middle of the list, a region disappears, a month is added. Every row below the change moves.
Formatting you applied to a specific cell is tied to the cell's position. Formatting you applied through the pivot's settings is tied to the field, the value or the whole report. That difference explains almost every "my formatting vanished" complaint:
- You highlighted the Grand Total row yellow by selecting it. A new item is added above, the grand total moves down a row, and the yellow stays behind.
- You dragged a column wider. The refresh autofit it back to the width of the widest value.
- You typed a number format into the Format Cells dialog on a selection. The refresh brought in a new value field and it came back as General.
Microsoft's documentation for designing the layout and format of a PivotTable names the two switches that decide a lot of this. "Preserve cell formatting on update" saves the layout and format so it is used every time you perform an operation on the PivotTable. Clear it and Excel discards your format and returns to the default each time. "Autofit column widths on update" does the opposite job: when it is on, columns refit to the widest value on every refresh, and when it is cleared the current widths stay.
Both live in the same place: click anywhere in the pivot, then PivotTable Analyze, Options, and the Layout & Format tab.
Where should number formats be set so they stick?
On the field, not on the cells. Click a value in the pivot, go to PivotTable Analyze, Active Field, Field Settings (the dialog is titled Value Field Settings when you are on a value), and select Number Format at the bottom. That opens the usual Format Cells dialog. Pick the category, choose OK twice. Microsoft also notes you can right-click a value field and choose Number Format directly.
Because the format now belongs to that value field, it follows the field when rows are added, when the filter changes and when a new month arrives. Formats applied by selecting cells and pressing a shortcut may survive a refresh with "Preserve cell formatting on update" ticked, but they are only as stable as the cells underneath them. The field setting does not depend on where the numbers land.
A few habits that go with it:
- Pick the format before you rename anything. The Number Format button is in the same dialog as the custom name, so you can do both in one visit.
- Use the same grammar as your source sheet. The format codes work exactly as they do in a normal cell, so the thousands comma, the parentheses for negatives and the percentage codes in the number formatting guide apply unchanged.
- Rename "Sum of Revenue". In the same Value Field Settings dialog, the Custom Name box lets you call it Revenue. Excel will refuse a name that is identical to an existing field name, and the usual workaround is to add a trailing or leading space.
- Set what blanks and errors show. In PivotTable Options, Layout & Format, the "For empty cells show" box controls blanks and "For error values show" controls errors. Leave a box empty to show a blank cell; clear the check box on empty cells to show zeros.
How do you stop column widths jumping on refresh?
Open PivotTable Analyze, Options, then the Layout & Format tab, and clear "Autofit column widths on update". Then drag the columns to the widths you want. From now on a refresh leaves them alone.
This is the single most common reason a pivot report looks different on Monday than it did on Friday, and it only takes one tick box to fix. There is a trade-off. If a new customer name arrives that is longer than the column, it will be cut off or spill instead of being fitted. For a report you send out, that is the right trade, because you would rather check one long name than have every column resize every week. For a pivot you use as a working scratchpad, leave autofit on.
If your widths still look wrong after this, the problem is usually in the source columns, not the pivot. A source column full of text-formatted numbers or stray spaces produces labels that are wider than they look. Run the source tab through a cleanup first. The spreadsheet cleaner flags those columns without changing anything, and the data structure rules explain what shape the source should be in.
Compact, outline or tabular: which layout reads best?
Microsoft describes three forms under Design, Report Layout:
- Compact form puts items from different row fields in one column with indentation. It is the default because it saves horizontal space and leaves more room for the numbers. It also shows expand and collapse buttons.
- Outline form is similar to tabular but can show subtotals at the top of every group, because the next field's items start one row below.
- Tabular form gives one column per field, with room for a header on each. Microsoft's own description is that it shows the data in a traditional table format and makes it easy to copy cells to another worksheet.
| Layout | Best for | Where it falls short |
|---|---|---|
| Compact | Exploring, narrow screens, many nested fields | Labels from different fields share one column, so it copies out as a messy block |
| Outline | Reports with subtotals above each group | Takes more width than compact; subtotals at the top surprise readers used to totals at the bottom |
| Tabular | A report someone else reads, or a pivot you copy out | Wider; needs repeated labels to read well |
For a report that a person reads, tabular form is the usual answer, with one extra step. On the Design tab choose Report Layout, then Repeat All Item Labels, so every row carries its own category name. Without it, the first row of each group has the label and the rows below are blank, which looks tidy but makes the table impossible to scan from the middle and impossible to sort or filter once it is pasted somewhere else. Excel for the web puts the same choice in the PivotTable Settings pane, as Repeat or Don't repeat.
If you want the table to behave like an ordinary table, this is the setting that does most of the work. It is also the reason a pivot can look like the tables described in Excel table design best practices: clear headers, one idea per column, and no gaps.
Merged cells are a separate trap. Microsoft notes you cannot use the Merge Cells check box under the Alignment tab of a pivot. The pivot has its own option, "Merge and center cells with labels", under Layout & Format. Leave it off unless you have a specific reason, for the same reasons merged cells cause trouble anywhere else in a workbook.
How do you build a custom PivotTable style?
A style is the one formatting layer that the pivot reapplies itself after every refresh. It describes the look of the header row, the whole table, the first column, the subtotal rows and the grand total as a set of rules, not as fills on cells, so there is no cell for a refresh to leave behind.
To make one:
- Click inside the pivot and open the Design tab.
- In the PivotTable Styles group, expand the gallery. At the bottom, select New PivotTable Style. Microsoft's page documents this route.
- Alternatively, right-click an existing light style and choose Duplicate, which gives you a copy you can edit without touching the built-ins.
- Name it, pick a table element from the list (Whole Table, Header Row, Subtotal Row, Grand Total Row and so on), choose Format, and set the font, border and fill.
- Select OK, then apply it from the gallery. The custom styles appear at the top.
Start from the plainest style and remove things rather than adding them. A thin line under the header row, a thin line above the grand total and no banding gets you a result that looks like a deliberate report. If you do want banding, the Design tab has Banded Rows and Banded Columns in the PivotTable Style Options group, and Row Headers and Column Headers to include the headers in the banding. The banded rows post covers when banding helps and when it only adds noise.
Two practical notes. A custom style is stored in the workbook where you made it, so it does not appear in a new file. The usual way to carry it across is to copy a sheet containing a pivot that uses it into the other workbook with Move or Copy, then delete the copied pivot; the style stays in the gallery. And if you pick a theme-based fill, the style follows the workbook theme, which is useful when the file is rebranded and a nuisance when you opened it in a different theme than the one you designed in.
Conditional formatting is the other tool that persists, within limits. Microsoft says that when you change the layout by filtering, hiding levels, collapsing and expanding, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed. It also says the scope of a rule on the Values area can be set by selection, by corresponding field or by value field. Choose the value field scope for a report you will refresh, because a rule scoped to a selection of cells is the same position problem as a hand-painted fill. For the rules themselves, conditional formatting that actually helps explains which ones are worth the pixels.
Which subtotals and grand totals should you turn off?
Only keep the ones a reader would otherwise have to add up.
In tabular form with two or three row fields, a subtotal under every group doubles the number of rows and makes the real data harder to see. Turn off the subtotals for the inner fields and keep the one for the outermost. Click a row field, go to PivotTable Analyze, Field Settings, the Subtotals & Filters tab, and choose None under Subtotals. Microsoft's note is that selecting None turns subtotals off. This is a setting on the field, so it persists.
Grand totals work the same way: the Design tab has a Grand Totals button in the Layout group, with options for rows, columns, both or neither. Keep the grand total when the report is a list of parts that add to a whole. Turn it off for a pivot of averages or percentages, where a total of totals means nothing, and in a pivot you filter to one item, where the "grand" total is just the filtered number.
If you rename the grand total (type over the label), the new text persists on refresh. A label like "All departments" tells the reader what the number covers.
For a table that other people will review, this is the same advice as in the executive summary layout: put the number they came for where they will see it, and cut everything that makes them hunt.
What persists and what doesn't?
This is the whole post in one table. Where people usually apply a format is on the left; where it should go is on the right.
| Change | Where people usually apply it | Survives a refresh? | Apply it here instead |
|---|---|---|---|
| Number format | Select cells, Format Cells | Only as long as the cells stay put | Value Field Settings, Number Format |
| Column width | Drag the column edge | No, if autofit on update is on | Clear "Autofit column widths on update" |
| Fill colour on a total row | Select the row, fill | No, the row moves | Custom PivotTable style |
| Header borders | Format Cells, Border | No | Custom PivotTable style, Header Row element |
| Banding | Fill alternate rows by hand | No | Banded Rows in PivotTable Style Options |
| Rule-based colour | Conditional formatting by selection | Mostly, but scoped to those cells | Conditional formatting scoped by value field |
| Blank and error display | Overtype the cells | No | PivotTable Options, "For empty cells show" and "For error values show" |
| Column names | Overtype the cell | Yes, if the name is valid | Custom Name in Value Field Settings |
| Subtotals | Delete the rows | Not possible | Field Settings, Subtotals & Filters, None |
| Everything above | Any of the above | Discarded if "Preserve cell formatting on update" is off | Keep that box ticked |
The pattern is simple. If the format describes the data, put it on the field. If it describes the report, put it in a style or a PivotTable option. If it describes one cell, expect to redo it.
How do you check it before you send it?
Do the check the work is meant to pass: change the data and refresh.
- Add a row to the source that creates a new item, and one long label.
- Refresh the pivot (PivotTable Analyze, Data group, Refresh).
- Look for a width change, a lost number format, a colour on the wrong row, or a new value field showing General.
- Fix it at the setting that owns it, not on the cell, then refresh again.
Do this once on the template you reuse and you will rarely need to redo it. Menu names also differ a little between Excel for Windows, Mac and the web, and Microsoft's page has separate instructions for each, so use the version that matches your own copy.
A pivot is only as tidy as the data it summarises. Text that looks like numbers, trailing spaces that split one customer into two, and headers that change between exports all show up in the pivot as noise you cannot format away. Run the data tab through the spreadsheet cleaner first, then build the pivot. Microsoft's walkthrough for creating a PivotTable to analyze worksheet data is a good start if you have not built one before, and Excel Tables are the easiest source because they grow as you add rows.
For the dashboard that usually sits on top of a pivot, see Excel dashboard design.
Frequently asked questions
How do I keep pivot table column widths from changing?
Click inside the pivot, then PivotTable Analyze, Options, Layout & Format, and clear "Autofit column widths on update". Set your widths by dragging, and a refresh will leave them as they are.
Why does my pivot table lose its number format?
Usually because it was applied to selected cells, not to the field. Apply it through Value Field Settings, Number Format (or right-click a value, then Number Format) so it belongs to the field. Also check that "Preserve cell formatting on update" is ticked in PivotTable Options.
Can I use conditional formatting on a pivot table?
Yes. Microsoft says the format is maintained when you filter, collapse, expand or move fields, as long as the underlying fields are not removed. Scope the rule by value field, not by selection, so it follows the data when rows move.
How do I make a pivot table look like a normal table?
Switch to tabular form (Design, Report Layout, Show in Tabular Form), choose Repeat All Item Labels, turn off the expand and collapse buttons and the extra subtotals, and apply a plain custom style with a thin header line.