Intro to Pivot Tables

First, let’s review the two basic spreadsheet functions we’ve used so far…

Sort (Data>Sort) is useful for organizing data. For example, organizing a column by A to Z, Smallest to Largest, Newest to Oldest, etc.

Filter (Data>Filter) is useful for narrowing down the data and hiding what you don’t want to see. For example, you can select just one or a few categories.

Now, we’ll add a third…

Pivot tables (Data>PivotTable) are useful for summarizing or extracting data by variables.

The key to making a pivot table is to understand:

  • what variables you want in your rows
  • what variables you want in your columns
  • what values you want to work with.

For example, open the Fake NJ Crime Stats spreadsheet

Say you want to summarize crime by County…

1. To create a pivot table

Click a cell somewhere in the data
Go to ‘Data’ in top menu bar

From the pulldown menu, select ‘Summarize with Pivot Table’
Select the range of data, new spreadsheet, and click ‘OK’

To summarizing data using a Pivot Table by a categorical variable:

Drag or click the first variable to ‘Rows’
Drag or click the another variable to ‘Values’
Drag or click another variable to ‘Columns’

For example, drag County into your ‘Rows’ field. Then drag Murder into your ‘Values’

Try another one.

Drag your labels out of the boxes to delete them.

Now drag County in ‘Rows’ field. Drag ‘Municipalities’ into ‘Values’

2. Making a pivot table with multiple variables

Try another one.

Drag your labels out of the boxes to delete them.

Drag County into your ‘Rows’ field.

Drag or click the second, third, fourth, etc. variables to ‘Values’
Pivot tables will also give the ‘Grand Total’

3. Making complex pivot tables

Summarizing is especially helpful when you are dealing with a lot of data.

For example, download and open Prisoner Data

Look at the data and see what we are dealing with.

Now create a series of pivot tables that summarize the data in useful ways.

For example:
Select ‘Data’ from menu bar
>Summarize with Pivot Tables
Drag ‘Offense’ into ‘Rows’
To create a count, drag ‘Offense’ into ‘Values’
Pivot tables add up by default. When it encounters text, it can only count the text

Now, use the Offense pivot table and add Sex to ‘Column Labels’

4. Pivot tables can also do quick formatting of cells

For example, to find the percentage of crime per gender

Go to ‘Count of Offense’ under ‘Values’ and click ‘i’
Click ‘Show data as…’
Click on ‘% of column’

Try something else.

Click on ‘% of row’

See the difference?

You can also find averages.

For example, to find the average number of years sentenced per crime

Take out ‘Count of Offense’ from ‘Values’ and add ‘Sentence years’
Click on ‘i’
Click on ‘Summarize by’
Click ‘Average’ and click ‘OK’

This entry was posted in Excel Tips, In-class Activity, Lecture. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s