How to set the fill color of a pivot table

Key points of this article

If you want to make the subtotal rows stand out, use the pivot table style first. It follows the table and the color is usually retained after refreshing.
When there is only one level of classification and there are not many summary rows, you can directly select the subtotal cell to fill it in manually, but it is easy to lose it after the fields are changed.
When you need fixed color matching, you can create a new pivot table style and specify colors for the title row, subtotal row and total row respectively.
Before setting, make sure that the category fields have been placed in the row area, and each column of the original data has a header, otherwise the category summary row will not appear.
After refreshing or changing fields, check whether the color is still there item by item, especially the manually filled parts.

When making a pivot table, a common requirement is to make the "subtotal rows" and "title rows" visible at a glance, such as marking the subtotals of each region in light gray and marking the table header in dark blue. Many people will directly select cells to fill in colors, but as soon as they refresh or adjust the fields, the colors will no longer match.

This article is written for people who need to organize pivot tables in desktop office software such as Excel. You'll learn three things: how to determine which coloring method is suitable for your table, how to use a PivotTable style to uniformly color header rows and subtotal rows, and how to avoid color loss when manually filling. After reading, you can make a summary table that is clearly organized and will not be messy after refreshing.

One thing needs to be explained here: the color of a pivot table is not the same as the color of an ordinary cell. Ordinary cell filling only takes effect for a few fixed cells, while the summary rows of the pivot table will move as the data changes. Therefore, the key to deciding which method to use is to look at the structure of the watch and whether it will change in the future.

How to classify and summarize fill colors in pivot tables? One sentence conclusion

Prioritize using the PivotTable style to unify color matching. If necessary, customize the style or manually fill in the data. The table below gives suggestions based on your situation.

your situation	Recommended practices	reason
I just want to quickly distinguish the title row and summary row, and don't care about the specific color number.	Choose from built-in styles with header row and summary row emphasis	Fewer operations, colors automatically update with the table
Need to fix company colors or report templates	Create a new pivot table style and set the title row, subtotal row, and total row respectively.	The same template can be applied repeatedly to different pivot tables
There are only one or two subtotal rows, and the fields will not be changed later.	Manually fill selected subtotal cells	Simple and straightforward, but recheck after making changes
The data is refreshed every month, and the number of rows will increase or decrease.	Only use styles, no manual filling	Manual colors will stay in fixed cells and are easily misplaced.
Need to apply color rules to detail data	Use conditional formatting on raw data ranges	Conditional formatting rules are based on values or text and are more flexible than pivot table styles.

Before you start: Confirm these points first

Check the following items before doing it, otherwise it is possible that the color is set but the summary row cannot be found, or the color disappears on the second refresh.

Whether the original data is standardized. Each column must have a title, and there cannot be empty columns or merged cells in the middle. Otherwise, the pivot table will treat blank spaces as fields, and the classification structure will be confusing.
Whether the classification field is in the row area. Subtotal rows will be generated only if two or more fields are placed in the row area (such as "Region" and "Product"). When putting only one field, there is usually only a total row.
The display position of the summary row. Subtotals can appear at the top or bottom of the group. The position determines which row the color should be added to, so decide it first and then add the color.
Whether the fields will be refreshed or changed in the future. If you still want to update data, you should give priority to the style method; if you only make a one-time report, manual filling is also acceptable.
Whether it needs to be copied to other files. Colors may vary when converted between different software or formats. When you need to share it with others, try to save it in the same format and check it again.

Steps to color subtotal rows and header rows in Excel

The following steps take Excel as an example and describe common operations. The menu positions of different versions may be slightly different, but the core idea is the same.

