Learn how to format pivot tables in Google Sheets with or without its Themes option. We'll take you through the font, fill, number formatting, and more to make your pivot table look presentable for the end user.
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
Click Format in the file menu
Click Theme at the top of the drop-down list.
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.
Select the cells of data you want to format
Choose your desired cell fill color from the color palette in the toolbar
Select a font from the Font drop-down box in the toolbar
Change the number format from the number toolbar.
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.