PIVOT TABLE 101

WHY PIVOT TABLES?

Looking at a raw data set like the one here,

how would you answer the following?

1. Which state had the highest population in 2002?

2. In which year was overall US population the highest?

3. Which states saw a decline in student population rate

between 2003 and 2004?

What if you don’t even know what

you’re looking for?
 PIVOT TABLE 101

PivotTables allow you to easily organize, filter,

summarize, and analyze raw data

“Analyzing data without a Pivot is like hammering a nail with a noodle”

-Albert Einstein*

*Quote not confirmed

 KEY BENEFITS

1 POWERFUL
- Uncover insights and answer key questions about your data

2 BEAUTIFUL
- Apply custom styles and conditional formatting rules to bring your Pivots to life

3 FAST
- Create custom views, filters, and calculated fields on the fly

4 ACCURATE
- Automate calculations to minimize human error

5 FLEXIBLE
- Manipulate table layouts and create dynamic views in seconds
 DATA STRUCTURE

GOOD! BAD!

• Rectangular (variables as columns, observations as rows) • Transposed (variables as rows, observations as columns)
• No extra formatting • Unnecessary formatting
• Contains only dimensions & measures • Contains calculated fields
• Clear column headers • Confusing column header names
• No extra headers, footers, sub-totals or calculated fields • Extra header rows
 INSERTING A PIVOT TABLE

From the “Insert” menu, select PivotTable to create a blank

Pivot, or use the Recommended PivotTables option to
browse pre-populated starting points

What data are

you analyzing?

Where will the

PivotTable live?

(Insert  PivotTable) (Insert  Recommended PivotTables)

 THE FIELD LIST

Layout options allow you to adjust

the look and feel of the field list
The Field List shows all the
variables in your dataset, and
which ones are currently
included in the Pivot

Variables included in the Columns

If there are fields that you want field appear as individual columns
to use to filter the whole data within the Pivot
set, drag them to the Filters box

Numerical variables are almost always

included in the Values field
Variables included in the Rows
field will appear as individual (These are the quantitative measures that you
rows within the Pivot care about: sales, revenue, clicks, etc.)
 ANALYZE & DESIGN OPTIONS

The “Analyze” Tab:

The “Design” Tab:

 SELECTING, CLEARING & MOVING PIVOTS

Clear options allow you to

clear all fields and values
from a table, or just any filters
that have been applied Select options (allow you to select Move options allow you to
entire sections of the PivotTable relocate an existing PivotTable to
(or the entire table itself) a new worksheet or a new location
within the existing one
PRO TIP:
Select  Entire PivotTable, then copy
and paste to duplicate an entire Pivot
 REFRESHING & UPDATING PIVOTS

Refresh updates the PivotTable based

on changes made within the defined
source data range or table

PRO TIP:
Format your source data as a table to dynamically Change Data Source allows you
adjust as new columns or rows are added, or use
a column-only range reference (i.e. $A:$G)
refresh the Pivot to reflect changes
outside of the defined source range
or table (i.e. new columns or rows)
 HOW DO PIVOTS ACTUALLY WORK?

STEP 1: Detect/evaluate coordinates

• State = Arizona
• Measure = Total Population
• Filter = All ages

Excel isolates relevant source data

STEP 2: Apply arithmetic

• Summarize Values By: AVERAGE
• (vs. SUM, COUNT, MAX, MIN, etc.)
NOTE: You can double-click any
specific value in a Pivot to generate
STEP 3: Display result a new tab showing the exact source
data used to calculate it
• (586+859+870+1656+892)/5 = 973
PIVOT FORMATTING
NUMBER FORMATTING

Right-click a column header or any individual value within

a field to change the number format (number, currency,
percentage, date, etc.)

PRO TIP:
Right click, select PivotTable Options, and select
the “Layout & Format” tab to customize how you
want to display blank or error values
TABLE STYLES

Select from a range of styles

(right-click to make default), or
customize your own:
 TABLE LAYOUTS: COMPACT VS. OUTLINE

Compact Form (default): Outline Form (recommended):

VS.

• Nested fields/dimensions condensed into • Each field/dimension broken out into its own column, with
one column, with one filter option separate column headers and filter options
• Allows you to apply custom filters to each field (i.e. label
filters on the Product Category field and value filters on
the Product Sub-Category field)
 TABLE LAYOUTS: TABULAR FORM

Tabular Form (non-repeating): Tabular Form (repeating):

PRO TIP:
Use Outline Form when you are manipulating data
within a Pivot, and switch to Tabular form with repeating
labels (and no grand totals or subtotals) if you want to
create a new raw dataset
CONDITIONAL FORMATTING

Conditional Formatting rules

can be applied to PivotTables just
like normal data ranges
(Home  Conditional Formatting)

Options include:
• Text and Value-based Formats
• Data Bars
• Color Scales
• Icon Sets
• Formula-Based Rules
SORTING, FILTERING
& GROUPING
 SORTING & FILTERING

Hit this button (or right-click one of the values)

to drill into Sorting & Filtering options

More Sort Options: Label Filters: Value Filters:

Manual
Selections
 GROUPING DATA

Select values that you’d like to group Right-click and A new field is created (“Job Title2”)
(in this case fire-related job titles) select Group containing the new group (“Group1”)

Note: Both names can be customized

 SLICERS & TIMELINES

Insert Slicers or Timelines

PRO TIP:
Slicers and Timelines work just
like regular report filters, but with
user-friendly interfaces

A filter designed
specifically for dates

Basically a prettier
version of a filter!
 REPORT FILTER PAGES

(PivotTable Tools  Analyze)

Use the “Show Report Filter Pages”

option to create new tabs for each value
that a given filter (i.e. Year) can take

Year = 2011 Year = 2012 Year = 2013

CALCULATED VALUES & FIELDS
SUMMARIZE VALUES BY

Summarize Values By determines how

numbers should be treated when they are rolled
up or aggregated (sum, count, average, max, etc.)

PRO TIP:
Excel will default to “Count Of” if a data
column contains blanks or non-numerical
values. Typically you will want to change
this field setting to “Sum Of”
SHOW VALUES AS

Show Values As options allow you to

apply additional calculations to change
the way values are shown, such as the
Percent of a Total or Subtotal, Running
Value, Rank, etc.

In this case, we’re showing Order

Quantity values as % of Column Total,
rather than whole numbers
 SHOW VALUES AS - EXAMPLES

In this example we’re summarizing the same Revenue field 6 different ways:

Value % of Total % of Parent % Difference Running Total Rank

(no calculation) Column (genre) (prev. year) (by year) (Large  Small)
 SHOW VALUES AS - INDEX

The Index calculation uses an aggregated weighted average to reveal the impact of one number
within the context of a data set

Documentaries index very high in France, meaning that a global

increase in Documentary ticket prices would impact the
French film industry significantly more than any other country

Each Revenue number is converted to an Index representing

it’s importance within each column, using the following formula:

(Cell Value * Grand Total) / (Row Total * Column Total)

 CALCULATED FIELDS

Calculated Fields allow you to create new measures based on existing, numerical fields:

In this case I’ve added a measure called % Students,

equal to Student Population / Total Population

PRO TIP:
Don’t calculate rate metrics (i.e. CTR, CPC) in your raw data, use calculated fields in your
Pivot. This ensures that they calculate properly no matter how your data is rolled up
 CALCULATING USING COUNTS

Calculated fields are always based on the SUM of other fields (even if they are shown as a count,
max, average, etc.). But what if you want to make a calculation based on the COUNT of a field?

Ex) Create a field to calculate the

