2. Build Your First Pivot Table
Our first Pivot Table will compare the performance of our salespeople by state and provide some insights into how the business is run.
Building Pivot Tables
- Simply drag fields into the Column Labels, Row Labels and Values areas
- Values determine what data will be included in the body of Pivot Table
Column and Row Labels
- Only fields with repeating values (e.g. State, City) should be added as Labels
- Row Labels determine the metric for each row of data
- Column Labels determine the metric for each column of data
In the previous lesson, we converted our data set into an Excel table and then created a Pivot table on a new sheet from this data set.
Now let’s use this Pivot table to find out some information about the performance of our Company.
To do this we use the task pane on the right hand side.
Let’s start by deciding on the values we want to include in the Pivot table.
Given that I’m measuring sales performance, I’m going to put Revenue in the Value’s column.
So I’ll select Revenue, and then drag it under Values.
And you can see that the Pivot table has updated to include the sum of all Revenue.
Next, let’s add fields to the row and column labels.
To get insights from a Pivot table, the fields that are added to the column and row labels must have values that are repeated across multiple transactions.
In our field list, Payment date, Sales person, and potentially City and State, satisfy this criteria.
In this our first Pivot table, let’s examine which states our individual sales people have been focusing on.
So we’ll add Sales People to our role labels.
And again you can see that the Pivot table has updated to include the total revenue for each sales person.
Now let’s add State to each column label.
And the Pivot table dynamically updates to show us the total revenue for each sales person across every state.
It also shows us the total revenue for each state and the total revenue for each sales person.
If you think it would be easier to view the sales people in columns, and the states in individual rows, you can simply switch the labels.
So I'll move Sales Person into the column labels, and State into the row labels.
If I scroll back to the left, you can now see that the sales people are across the top, and the states are in individual rows.
If you wanted to view sales people individually, we could grab the sales person and bring it back to the row label above the state.
And this allows us to view each sales person in a single column.
For my purposes, having the sales person in the row and bringing the state back to the column label, is the easiest to read.
Examining the Pivot table in this format, a couple of performance insights quickly emerge.
Firstly, we can see that California and Texas contribute a huge portion of total revenue.
In fact, these two states alone contribute about a third of our total annual revenue.
The Company might want to explore further as to why this is the case.
It could be that a particular industry in these states loves the product, or perhaps company referrals from a couple of customers in these regions have been very lucrative.
The second insight is that one of our sales people, which turns out to be Pritchard, is under performing and lagging far behind the other sales people.
If this trend continues, he might need to be let go.
The Pivot table also gives us some information about how the Company operates.
Given that almost every sales person has some revenue coming from California, it’s clear that the sales people are not geographically restricted.
This leads me to believe that the sales process is normally completed over the phone, otherwise the Company would be wasting a huge amount of money in travel expenses.
Hopefully this lesson has shown you that even a simple Pivot table, with one row label, one column label and one set of values, can help you to quickly draw useful conclusions from your data sets.
In the next lesson, I’ll show you how to format the data within a Pivot table, to make it even easier to spot trends and reach conclusions.