Pivot Tables will allow you to both group and pivot data.
To build a Pivot Table in Google, highlight all the data you wish to pivot including the header columns (i.e. the column/field names). From here, click Data → Pivot Table.
Here's what it looks like when we pivot the Getting Started: Users dataset:
Once clicked, a modal will pop-up asking you where you want to put the pivot table – on a "New sheet" or and "Existing sheet". Where you put the pivot table is really a matter of personal preference, but we typically select "New sheet", and then give that sheet a pivot-related name. For instance, after pivoting the Getting Started: Users dataset to a new sheet we'd name it "Users, Pivot".
Using a Pivot Table to Pivot Data
A pivot table is made up of four elements:
Item | Description | Example (see screenshot below) |
Row | The field you want to see as rows | Place each "Country" value in a row |
Column | The field you want to see as columns | Place each "Acquisition Source" value as a column |
Value | The field you want to aggregate | Aggregate for the Count of Unique Users |
Filter | The data you want to exclude | Exclude any User that's deleted their account |
We use the Pivot table editor to specify the layout and what data to exclude. The screenshot below follows the "Example" described in the table above – how many unique users (Value), that haven't deleted their account (Filter), have signed up via each acquisition channel (Column) for each country (Row):
You'll notice there are a selection options inside each element of the Pivot Table Editor:
Item | Function | Description |
Row / Column | Order | How to order values: ascending or descending |
Row / Column | Sort by | Which field you want to sort by |
Row / Column |
Show totals | On/off toggle for showing totals |
Value | Summarize by | How to aggregate values (e.g. sum, average, count, ...) |
Value | Show as | How to show aggregate output (e.g. default, % of row, ...) |
Filter | Status | What and how to filter: by condition or values |
Using a Pivot Table to Group Data
Not all four elements are required to build a pivot table. For instance – how many unique users (Value) have signed up in each country (Row):
In the above screenshot we exemplify the use of a Pivot Table without actually pivoting any data (i.e we aren't turning any cell values into new fields).
Instead, we're using a Pivot Table to Group our data! This is why these two processes are together in one lesson.