Likes per Post on each date STEP 1: Create a new “Count” STEP 2: Create a calculated
column (=1) in the source data field defined as Likes/Count
 CALCULATED ITEMS

Calculated Items allow you to create new dimensions or categories based on existing dimensions:

In this case I’ve added a new category called “Kids”,

which combines G and PG movie ratings

PRO TIP:
DON’T USE CALCULATED ITEMS UNLESS YOU NEED TO; you’re usually better off simply
grouping fields or adding new category columns within your source data itself
 SOLVE ORDER

If you’ve defined multiple calculated items, the Solve Order can be used to determine which
calculations to prioritize (value is determined by the last formula in the list)
 LIST FORMULAS

The List Formulas tool produces a new tab summarizing all calculated fields and items
associated with a given Pivot, along with the current solve order
PIVOT CHARTS
 PIVOT CHART 101

A PivotChart is simply a chart that is tied to a specific PivotTable; as you adjust filters and
fields in your Pivot, the PivotChart updates dynamically

1) Select your pivot and choose PivotChart from

either the “Insert” tab or the “Analyze” tab

2) Select a chart type 3) The PivotChart will be inserted, and dynamically tied to the pivot
(note: you can filter the view using either the pivot table or the chart itself)
 PIVOT CHART OPTIONS

The “Analyze” Tab:

The “Design” Tab:

The “Format” Tab:

 PIVOT CHART LAYOUTS & STYLES

Chart Layouts & Styles

allow you to adjust the look
and feel of a PivotChart,
including adding elements,
changing color palettes, or
applying pre-set templates
 PIVOT CHART FIELD BUTTONS

Select PivotChart Tools  Analyze  Field

Buttons to hide them from the chart (or right
click one of them from the chart itself)

Field Buttons allow you to apply or PRO TIP:

adjust filters directly within the chart You can format PivotCharts exactly like normal
Excel charts – the only difference is that
PivotCharts are dynamically tied to a PivotTable
 ADDING SLICERS

A Slicer is basically a “prettier” version of a PivotTable filter; it works exactly the same way by
filtering the data you see in your PivotTable and PivotCharts

1) Select a PivotTable and choose “Insert

Slicer” from the “PivotTable Tools” tab

2) Select the field(s) 3) The Slicer will be inserted next to your table, allowing you to
that you want to filter filter on specific values (or combinations, using the CTRL key)
 ADDING TIMELINES

A Timeline works just like a Slicer – it’s just formatted to work specifically with Date & Time fields

1) Select your pivot table and choose “Insert

Timeline” from the “PivotTable Tools” tab

2) Select the date/time 3) The Timeline is inserted, allowing you to filter on specific time frames
field(s) that you want to filter (Note: may need to adjust unit of time (month, year, etc.))