Excel - Pivot Tables - Part One

Printer-friendly versionPrinter-friendly versionEmailEmail

Crash course on Pivot Tables - Party One

We've split this lesson into two parts: Part One will cover how to quickly create a pivot table while Part Two goes into a bit more detail.

Unable to display content. Adobe Flash is required.

Welcome to T4T’s tip #19. Today we’re working in Excel, specifically with Pivot Tables. Pivot Tables give you the ability to summarize a large amount of information very quickly. In today’s video clips we’ll actually be splitting them into two parts. In Part One we’ll show you how to get a basic pivot table set up and some of the key elements. The second part will go into a bit more detail.

The good news is that pivot tables are relatively easy to use. It might take a bit of trial and error in terms of getting it to display correctly, but luckily that trial and error is easy to do.

But first, let me give you an overview of the data set we’ll be using. It consists of 4,000 voter registration records. There are eight columns of data: a unique voter ID, party affiliation, precinct, age group, last year voted, years registered and ballot status.

We’ll use a pivot table to make some sense of this data. Over here on the scenarios tab, I’ve set up a few sample questions for us to answer. The first question asks us how many ballots need to be printed for each precinct.
To set up a pivot table, just click anywhere within the data, head up to the Insert Tab and click on the Pivot Table button. A Pivot Table Window pops up asking us two questions about the location of your data and where you’d like the pivot table inserted. Let’s stick with the defaults. As soon as I click OK, you’ll see that I’ve added a new sheet called Sheet4 which will be our new pivot table. A quick tour: Our pivot table will be created over on the left and our available fields to use are located in the list to the right.

To remind you of our first scenario, we need to make sure we have enough ballots by precinct, so totals by precinct from our data. Over on our pivot table, we can grab ‘Precinct’ and drag it down to the Row Labels. This is where a lot of the trial and error comes into play. We could also drag ‘Precinct’ down to Column Labels to create a more horizontal pivot table. It’s really up to your preference; I’ll stick with a vertical set-up and keep ‘Precinct’ in the Row Labels box.

The next thing we need to do is get some sort of totals for each of those precincts. So let’s grab ‘Voter’ and drag it into the Values box. But we’ve got an issue. Excel thinks it’s helping us by adding all the voter IDs together. Unfortunately, we don’t want a sum of voter IDs, we want an actual count of voter IDs. It’s easy to fix. Just click on the ‘SUM of Voter’ field and select Value Field Settings. Change SUM to COUNT. And there we have something useful.
So in about 30 seconds of work, we’ve taken our 4,000 records and quickly summarized them into voter totals by precinct.

We’ll stop Part One right here and go into more detail in Part Two. See you over there.