BUA 345 - Lecture 4

Descriptive Analytics with Pivot Tables

Housekeeping

HW 2 is due Monday, 1/26/26

Demo videos for HW 2 are posted

Today’s plan

• Descriptive Analytics
  • Pivot tables
  • Summarizing data in Tables
  • Summarizing data ‘On the Fly’ with Slicers
• HW 2 Demo
• R and R/Studio Resources for BUA 345

Lecture 4 In-class Exercise - Q1

Poll Everywhere - My User Name: penelopepoolereisenbies685

• Download the Lecture 4 Review Question Data.xlsx dataset to your BUA 345 folder and open it.

• Convert these data to an Excel table.

• Remove Duplicates using all columns except OBS NO..

• Sort data by Price from ‘Largest to Smallest’

• Use the first indicator icon set to classify PRICE in Conditional Formatting.

• What is the lowest price in the ‘High’ (green checkmark) category?

  • Round answer to closest thousand to select correct choice.

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:

  1. Convert our 2025 Movie Data to an Excel Table (Insert > Table)

  2. Click any cell within table, so that ‘Table Design’ tab is visible.

  3. 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:

1. Specify `Existing Worksheet.

2. Specify G1 for 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:

1. Drag DAY to the Rows field.

2. Drag GROSS to Values column

3. Right click on a data value in the table and change ‘Summarize Values By’ to Average.

Lecture 4 In-class Exercise - Q2

What was the average gross movie sales in the United States on Fridays in 2025?

Round answer to closest million.

The average gross movie sales on Fridays in the United States was ____ million.

Lecture 4 In-class Exercise - Q3

Using the framework for the summary in Question 2, we can easily find the minimum or maximum for each weekday as well.

Which day had the lowest minimum gross sales in the United States in 2025?

When you are done with this question, clear your pivot table fields by unchecking the boxes in the field list.

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 total gross for the last two months on 2025.

1. Drag MONTH to the Rows field.

2. Drag GROSS to the Values field.

3. 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 2025.

3. Click on Insert Slicer, select MONTH and click OK.

4. In the MONTH slicer menu, select Nov and Dec only.

Lecture 4 In-class Exercise - Q4

What is the approximate total movie gross for the last two months of the 2025 in the united states?

Round to the closest million, e.g. 453 million.

___ million dollars

When you are done, clear the slicer filter, select the slicer menu, and delete it.

Slicing by Value

In this example, we want to find which day and month had the highest maximum GROSS?

1. Setup the Pivot Table as shown.

2. Click Insert Slicer and select GROSS and click OK.

3. Select the largest GROSS value only.

Lecture 4 In-class Exercise - Q5

Which day in which month had the highest maximum gross sales in the United States?

The highest maximum gross sales in the United States occured on ___(day of week) in ___ (month).

When you are done, clear the slicer filter, select the slicer menu, and delete it.

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.

1. Convert the Diamonds Data (Worksheet 2) to a table (Insert > Table)

2. Create a pivot table as shown

   • Note that other text columns would also work in the Values field

3. Count shows the number of observations in each Color/Clarity combination.

NOTE: It is also helpful to center the columns to make the table easier to read.

Lecture 4 In-class Exercise - Q6

How many diamonds in this dataset are classified as Colorless with Slight Inclusions?

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:

  1. Right-Click on any value within Pivot Table

  2. Select ‘Show Values As’

  3. Select option to answer your question

• In the example table, we select % of Column Total to answer this question because of how the table is oriented.

Question: What percent of Colorless diamonds have Slight inclusions? (Similar to Questions 3 and 4 in HW 2)

Lecture 4 In-class Exercise - Q7

What percent of Colorless diamonds have Slight inclusions?

Round answer to one decimal place.

See instructions on previous slide to convert counts to column percents.

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 inclusions are Near colorless.

1. Right-click on any value in the table.

2. In ‘Show Values As’ change % of Column Total to % of Row Total

3. Examine 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 As to answer Question 4 after completing Question 3.

Lecture 4 In-class Exercise - Q8

What percent of diamonds with Slight inclusions are Near colorless?

Round answer to one decimal place.

See instructions on previous slide to convert column percents to row percents or vice versa.

Demonstrations

HW 2 Demo

A short demo of some key aspects of HW Assignment 2

R/Rstudio Demo using Posit Cloud

• Preview of the R and RStudio environment using Posit Cloud.

• This requires that you create free a Posit Cloud account.

Key Points from Today

• Pivot Tables

  • Very effective for summarizing categorical data
  • Efficient once you get used to using them

• Pivot Tables with Slicers

  • Useful for ‘On the Fly’ subsets and toggling between groups

• Caution

  • Sometimes filtering within the table and then filtering using slicers in a pivot table can cause conflicts. Use one or the other.
  • When in doubt, clear all previous filters and clear all fields on the Pivot Table.
  • Resetting the Pivot Table is the quickest way to resolve an error.

HW 2 is due Monday, 1/26/26

To submit an Engagement Question or Comment about material from Lecture 4: Submit it by midnight today (day of lecture).