How to retain the format after refreshing and copying the PivotTable report

Key points of this article

Refreshing the pivot table will regenerate the cells according to the layout. Manually added borders and background colors may be cleared. Use the pivot table's own style to save the appearance.
The numerical format should be defined in the value field settings, so that it will still take effect after refreshing, which is more stable than changing the format directly on the cell.
Automatically adjust column widths during updates will reset the column widths you manually adjusted, making it easier to maintain the format after turning it off.
When copying and pasting only values, the formatting will disappear. You need to use Paste Special to retain the source formatting, or directly copy the entire worksheet.

When making reports, many people will spend half an hour adjusting the pivot table neatly: bolding the header, adding thousandths to the amount, adding good borders, and uniform background color. But once the data is refreshed or the table is copied to another file, the format is messed up, the borders disappear, the amounts return to decimals, and the column widths are reset. This type of problem is very common in Excel, and searches are often written as "The format does not change when refreshing the Pivot Table" or "The format does not change when copying and pasting the Pivot Table."

This article is written for people who often make summary tables and need to regularly update or distribute reports. After reading, you will be able to determine the cause of format loss, know which formats can be retained after refreshing, which ones will definitely be reset, and master the methods of copying and distribution. The article talks about general methods and does not involve additional functions of specific software.

One thing needs to be explained first: Pivot tables are different from ordinary tables. Its cells are dynamically generated by the field layout and will be recalculated according to the layout when refreshed. So "convert pivot table to normal table" will fix the appearance, but will lose the ability to continue refreshing. If you still need it to update with the data, you need to let the format follow the pivot table instead of attaching it directly to the cell.

How to keep the formatting after refreshing and copying? One sentence conclusion

First put the format in the pivot table's own settings (style, value field number format, update options), and then deal with the copy problem; it is easy to lose the method of manually changing cells.

your situation	Recommended practices	reason
The appearance needs to remain unchanged after each refresh	Use the pivot table style and check Preserve cell formatting on update in the pivot table options.	Styles are managed by pivot tables and will be re-applied when refreshed
Amounts and percentages need to be displayed in a fixed way	Define the number format in the value field settings	This format is bound to the field and will not disappear due to recalculation.
Only needs to be distributed once and will not be updated again	After copying, use Paste Special to retain the source format, or convert it to a numerical value and then beautify it.	After leaving the pivot table, the cell format can be saved freely
Need to move the entire report to another file	Copy the entire worksheet or recreate the pivot table in a new file	Copying the entire table preserves the pivot table and its settings
The layout is still being adjusted frequently	After the layout is finalized, beautify it	Manual formatting may be reset every time you drag a field, and beautifying it in advance is a waste of time.

First determine what kind of problem it is

The reasons for format loss are different, and the methods of revision are also different. First check the table below to confirm which category you belong to.

symptoms	Common causes	How to confirm
The border and background color disappear after refreshing	Manually added formats are not within the scope of pivot table management and are not retained when updated.	Click on a cell whose format has been cleared to see if it was added manually and not caused by the style.
After refreshing, the column width returns to default.	Automatically adjust column widths when updating is enabled	Check the layout and formatting options in the pivot table options to see if this option is checked.
After the amount is refreshed, the decimal place or thousandth place is missing.	The number format is added directly to the cell instead of being set in the value field	Click on the value field setting to see if the number format is "normal" or not set.
After copying it to other places, the colors and borders are all gone.	Only the numeric value is pasted, or the paste method is not with the source format.	Compare paste options to see if only values are selected
Format dislocation after layout change	Drag a field or expand and collapse to change the position of the cell, and the manual format does not match	Recall whether the row and column fields have been adjusted or grouped recently

# item-by-item troubleshooting and repair

According to the order of easy before difficult, most of the problems can be solved in the first two or three steps.

