[Looker Studio] Ultimate Guide to Using Pivot table

Pivot tables allow you to visualize metrics by specifying dimensions in rows and columns. They are suitable for viewing numbers by combining different dimensions. In this article, we will explain in detail how to use pivot tables, including images. By mastering the finer settings, you can expand what can be expressed with pivot tables, so please take a look.

For information on how to add graphs and tables to Looker Studio reports and basic configuration methods, please refer to the following article:

□ Official Help: Pivot table reference

What is the Pivot table?

In this component, you can specify dimensions in rows and columns to visualize metrics. It is suitable for analyzing figures when multiple dimensions are combined, rather than just one. In addition to displaying numbers, it can also represent them as heat maps or bar charts.

 (Quote:Looker Studio)

Property Panel Settings

The configuration of pivot tables is mainly done in the property panel. The property panel has settings and style menus. We will first explain the settings menu related to numerical configuration.

 (Quote:Looker Studio)

Data source

In the data source section, you can select and edit the data sources used in the pivot table.

By selecting the data source name, you can switch to other data sources. By choosing the edit icon to the left of the data source name, you can edit that data source. Calculation fields can also be edited from here. 

By selecting "Integrate Data," you can edit integrated data sources that allow you to display data from multiple data sources in a single graph. 

 (Quote:Looker Studio)

Date Range Dimension

If you select a data source other than GA4 or Google Ads, the setting for the date dimension will be displayed. You can choose which date to use as the basis for the period of the displayed data. For example, if the data includes two dates, such as booking date and usage date, and you set the booking date as the date dimension, the displayed data will be based on the booking date for the specified period.

(Quote:Looker Studio)

Row dimension

A dimension refers to a category of data. By setting the row dimension, you can display data divided by the specified categories.

 (Quote:Looker Studio)

Expand-collapse

You can set multiple dimensions for the rows. Additionally, by checking "Expand/Collapse," you can toggle the display of the hierarchy of row dimensions.

 (Quote:Looker Studio)

Column dimension

You can further split the dimension specified in the row by the dimension specified in the column to view the numbers.

 (Quote:Looker Studio)

Metric

Select the metrics to display in the pivot table. The values for the selected metrics will be displayed. Multiple metrics can be selected.

 (Quote:Looker Studio)

Optional metrics

 By setting optional metrics, you can switch the metrics displayed in the report. This allows you to display multiple metrics. Enable optional metrics and add the metrics you want to display.

 (Quote:Looker Studio)

Once you configure the settings, an icon will appear at the top right corner of the pivot table. By clicking on it, you will be able to select the metrics you want to display.

Totals

You can display the total values for both rows and columns in a pivot table. By checking the option to display the grand total, the totals will be shown.

(Quote:Looker Studio)

Sorting

You can specify the sorting of rows and columns. Additionally, you can specify how many rows and columns to display after sorting. In the image below, it is set to display up to 4 rows and 2 columns of dimensions.

(Quote:Looker Studio)

Default date range

Here, you can set the period for the indicators displayed in the pivot table. For detailed instructions on how to configure the period, please refer to the following article.

[Looker Studio] Guide to Setting Report Date Range 

 (Quote:Looker Studio)

Filter

By using filters, you can narrow down the displayed data based on specific conditions. Please refer to the following article for instructions on how to use filters. In this case, we are narrowing down to only the numbers for a particular audience.

[Looker Studio]How to use filter

(Quote:Looker Studio)

Property Panel Style

Next, I will explain the styles of the property panel involved in the design of the pivot table.

 (Quote:Looker Studio) 

Chart Title

You can display a title on the pivot table.

 (Quote:Looker Studio)

1. Enter the title.
2. Format the title.
3. Set the display position of the title.

Conditional formatting

You can change the background and text color of the pivot table based on specified conditions, similar to Excel or spreadsheets.

 (Quote:Looker Studio)

Table Header

You can change the text color, size, and font of the table headers. Here, the text color has been changed to blue.

(Quote:Looker Studio)

Table Color

You can specify the background color of the header, the color of cell borders, the highlight color when focused, the color of odd rows, and the color of even rows.

(Quote:Looker Studio)

Table Labels

You can change the text color, size, and font of the dimension and metric values displayed in the pivot table.

 (Quote:Looker Studio)

Missing Data

You can choose how to display when there is no data for the metrics. The options include No Data, 0, -, null, and blank. The default is set to '-', but here it has been changed to display 'No Data'

 (Quote:Looker Studio)

Metrics

You can edit the display settings for each metric. These edits apply to individual metrics, allowing you to change the display format for each metric separately.

 (Quote:Looker Studio)

 You can choose from three display formats: numeric, heat map, and bar graph. If you check the option for abbreviated numeric display, the units of the numbers will be in thousands. Additionally, you can select the number of decimal places for numeric precision.

Background and Border

 You can edit the formatting and design of backgrounds and borders. The configurable items are as follows:

• Background color
• Border radius (corner roundness of the border)
• Transparency
• Border color
• Border thickness
• Border style
• Border shadow

 (Quote:Looker Studio)

 

Chart Header

When setting optional metrics, you can edit the display conditions and colors of the icons that switch the metrics. There are three display conditions as follows. When setting optional metrics, choose either to always display or display on hover.

• Do not display
• Always display
• Display on hover

 (Quote:Looker Studio)

Constraints of Pivot Table

Pivot tables have several limitations. Please use them within the following constraints:

• Maximum number of cells: 500,000
• Maximum number of pivot tables per page: 5
• Maximum number of row dimensions: 5 (up to 10 for BigQuery)
• Maximum number of column dimensions: 2
• Maximum number of metrics: 20
• Metric filters are not supported