October 20, 2020

How to Format Pivot Tables in Google Sheets

Google Sheet spreadsheet
  1. Changing the format using Google Sheets’ Themes
  2. Changing the format without using Google Sheets’ Themes

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 the 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:

Original Pivot Table using Google Sheets’ Standard theme. Lists the Total Sales, Total Cost, and Profit from July 6 to July 13. Each day occupying a row. 
Original Pivot Table using Google Sheets’ Standard theme.

To change the theme of your spreadsheet, click Format in the main menu, and then click Theme on the top of its drop-down list.

Right now, the Theme 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.

 A sheet containing the Pivot Table. Uses Google Sheets’ Groovy theme, using shades of red to highlight the column labels, the row labels (the individual dates from July 6 to July 13), and the Grand Total. 
Pivot Table using Google Sheets' Groovy theme.

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.

Changing the format without using Google Sheets’ Themes

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. Simply select the cells you want to format before doing the actions. We list some of them:

  • Change the color shade of the cells: Click the Fill color button in the toolbar. A palette will appear. Select the desired color.
  • Change the font: Click the Font drop-down box in the toolbar. A drop-down list of fonts available will appear. Select the desired font.
  • 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.
Number formatting options.

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. 

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.