2. Build Your First Pivot Table

Overview

Our first Pivot Table will compare the performance of our salespeople by state and provide some insights into how the business is run.

Summary

  1. Building the Pivot Table (00:04)

    To build a Pivot Table, we use the task pane to the right of the table. We drag fields into the various areas in the task pane. Values denotes the values that the Pivot table measures. Here, we’re measuring sales performance, so we put revenue in the values area. The Pivot Table then calculates the sum of all revenue in the data set.

    We then add fields to the row and column areas. These areas determine what fields are shown in each row and column of the Pivot Table. These fields should have values that are repeated across multiple transactions. We add salespeople as row labels, and add state as column labels, to create a Pivot Table showing total revenue for each sales person across each state.

    We can easily adjust the way data is presented in the table by dragging fields between the row labels and column labels areas.

  2. Insights From the Pivot Table (02:27)

    Examining revenue by salesperson and state lets us obtain a variety of insights. For example, two states, California and Texas, account for a large proportion of revenue. We also can see that one salesperson, Pritchett, underperforms relative to the other salespeople. 

    Finally, we can see that salespeople are not geographically restricted, because almost every salesperson generates some revenue in California. This suggests the sales process is not completed in person.

Transcript

In the previous lesson we converted our data set into an Excel table, and then use it to create a pivot table on a new sheet.

In this lesson, we'll make use of the pivot table to analyze 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 we're measuring sales performance, we're going to put revenue in the values column. So we'll select revenue and then drag it under values.

We can see that the pivot table is updated to include the sum of all revenue.

Now 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.

Let's examine a which states our individual salespeople have been focusing on. We'll add sales person to our row labels.

Again, we can see the pivot table has updated to include the total revenue for each salesperson.

Now let's add state to our column labels.

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 salespeople in columns and the states in individual rows, you can simply switch the labels. We'll move sales person into the column labels, and state into the row labels.

If we scroll back to the left, we can now see that the salespeople are across the top and the states on individual rows.

If you want to view sales people individually, you could grab the sales person, and bring it back to the row label above state. This allows us to view each salesperson in a single column.

For our purposes, having the sales person in the row, and bringing the state back into the column label is the easiest to read.

Examining the pivot table in this format, a couple of performance insights quickly emerge. First, 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 salespeople, who turns out to be Pritchett, is underperforming and lagging far behind the other salespeople. If this trend continues, they might need to be let go.

The pivot table also gives us some information about how the company operates.

Given that almost every salesperson has some revenue coming from California, it's clear that the salespeople are not geographically restricted.

This indicates 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 quickly draw useful conclusions from your data sets.

In the next lesson, we'll learn how to format the data within a pivot table to make it even easier to spot trends and reach conclusions.

Excel Excel for Business Analytics Learning Plan
Data Analysis
Pivot Tables

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial