Pivot Table Basics

1
Pivot Table Basics

• Presenter Julie Hoffmann 98, IR

2
Things we will cover today

• What is a Pivot Table?
• How do you make one?
• What can you do with a Pivot Table?
• How do you find duplicate records using Pivot
  Tables?
• What if my data changes?
• What if I get more data AFTER I build my Pivot
  Table?
• How do I know if my Pivot Table is right?

3
What is a Pivot Table?

• A cross tabulation, kicked up a notch.
• A way to present two or more types of data at a
  time.
• An extremely flexible reporting tool used with
  LIST data!!!
• Example Data from a Class at BU

4
How do I create a pivot table?Step 1 Get Data

• Have data in an Excel spreadsheet
• Use one header row.
• Tip Keep headers brief!
• Data must be in a format that can be used to do
  what you want it to do (the pivot table cant
  work with what isnt there).
• Tip figure out what questions you want to answer
  BEFORE you collect your data.
• The format must be CLEAN!!! No extra lines, no
  sub total or total rows, no blank empty rows
• Data should be SNUG. Get rid of fluff!

5
Example of Data Set That Wont Work
6
Example of Data Set That Will Work
7
How do I create a pivot table?Step 2 Put the
cell pointer somewhere IN your data, then go to
Data. Choose Pivot Table.
8
Step 3 Since your data is already in Excel, you
will leave the default selected Microsoft
Office Excel list or database. Most of the time,
you will want to create a Pivot Table (not a
Chart). Click Nextgt.
9
Step 4 Tell the Pivot Table wizard where your
data is. Click Next.
10
Step 5 Specify the data fields Column, Row,
and Page (note can have multiples). Then
specify the Data Item.
11
Step 5 (cont) Drag and drop the
followingCOLUMNGender ROWYear PAGE
FIELDResidenceSet the Data ItemID. Your PT
should look like this
12
You have just created your first Pivot Table!!!
However, we are not quite done yet

• What is wrong with our Pivot Table?

13
Step 6 Right click on the Data Field (in this
case, Sum of ID). Then choose Field Settings.
Since we are using ID as our identifier, choose
Count, then OK.
14
We now have a fully functional pivot table! The
data is meaningful and makes sense. From here,
you can do MANY things with a Pivot Table
15
What can I do with a pivot table?

• Count/tally data
• Calculate percentages
• Perform math functions add, subtract, multiply,
  divide, average
• Find duplicate records
• Identify records of interest
• Impress people!
• Save time!
• Set up reports that can be updated easily

16
We can see the of Total of our classclick on
Count of ID (our Data field), then right click to
make the Pivot Table Field Settings menu appear.
Click on Optionsgtgt. Choose of total.
17
We can rearrange our Pivot Table drag and drop
per the following COLUMNYear ROWGender. You
can also rearrange the contents of all fields by
clicking and dragging. Example move Sophomore
to be after Freshmen. Move Male above Female.
(note the crosshairs need to appear for you to
do this!)
18
We can limit the data to ONLY residents of Resco.
In the page field drop down (which in our case
is Residence), choose Resco. The data
automatically repopulates
19
We can elect to see ONLY residents of Ross OR
Resco. Double click on the Page Field (in this
case, Residence). Then, hold down the Control
key on your keyboard, and click Village and
Schwitzer to HIDE them. Click OK. Go to the
Residence drop down and choose ALL.
20
We can add MORE and DIFFERENT fields. In the
table, right click, and choose Show Field List.
Drag and Drop in t the following manner
COLUMNCar on Campus AND Gender ROWMajor
DATAParking Ticket Fees PAGEHome State set to
INDIANA. Change the Field Settings to Sum.
Does anything about this report seem strange?
21
I might want to know WHO has no car but has
parking tickets. To find out, just double click
IN the cell
22
Finding Duplicates. Lets make a NEW Pivot
Table. Click on an empty cell BELOW your Pivot
Table. Then go to Data, and choose Pivot Table.
Choose Another Pivot Table report as your data
to analyze. Click Next, then Next, then choose
Existing Worksheet A18.
23
Drag and Drop the following COLUMNGender
ROWID DATAID PAGEHome State. Change the
Field Settings to Count. Your pivot table should
look like this
24
Based on the count, we have two sets of
duplicates in our data. How can we see more
information about these four data entries? Go to
the Master Data set and change the IDs of 2 of
the 4 so that there are NO DUPLICATES.
25
Go back to your Pivot Tablehas the data changed?
You need to refresh the data using the icon, or
by right clicking in the Pivot Table.
Now has the data changed?
26
Two students blue card into this classso now
your data has more records.Go to the worksheet
labeled Blue Cards. Copy the two bottom rows,
and paste them into the bottom of your Master
Data Set worksheet. Then, return to your top
pivot table. Put your cursor in a pivot table
cell. Right click. Choose Pivot Table Wizard.
Choose Backgtgt. Reselect the data set, to include
the two rows most recently added.
27
The data set should look like thisthen click
Nextgt and Finish.
TIP If you your data set will change on a
regular basis, you can select a much wider cell
range so that all records will be included.
28
The data range has now been updated. Does your
top pivot table look different? Does the bottom?
TIP If you need a series of reports built, build
them off of one main report. This way, you can
refresh them all by refreshing one table.
TIP Under Table Options, you can set the Pivot
Tables to Refresh Upon Open, or to Refresh at a
designated time.
29
How do I know my Pivot Table is right?

• Check your data manually, using Sort and Filter.
• Run calculations by hand or with a calculator.
• Always pick a sample piece of data in your report
  and verify it against the raw data.

30
Groupings

• Make a pivot table using SAT score as the Row.
  Group all scores lt1100 together. Group all
  scores gt1100 together. Hide the detail.
• Display the average of the satisfaction field.

31
Follow-up Project

• Create a data set that includes at least 3
  qualitative fields and 3 quantitative fields
  (examples sales records for the Atherton
  Convenience Store, Greek House membership fees,
  rsvps for a fundraiser dinner). Enter the data
  in pivot table ready format.
• Create one master pivot table and base three
  other pivot tables off of the master. Make the
  tables managerially significant. Use a different
  pivot table field setting in each table. If you
  wish, use different fields for each table.
  Alternatively, use the same fields for each
  table, but display the data differently.
• Set the pivot tables to refresh upon open.
• Use one calculated field (which combines two or
  more fields).
• Use one calculated item (which combines multiple
  elements of one field).
• Write one sentence for each pivot table
  summarizing what information the table provides a
  manager.

32
Any questions???