Pivot Table Formatting in Google Sheets (3 Easy Ways)

Pivot tables are perhaps one of the most important data presentation tools that a spreadsheet application can have. The focus word here is presentation.

A default pivot table can have bland features that do not convey the data clearly most of the time.

That’s where formatting becomes crucial. And in this article, we will look at a few ways we can perform pivot table formatting in Google Sheets.

Let’s get started.

Formatting a Pivot Table in Google Sheets

First things first, let’s create a pivot table in Google Sheets. Here we have a sample dataset of sales data according to month and region:

example dataset - pivot table formatting in google sheets

We will create a pivot table that focuses on presenting the number of sales in each month by product.

Step 1: Select the whole dataset and navigate to the Insert tab to find the Pivot table option.

Insert > Pivot Table

navigating to pivot table option of the insert tab

Step 2: Set the following conditions in the Pivot Table Editor as seen in the image below:

conditions to generate a pivot table in the pivot table editor

And we are done!

the generated pivot table

This is the default look of any pivot table in Google Sheets. While it conveys the message just fine, it is not quite presentable.

To make it so, we will use some formatting techniques that are already available in the application and are quite easy to use.

1. Using Predefined Themes to Format a Pivot Table in Google Sheets

The easiest way to bring life to your pivot table is to use themes. Thankfully, Google Sheets already provides us with a set of predefined themes right in the application.

We can easily find the option in the Format tab.

Format > Theme

navigating to pivot table theme from the format table in google sheet

This will open the Themes pane on the right-hand side of the window.

the themes pane of google sheets

As you can see, there are a plethora of options to choose from. Select the one that closely matches the data or your presentation.

a lot of themes to choose from for pivot table formatting in google sheets animated

Customize Themes

While the number and variety of the themes available are respectable, Google Sheets further allows us to customize them to format the pivot table in any way we want.

You will find the Customize button on the Themes pane. After settling on a general theme, click on the button to get started.

customize the theme

Clicking on the button will open a general customization panel. Here you can set your pivot table to have any fonts or any color that you desire for the different aspects of a dataset presented here.

customization option for themes in google sheets

Click Done to finish up.

2. Format a Pivot Table Manually

Fundamentally, a pivot table is simply a generated table from a source dataset in Google Sheets. This means that any customizations you can do in a regular worksheet can also be performed on a pivot table.

For example, let’s say we want to…

  • Apply a border to the table
  • Bold the column headers
  • Center-align the column headers and data

Of the following pivot table in Google Sheets:

theme formatted pivot table in google sheets

We will take the same steps for this formatting as we would for a regular worksheet.

  1. Applying a border: Select the table and click on the Border icon on the ribbon.

applying borders to the pivot table

  1. Bold text: Select the text, in this case, the column headers, and click on the Bold icon on the Ribbon. You can also use the keyboard shortcut CTRL+B.

bold text formatting

  1. Center-align the data and headers: Select the data and header cells and click on the Alignment button to open the tray and select Center.

aligning text center in a pivot table

These are just a few examples of manual formatting. There’s so much more available in Google Sheets.

3. Conditional Formatting a Pivot Table in Google Sheets

Same as manual formatting, we can also apply conditional formatting to a pivot table in Google Sheets.

For this example, let’s highlight the Grand Total of Sales for each month. Sales above 250 units get one highlight and sales under 100 units another highlight.

Step 1: Select the cell to highlight.

Step 2: Navigate to Conditional formatting from the Format tab.

Format > Conditional formatting

navigating to conditional formatting from the format tab in google sheets

Step 3: Apply the first conditional formatting to values above 250.

first conditional formatting conditions

Click Add another rule for the second conditional format.

Step 4: Apply the second conditional formatting to values under 100.

second conditional formatting

Step 5: Click Done to apply.

conditionally formatting a pivot table in google sheets

Learn More: How to Use Conditional Formatting in Google Sheets

Final Words

That concludes all the ways you can format a pivot table in Google Sheets. Themes are specially made to be applied to these tables in the application, they can also be customized according to the users’ requirements.

Other than themes, a user can apply any type of formatting that is already available in Google Sheets on a pivot table. That includes conditional formatting.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply