SCMS 3711

Business Analysis - Statistics

Chapter 3a

Pivot Tables – Introduction

(Chapter 3.2)

1
 Chapter 3
The textbook has a lot of information
about Data Visualization: it’s good
stuff, scan through it to gain
whatever is important to you

We will only focus on sections 3.2 and

3.3 that deal with Pivot Tables and
Pivot Charts

2
 What is a Pivot Table?
An analytical tool within Excel that can
quickly generate meaning from large,
detailed datasets

Used to summarize, analyze, explore and

present summary data

Microsoft Overview

3
Functions and Characteristics

Aggregate Interactive
Summarize Intuitive
Cross Tabulate “Slice and Dice”
Database Queries Groupings
Drill Down Descriptive Statistics

4
 Video Introduction

Please watch the first

introductory video that explains
the basics of Pivot Tables

5
 Source Table: Basics
Contiguous Columns
– Must have named headings
– All cells in a column technically can be empty
but typically are not

Contiguous Rows
– Must have some data in each row (no row
can be completely empty)

6
 Source Table: Excel
These videos will say to select the entire table
(Ctl+Shift+8) before "inserting the Pivot Table“
The current releases of Pivot Table no longer
require the entire table to be selected
– Click any cell within the table you want to create a
Pivot Table from; Excel will select all contiguous
columns and rows as the table to use

Source tables can get “out of sync” with the Pivot

Table’s Data Source, then use Ctl+Shift+8 to select
the entire table again
7
 Restaurant Data:
Inserting a Pivot Table
1. Click on any single cell inside the data set
or table
2. Click on Insert / Tables / Pivot Table
3. Create Pivot Table dialog box
– Excel defaults to the table from step 1,
or use an external data source
– Select where the Pivot Table should be placed
4. Click OK

8
Pivot Table Body

The area created to

hold the selected fields
by using the Field List

9
Field Section
Built from the labels
(Column Headings) in
the source data table

Field List

10
Area Section
Filters identify which (if
any) fields can be used
to filter data
– Fields cannot be in both
the Filters area and the
Values area

Columns identify the fields

used as column headings
– Columns must have
headings (labels)

11
Area Section
Rows identify the fields
used a row headings

Values identify the fields

being shown or
calculated for each
Column and Row
– Any field is possible
(does not have to be
numeric)

12
 Value Field Settings
Summarize
We are not limited to
showing only the
totals of numeric
fields
We have 11
summarization
methods to choose
from

13
 Value Field Settings:
Show Values As
We are also not
limited to how the
values should be
displayed
We can have Excel
calculate various
percentages,
differences, running
totals, or rankings

14
 Value Field Settings:
Alternative Access
We can also access
the Summarize and
Show Values
options by right
clicking on a cell
and selecting the
option desired

15
 Use Restaurant Data
Quality Rating showing Wait Times
­ Average Wait Times
­ Format to one decimal place
Quality Rating showing Meal Price
­ Average Meal Price
­ Format to dollars with two decimal
places

16
 Use Restaurant Data
Quality Rating showing both Wait Times
and Meal Price
­ Average Times and Price
Quality Rating showing Meal Price:
Minimum, Average, Maximum
­ Shorten the Headings

17
 Use Restaurant Data
Sort to: Good, Very Good, Excellent
Set Columns to Price
­ Group in $5 increments
­ Count of Restaurants

Identify 13 Excellent restaurants in

$40-$44 range

18
 Calculated Field
We can create a new column in the
source data and calculate some value
(profit, weight, whatever) based on
existing columns from our data

Or we can just create them inside the

Pivot Table itself

19
 Calculated Field
1. Click Analyze / Calculations / Fields, Items, Sets,
and select Calculated Field

20
 Calculated Field
2. Give the field (column) a name
3. Enter the Formula
(you can use the Field Names from your table)
4. Click Add

21
 Calculated Field
There are many options (that we don’t cover)
including:
• adding a calculated item (which is different
from a calculated field)
• having different formulas cell-by-cell
• a different order of calculation

see Create Formulas for details

22
 Pivot Table: Design
Report Layout
Compact: uses generic names
Outline: uses actual field names
No grid lines
Tabular: uses actual field names
Includes grid lines
Repeat All Item Labels

23
 Pivot Table: Design
Subtotals & Grand Totals
Subtotals can be:
­ on or off
­ Above or below the data

Grand Totals can be:

­ On or Off for rows
­ On or Off for columns

24
 Use Tomato Data
Sales by Rep and Tomato Type
­ Leo’s total sales = ?
­ Early Girl total sales = ?
­ Rachel’s sales of Sweet 100 = ?
Switch the Columns and Rows
Sort by sales of Mortgage Lifter

25
 Use Tomato Data
Sales by County / Rep and Tomato Type
­ Remove subtotals
­ Hide column grand totals
­ Show column grand totals and hide
row grand totals

26
 Use Tomato Data
(Fewer directions)

What is the highest grand total percentage

of sales by rep for Shelby county?
What percentage of the grand total sales
did Leo make to Marshall county?
What is the price per Lug of the second
most expensive tomato type?

27
 Use Tomato Data
(Fewer directions)

What sales rep had the greatest profit?

What sales reps had profits between $12
and $13 per Lug?
What county generates the most profit?
Generate a list of sales to Fayette County

28
 Self-Study

You are strongly encouraged to do

every step of each of the posted videos

Pivot Tables are powerful and easy, but

hard to teach in Power Point slides

29
 Summary

Fields and Areas

Field Settings and Headings

Sorting and Grouping

Drill Down

Report Layout: subtotals, grand totals,

repeated headings,

30
 Learning

What did I learn?

What do I not understand?
What applies to my career?

31
Questions?

32