Lesson 4: Analyzing Data with Pivot Tables
Overview
In this exercise, participants will delve into advanced Excel techniques, focusing
on analyzing data with pivot tables. The goal is to simulate real-world scenarios
to enhance practical skills in data analysis. By addressing specific questions and
challenges, participants will gain proficiency in optimizing data visualization and
analytical processes.
Instructions
• Work through all questions individually or within your group
• Divide your time evenly among the questions to ensure comprehensive
understanding
• Utilize Excel's documentation and online resources to enhance your
problem-solving abilities
• Use the given dataset to complete the given tasks
Task
As a business analyst of an organization, you are required to summarize, sort,
reorganize, group, count, total or average data stored in a table. Also, you are
required to do grouping by any field (column), using advanced calculations on
the dataset of company.
To achieve these tasks, you will be learning a few concepts, such as grouping in
pivot table, custom calculation, calculated field, calculated item, and slicer that
will help find a solution for the given scenario.
Use the Lesson_4_GP_Revenue_dataset which consists of the following
columns:
• Product Category: Product classification
• Revenue: Total sales amount (in $)
• Order Date: Dat of purchase
• Order Quantity: No of items purchased
• Cost: Cost amount (in $)
• Profit: Profit amount (in $)
• Discount: Discount amount (in $)
• Region: Region of the store
1. Summarizing Total Revenue by Product Category:
Summarize the total revenue generated by each product category in the
company's dataset using a pivot table
2. Creating a Calculated Field for Profit Margin:
Create a calculated field in a pivot table to determine the profit margin for each
product, considering profit and cost columns in the dataset
3. Slicer for Regional Filtering:
Utilize a slicer in a pivot table and explain how it contributes to improved data
analysis, taking the "Region" field as an example
Discussion Questions (Optional)
If time permits, discuss the following questions:
• How does summarizing total revenue by product category contribute to
understanding the company's sales performance?
• Discuss the advantages of using a slicer for regional filtering in a pivot
table, and how it improves the efficiency of data analysis.
Answer Key
Dataset:
1: Summarizing Total Revenue by Product Category:
Step 1: Create a pivot table
Step 2: Drag Product Category to Rows and Revenue to Values (summarized
by sum)
2. Creating a Calculated Field for Profit Margin:
Step 1: Create a pivot table
Step 2: PivotTable Analyze > Field, Items, & Sets > Calculated Field
Step 3: Name it Profit Margin and use the formula (Profit / Revenue) * 100
Result:
3. Slicer for Regional Filtering:
Step 1: Create a pivot table
Step 2: Go to the Insert tab and click on Slicer
Step 3: Choose the Region field
