BYU Student Author: @Carter_Lee
Reviewers: @Benjamin_Lau @Dallin_Gardner
Estimated Time to Solve: 10 Minutes
This is an intro challenge that is part of the Excel Learning Path.
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
SmartHome Co. sells smart home systems of three levels (Base, Plus, and Full). SmartHome has an international salesforce and generates millions of sales each year. You are given the dataset of SmartHome branches each employee information including name, location, employeeID, Branch ID, and Sales Generated. In this challenge, you will use Pivot Tables functions in Excel to uncover interesting facts about SmartHome, its employees, and sales mix analysis.
Instructions
- Open up the “Employee Data” Sheet
- Create a new sheet called “Pivot Table”
- In sheet titled “Pivot Table” create 4 new different Pivot Tables using the data in the Employee Data sheets
- For the first pivot table display a list of employees that yield the above 1,000,000 in sales generated for this year
- For the second pivot table to display the Average cost of Sales for the Japan branch
- For the third pivot table that shows each regions sales in cost of sales, sales generated and the average of Employee satisfaction score
Suggestions and Hints
For the First Pivot Table
- Drag the Employee Name to the Rows area.
- Drag the Sales Generated to the Values area, and set it to show the Sum.
- Apply a filter to display only those records where Sales Generated is above $1,500,000.
For the Second Pivot Table
- Drag the Employee Name to the Rows area.
- Drag the Sales Generated to the Values area, and set it to show the Average.
- Apply a filter to include only the Japan branch.
For the Third Pivot Table
- Drag the Region to the Rows area.
- Drag the Sales Generated to the Values area, and set it to show the Sum.
Data Files
Solution
Challenge146_Solution.xlsx
Solution Video: Challenge 146|EXCEL – Pivot Table
