How to Format Pivot Tables in Google Sheets

The Pivot Table is Google Sheets’ answer to Microsoft Excel’s PivotTable feature. This feature allows the user to quickly summarize a large amount of structured data through few clicks, giving the user a powerful tool for free. Pivot Table can be a bit more challenging to tame, but this tutorial will help you master it by considering the common demands of data analysis. 

If you find the default format of the Pivot Table quite boring, or you will eventually use it in a document with a predefined formatting, you can easily apply the existing format options to the Pivot Table in Google Sheets. 

Changing Pivot Table Format using Google Sheets Themes

If you are not yet aware, Google Sheets now has the Themes feature, giving you more flexibility to improve the look of your spreadsheet. This feature also affects the look of your Pivot Table. For reference, this is how the Standard theme looks like, applied to the Pivot Table:

To change the theme of your pivot table in google sheets

1. Click Format in the file menu
2. Click Theme at the top of the drop-down list.
3. Choose a theme and color palette for your pivot table

Right now, the Theme feature is labeled with the box New, indicating that it is a recently-added feature in Google Sheets.

After clicking the Theme option, a sidebar will appear on the right side with the label Themes. It contains a list of predefined themes you can use, alongside a preview of its color palette. Select one that you'd like to use.

One advantage of using predefined or customized themes is that Google Sheets adjusts the colored cells if there are changes to the number of cells occupied by the Pivot Table. The disadvantage, however, is that when you copy the contents of the Pivot Table to another sheet, you cannot copy the color shading even if you either click Paste format only after right-clicking on the target cells or click the Paint format button in the main toolbar. 

If you either don’t like the predefined themes or you want to streamline the colors with the existing theme you use for your documents, you can click Customize on the Themes sidebar.

‍

Google Sheets Pivot Table Formatting (Classic Way)

If you don’t want to use predefined themes nor customize a theme, you can always manually format the individual Pivot Tables. There are no extra steps you need to do before formatting them.

1. Select the cells of data you want to format
2. Choose your desired cell fill color from the color palette in the toolbar
3. Select a font from the Font drop-down box in the toolbar
4. Change the number format from the number toolbar.
5. Add a border and Center Text

To Change the number format: the common options for the number format are listed before the Font drop-down box (pictured below). From left to right, the listed formats are Currency, Percentage, Decrease decimal places, Increase decimal places, and More formats. You can select this instead of accessing the number formats from the Format option in the main menu.

The advantage of manually formatting the Pivot Table is that you can copy both the values and the format you have placed to another cell or sheet.

The disadvantage of formatting the Pivot Table manually is that you have to redo some formatting once additional column(s) or row(s) are added, as they do not adjust to changes in the size of the Pivot Table but are tied to the specific cell you formatted.