146|EXCEL – Pivot Tables

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

Thank you Carter for the nice challenge on Pivot Tables! Here is my solution.


Here is my solution! There were a couple of differences between the instructions and the solution so I wasn’t perfectly clear on which tables to make, but I thought it was a great challenge!

Challenge146_Data.xlsx (168.1 KB)

Great training! Here is my solution.

Very fun. Thanks.


This was great! Here is my solution.

Hi! Thanks for the Challenge. Here is my answers.

I don’t think I did this right but great challenge!

"Time to complete: 10 minutes
Rating: Easy
This was a fun challenge that highlights an important aspect of excel that many of us will use going forward in our careers! Thanks so much for providing it! Attached below is my solution.
SpencerSmithChallenge146.xlsx (165.0 KB)

Here’s my solution!

Challenge146_Data.xlsx (212.3 KB)
Here is my solution, great challenge thank you!

Time to complete10 minutes
Difficulty: Easy
Great challenge to brush up on Excel Skills
Challenge146PivotTables.xlsx (162.2 KB)

Challenge146_Data (1).xlsx (289.2 KB)

Challenge146_Data - Coliano solution.xlsx (178.2 KB)
Here’s my solution! took me about 20 minutes. Thanks!

Time to Complete: 15min
Challenge146_Data.xlsx (164.4 KB)

Rating: 8/10
Fun exercise and great practice to get familiar with PivotTables

Great practice for Pivot Tables!

Challenge146_Data.xlsx (109.6 KB)

Time to complete: 15 min
Difficulty: 3/10
This was good practice for pivot tables! Below is my solution. Thank you!