BUA 345 - Lecture 4
Descriptive Analytics with Pivot Tables
2025-01-22
Descriptive Statistics
Describing and summarizing data quickly is an essential skill.
Excel has a powerful set of tools for this purpose, Pivot Tables.
To Access these tools:
Convert our 2024 Movie Data to an Excel Table (Insert > Table)
Click any cell within table, so that ‘Table Design’ tab is visible.
Click
Summarize with PivotTable.
Introduction to Pivot Tables
- When you click
Summarize with PivotTable, you will see this pop-up which should be populated as follows:
Specify `Existing Worksheet.
Specify
G1for the location.
If PivotTable Fields menu does not appear on the left side of your screen, right click within the empty Pivot Table and select ‘Show Field List’.
Answering Questions with Pivot Tables
Here a few examples of the many questions we can answer:
What was the average of of the
GROSS(ticket sales) for each day of the week?What was the minimum of the
GROSS(ticket sales) for each day of the week?What was the total gross in the last two months of the year?
Which day and month had the highest maximum GROSS?
Pivot Table Questions
What was the average of of the
GROSS(ticket sales) for each day of the week?Pivot tables allow you to drag and drop variables, and move data around efficiently
There’s no one right way to answer a question but here is one solution:
Drag
DAYto theRowsfield.Drag
GROSStoValuescolumnRight click on a data value in the table and change ‘Summarize Values By’ to
Average.
Filtering Pivot Tables
A useful quick tool for modifying a pivot table is a slicer.
For example, we want to create a table of the maximum gross for each month in the Fall.
Drag
MONTHto theRowsfield.Drag
GROSSto theValuesfield.Click on any cell in the pivot table so that the `PivotTable Analyze’ menu appears.
Filtering Pivot Tables Cont’d
Again we want to know the total GROSS for the last two months of 2024.
Click on
Insert Slicer, selectMONTHand clickOK.In the
MONTHslicer menu, selectNovandDeconly.
Slicing by Value
In this example, we want to find which day and month had the highest maximum GROSS?
Setup the Pivot Table as shown.
Click
Insert Slicerand selectGROSSand clickOK.Select the largest
GROSSvalue only.
Counts and Percents in Pivot Tables
In HW 2 you will examine 3 different sets of data about diamonds.
The next two examples are similar to Question 3 and Question 4 in HW 2.
The point of these examples is to show you how to
count the number of observations by categories.
convert counts to percentages.
make simple changes to answer different questions.
Counts and Percents Cont’d
We want to know how many diamonds in this dataset are classified as Colorless with Slight Inclusions.
Convert the Diamonds Data (Worksheet 2) to a table (Insert > Table)
Create a pivot table as shown
- Note that other text columns would also work in the
Valuesfield
- Note that other text columns would also work in the
Countshows the number of observations in each Color/Clarity combination.
Counts to Percents
After creating a table of counts, we can convert counts to other useful values to answer questions.
There are multiple ways to these same options.
One simple way:
Right-Click on any value within Pivot Table
Select ‘Show Values As’
Select option to answer your question
In the example table, we select % of Column Total to answer this question because of how it is oriented.
Question: What percent of Colorless Diamonds have Slight Inclusions? (Similar to Questions 3 and 4 in HW 2)
Changing Percents from Column to Row
We can easily convert percentages without changing the pivot table.
We want want to know what percent of diamonds with
Slight InclusionsareNear colorless.
Right-click on any value in the table.
In ‘Show Values As’ change
% of Column Totalto% of Row TotalExamine the table to confirm that it is correctly set up to answer our question.
- Note: In HW 2, you can make this same quick change in
Show Value Asto answer Question 4 after completing Question 3.