Making Your First Pivot Table

Dave Bruns's picture
ShareThis

You’ve probably heard a lot about Excel pivot tables. In fact, people who are really into pivot tables tend to go on and on about how cool they are. It can be a little annoying. What’s the big deal? Why are pivot tables so cool?

The main reason pivot tables are cool is that they instantly summarize large sets of data without any formulas. In less than 5 minutes, you can have a good-looking table, with perfect summaries by almost any category you like.

Even better, pivot tables give you a valuable skill. In a world overflowing with data, people who can use pivot tables efficiently are more likely to get noticed and rewarded.

But to really understand why pivot tables are cool, you need to build one, and spend a few minutes playing with it. That’s exactly what we’re going to do in this article.

Useful pivot table resources:

The Data
You can download the data we’re using in this worksheet attached to this blog post. It’s a list of 300 employees in different departments. To make things more interesting, and give you something to sum, there is also a column for donation. This represents a pledge for a fundraiser event.

Prep The Data
In order to avoid problems down the road, make sure you have a clean set of data. If the data contains any subtotals, remove them. Also delete any blank rows or columns. Your goal is a single block of data with all rows and columns touching. Finally, make sure each column has a unique, descriptive name at the top, since columns will appear as fields in the pivot table.  In this case, the sample data is ready to go; there’s no need to clean anything up.

Create The Pivot Table
To
create a pivot table, first select any cell in the source data. Then, on the Insert tab of the ribbon, click the PivotTable button and accept the default settings. Done! You now have a pivot table on a new worksheet.

Start With A Count

Start With A Count
A good first step with any pivot table is to run a simple count on the data. This will let you know that the pivot table is working correctly. To count the data, drag any text field into the Values area of the Pivot Table window. You want to use a field with data that appears in every row. In our case, the First name field will do nicely.

The pivot table tells us that the count of first names is 300. Perfect. That matches the number of employees in the list and tells us that the pivot table is working.

Summarize
Now
for the good stuff. The total count is nice, but not very useful. The power of pivot tables comes from their ability to slice and dice, or “pivot” the data in different ways. To see how this works, drag the Department field from the field list into the Row Labels area. The pivot table will break down employees by department. 

Now drag Gender to the Column Labels area. The pivot table will display a count of employees by department and gender.

Let’s give the pivot table a field to sum.  We’ll add Donations and summarize by Department.

First, drag both the Gender and First fields out of the table, and leave Department as a Row Label. Now drag the Donations field into the values area. Presto! We now have donations by Department. 

Format Values
This is looking pretty good, but it would be nice to have Donations formatted as currency. A pivot table can do this for you automatically.  First, right-click on any value in the Donation column and select Value Field Settings. 

Then click the Number Format button, and set the format to Currency.

Now donations are displayed as a dollar amount. What’s nice about this technique is that the pivot table will automatically format all values in the field using your formatting, no matter how big the pivot table becomes.

Tidy Things Up
Finally, let’s tidy things up. I like to give Pivot Tables a little breathing room by inserting a blank column to the left, and an empty row above the pivot table. Then I add a title, and turn off gridlines. This gives your pivot table a little professional polish.

So there you have it, a useful pivot table in less than 5 minutes!

 


About the Author: Dave Bruns is co-founder of exceljet.net, a resource to help people work in Excel faster. If you use Excel frequently, download his free PDF "220 Excel shortcuts for Mac and PC, side-by-side"

 

AttachmentSize
employee_data.xlsx22.2 KB
shadow