check the update settings in the PivotTable options. Open the PivotTable option, find the complex options related to layout and format, and make sure that "keep cell formatting on update" is checked. If it is turned off, the manual format will be cleared directly during refresh. Refresh it once after modification to see if the format is back before moving on to the next step.
automatically adjust the column width when you turn off the update. * there is usually "automatically adjust column width when updated" in the same set of options. When on, each refresh will recalculate the column width according to the content, and the column width you set manually will be overwritten. Turn it off and adjust the column width to the right position.
replace manual borders with PivotTable styles. * Select a style in the design-related functional area of the PivotTable, and check the desired row and column marking options. The style is uniformly managed by the PivotTable and will be reapplied after refreshing, which is much more stable than adding borders one by one. If you need a special border, give priority to a similar style, and then a small amount of fine-tuning.
format the number on the value field. * right-click a value in the numeric area, or open the value field settings, and set the decimal place, percentile and percentage in the number format. After this setting, the display of the same field will remain the same no matter how many times it is refreshed. If you change the format directly on the cell, it is likely to fail when refreshed.
confirm whether the manual format has been disrupted by layout changes. if you expand or collapse the grouping or drag the field, the position of the cell will change, and the original manually added format will not match. In this case, it is easier to fix the layout and then unify the format than to repair it one by one.
choose to retain the source format when copying. Direct pasting usually comes with only numerical values, and the color and border will be lost. Use selective paste instead, and choose to retain the source format or numeric plus source format in the options. The result is a formatted static table, but it has been separated from the PivotTable and can no longer be refreshed with the data.
copy the entire worksheet when you need to retain perspective. if the goal is to continue using this report in another file, it is more reliable to copy the entire worksheet directly than to copy only the PivotTable area. After replication, check to see if the data source still points to the correct data table, and then refresh the confirmation again.
only for distribution, converted to numerical values and beautified. if the report is only sent to others and no longer needs to be updated, you can convert the results of the PivotTable into values and beautify them with a normal cell format. In this way, the frame, background color and column width can be fixed. Note that after this step, the data will no longer be updated with the source table, and the data deadline should be indicated in the file name or header.

# how to avoid the next occurrence

- determine the layout first, then beautify it. do not spend a lot of time setting borders and background colors until the location and grouping of the fields are basically determined. - use style and field settings first. if you can achieve the effect in PivotTable style and value field number format, do not manually add it to the cell. - Save a template. Save the formatted PivotTable in a separate worksheet or file, and only replace the data source when you reuse it next time, without having to adjust it from scratch. - take a look before and after the update. write down the key column widths and number formats before refreshing the data; quickly compare them after refreshing, and adjust the options in time when you find changes. - check before copying it to others. before sending it, open it once with another computer or another file to make sure that the format and data are correct. - do not use merged cells as headings. the merged cells in the PivotTable are easily misplaced when the layout changes, and the title is more stable with headers and styles.

# frequently asked questions (FAQ)

1. How can the PivotTable format be set so that it will not be refreshed and cleared?

Give priority to the PivotTable style and check keep the cell format in the update options; the number format is defined in the value field settings. All three settings are managed by the PivotTable itself and are retained when refreshed. The border and background color added directly to the cell are easy to be lost when refreshing or layout changes.

2. The PivotTable copy and paste format remains the same, how should it be operated?

Select the option to keep the source format when pasting, and the format will be taken over, but the result is a static table that can no longer be refreshed with the data. If you need to retain perspective, copy the entire worksheet instead and check that the data source is correct in the new location.

3. What are the common practices for good-looking PivotTables?

The common practice is to choose a refreshing PivotTable style, the header and summary rows are distinguished by bold or background color, the values unify decimal and microdecimal places, and the column width is adjusted after the layout is finalized. The chart and title are placed on the outside of the PivotTable to avoid being crowded with the contents of the PivotTable.

4. The PivotTable report retains the cell format when it is updated, why does it still not work sometimes?

There are two common reasons: one is that the format is added to the cell, not in the settings of the PivotTable itself; the other is that layout changes (such as unfolding and dragging fields) make the cell position change and the format does not match. The problem can usually be solved by migrating the format to the style and value field settings and then fixing the layout.

5. The border disappears when the PivotTable tool is refreshed. Can you get it back?

It is usually impossible to recover from a format that has been cleared and can only be reset. You can first use PivotTable style to make up for the general appearance, and then fine-tune it manually. In the future, if you change the border class format to be part of the style, it will no longer disappear due to refresh.

# Summary

The format is lost after the PivotTable is refreshed or copied, mostly because the format is added to the cell, rather than managed by the PivotTable itself. Check the update options first, give the appearance to the style and value field settings, beautify the layout after finalization, and choose to retain the source format when copying, so that the report can be kept tidy after refresh and distribution.

Open OmniDoc and process your form file