Insert the PivotTable and drag in the category fields. Drag "Region" to the row area, and then drag "Product" to the bottom of the row area to form a two-layer structure.
Set the classification and summary display position. Select any cell in the row field, open the field settings, and select the desired display position in the subtotal-related options, such as displaying it at the bottom of the group.
Select any cell in the pivot table. At this time, the design tab related to the PivotTable will appear in the ribbon, which concentrates style, layout and options.
Open the PivotTable style library and select a built-in style. Select a style with obvious title row and summary row emphasis effects, and first check whether the overall effect meets your needs.
Check the Title Row and Bordered Row options. Check the title row in the style options, and check the border row if necessary to make a clear distinction between the title and the grouping.
When fixed color matching is required, create a new pivot table style. Select a new style in the style library and specify the fill color for the "entire pivot table", "title row", "subtotal row" and "total row" respectively. You can apply it repeatedly after saving.
When you only need to change individual cells, do manual filling. Select a cell of a subtotal row and set the fill color. This method is suitable for one-time reports that need to be rechecked after modification.
Save and refresh an exam. After saving the file, click Refresh Data to see if the colors are still on the correct rows.

Check each item after completion

The completion of color setting does not mean the end. The colors may change after refreshing the data, changing fields, or changing views, so check them one by one according to the table below.

Check items	How to see	What to do if there is a problem
Whether the subtotal row changes color	Check the last line of each group to see if the color falls on the subtotal line.	Check whether the display position of the classified summary has been changed, re-styling or manually correcting if necessary
Whether the title line is unified or not	Scroll to the header and make sure that all column headings are the same color	Check whether the title line option is unchecked, re-check and refresh
Whether the color is retained after refresh	Modify a piece of source data and refresh it to see if the color position changes	Manually filled cells need to be repainted, which can be avoided by using a style instead.
Is the total line prominent?	See the difference between the total row at the bottom and other rows	Adjust the total row color individually in the style settin
Print or export effect	Check after using print preview or export to PDF to make sure the color is still clear on the page.	When the color is too light, it deepens, and when it is too dark, use a light background bold font.

If you find that the color is misplaced, don't rush to modify it one by one. In most cases, the categorization location or the order of the fields has changed. Restoring the structure first and then checking the style is usually less time-consuming than manual patching.

# Core comparison

There are three common ways to color a PivotTable, which differ significantly in terms of applicable scenarios and stability.

Practice	Suitable for the scene	Whether to keep it after refresh	Main shortcoming
Built-in style	Daily reports, quickly distinguish levels	Usually reserved	The color matching is fixed, and you can't completely follow your own specifications.
Custom style	Team template, unified color matching is required	Usually reserved	It takes time to set it up.
Manually populate the cell	An one-time small table that only changes individual rows	Easily lost or misplaced	The number of rows should be adjusted line by line after the change

If your table will be updated frequently, it is recommended to use the built-in style as the bottom, and then change it to a custom style when you need a unified specification. Manual padding is only suitable for the final version that no longer changes.

# frequently asked questions (FAQ)

1. Why did I select the subtotal line and disappear after the fill color is refreshed?

Summary rows are regenerated when the PivotTable is refreshed, and cell-level formatting may be reset. Use the PivotTable style instead, and the color is generated along with the summary row, which is more stable than manual filling.

2. What if there is no classified summary line in the perspective table?

First check whether the row area has at least two fields. When there is only one field, there is usually no classified summary row. Then confirm that the categorization option is not turned off in the field settings.

3. Can you color the summary line in a conditional format?

Yes, but you need to set rules in the original data area or the value area of the PivotTable, and the rules should be written based on numeric or text conditions. It is suitable for high and low color according to the numerical value, but not by "is it a subtotal line", the latter is more direct with the style.

4. The color is set, why is the print lighter?

The color shades of the screen display and printout may be different, especially light gray. You can check in print preview first, deepen the fill color when the color is lighter or use bold font instead.

5. What if the color changes after copying to Word or PDF?

Different software may have slightly different interpretations of colors. It is recommended that after copying, check the target file one by one, and set the important color matching uniformly with the same color value, so as to avoid judging by the naked eye.

# Summary

Fill color to the classified summary row and title row of the PivotTable, give priority to the PivotTable style, create a new style when you need fixed color matching, and manual filling is only used for one-time small tables. After the setting is complete, remember to refresh the data once and make sure that the color still falls on the correct line.

Open OmniDoc and process your form file