Many people struggle to use Pivot Tables in Excel. This is a quick, 90 second tutorial on the basics of Pivot tables. It includes an example Excel file that you can download. One with just the data, and one with a pivot table set up.

Understanding Your Data

To create a pivot table, you need standardized data. That is, each column is a category, and each row belongs to some person, object, or ‘record.’

Bad Table Structure

This example shows a bad table structure. The sales regions are set up as individual columns with sales amounts spanning multiple rows and columns. You cannot pivot table this data.

 

 

 

 

Good Table Structure

A good table structure uses columns as categories. In the example there are three categories of information: Sales Agent, Sales Region, and Revenue.

Notice that the Sales Agent’s name gets repeated multiple times. This is ok. Also, row order doesn’t matter. What does matter is that like-for-like data is all in the same column.

 

 

Create a Pivot Table

Click in the table (any cell) then click Insert -> Pivot Table

Ensure the entire table gets highlighted, then click Ok.

Understanding Pivot Table Fields

The next item to understand is the Pivot Table Fields pane.

1.

2. Rows
Optional but usually required to draw any insights. Drag one field from the list into this box. In our example, we’ll use Sales Agent.

3. Columns
Optional. You would use this to segment the data into categories and should use only one field. For our example, we are using by Sales Region.

4.Values
Mandatory. Drag the field you want to analyze (usually contains answers to questions, or numeric values). In our example we will use Revenue.

5. Filters
Optional. Use this if you want to turn off certain options when looking at data. Fields used here cannot be used as a Row or Column in the pivot.

Answer a Single Question

The real trick to a pivot table is that is can be used to answer a single question at a time. For the example, the question I have is, “What was each agents’ total sales?”

With a pivot table I can quickly answer that. I need the Sales Agent field in the Row slot, then I need Revenue in the values slot.

 

What if we wanted the total sales by region instead of agent? Just swap the row value from Sales Agent to Sales Region (you can drag or uncheck/check to make the change).


Downloadable Example

This is the Example file, and it includes both the Good and Bad table examples, as well as three completed pivot tables you can play with (click into the tables to bring up the field pane)

 

Download Example Excel File

 

Conclusion

While this doesn’t begin to cover all the capabilities of Pivot Tables, it hopefully gives you enough to understand what you are seeing and an example that you can wrap your head around.

 

 

 

Contact us to learn more!

SHANE SLOAN

Shane is our Vice President of Products and Innovation our managed service line of business as well as our enterprise Intune migration program.  Shane is a graduate of Vanderbilt University’s Owen Graduate School of Management with a Master’s in Business Administration (MBA).  Prior to joining Mobile Mentor, Shane spent 9 years at AIG’s Life Insurance division focused on development, automation engineering and quality assurance leadership. In 2007, Shane obtained a Bachelor’s of Science in Information Technology from Colorado Technical University. Prior to undergrad, Shane had a distinguished career with the US Army as a Blackhawk helicopter crew chief.