Excel Workout 27 - Excel Mastery: Dive into PivotTables

Workouts Excel
1

PivotTables in Excel allow you to summarize, analyze, explore, and present your data in a structured manner. In this workout, learn the essentials of creating and manipulating PivotTables to derive valuable insights.

Scenario:

You’re an analyst at a retail company, and you’ve been given a sales dataset. The dataset includes columns like Date, Product, Region, Quantity Sold, and Price. Your task is to use PivotTables to answer various business questions.

Objectives:

By the end of this workout, you should be able to:

  1. Create PivotTables to summarize data.

  2. Manipulate and filter data within PivotTables.

  3. Draw insights from structured PivotTable data.

Interactive Task:

Given the sales dataset and the following business questions, describe how you’d set up the PivotTable:

  1. How many of each product were sold in each region?

    • Row Field: ________________________

    • Column Field: ________________________

    • Value Field: ________________________

  2. What’s the total revenue for each product category across different months?

    • Row Field: ________________________

    • Column Field: ________________________

    • Value Field: ________________________

  3. Which region had the highest average sales price for a specific product?

    • Row Field: ________________________

    • Column Field: ________________________

    • Value Field: ________________________

    • Filter: ________________________

Questions:

  1. Why are PivotTables particularly useful in Excel?

    • i) They automatically create complex formulas.

    • ii) They provide a dynamic way to summarize large datasets.

    • iii) They allow for advanced scripting.

    • iv) They integrate external databases into Excel.

  2. If you want to compare data across two different categories in a PivotTable, where would you typically place the second category?

    • i) In the Row Field.

    • ii) In the Column Field.

    • iii) In the Value Field.

    • iv) As a Filter.

Duration: 30 minutes

Difficulty: Intermediate

Period:
This workout will be released on Tuesday, September 5, 2023, and will end on Thursday, September 28, 2023. But you can always come back to any of the workouts and solve them.

3

Thank you for your workout. Please find my answers as below:
Answers for Interactive Tasks:
1 - How many of each product were sold in each region?

Row Field: Region

Column Field: Product

Value Field: Quantity Sold

2-What’s the total revenue for each product category across different months?

Row Field: Date

Column Field: Product

Value Field: Quantity Sold*Price

3-Which region had the highest average sales price for a specific product?

Row Field: Region

Column Field: ________________________

Value Field: Average Price

Filter: Product

Questions:

1. Why are PivotTables particularly useful in Excel?

  • ii) They provide a dynamic way to summarize large datasets.

2. If you want to compare data across two different categories in a PivotTable, where would you typically place the second category?

  • i) In the Row Field.
4

Hi - Thanks for the workout. Please find my responses below.

Interactive Task:

Given the sales dataset and the following business questions, describe how you’d set up the PivotTable:

  1. How many of each product were sold in each region?
  • Row Field: Region____________________
  • Column Field: Product______
  • Value Field: ___________Sum of Quantity Sold

Product/Region can be interchangeable depending on their numbers.

. What’s the total revenue for each product category across different months?

  • Row Field: Product__________
  • Column Field: Months______________
  • Value Field: Revenue____________
  1. Which region had the highest average sales price for a specific product?
  • Row Field: Region____
  • Column Field: ________________________
  • Value Field: ______Average Sales
  • Filter: Product________________

Questions:

  1. Why are PivotTables particularly useful in Excel?
  • i) They automatically create complex formulas.
  • ii) They provide a dynamic way to summarize large datasets.
  • iii) They allow for advanced scripting.
  • iv) They integrate external databases into Excel.

ii) They provide a dynamic way to summarize large datasets.

  1. If you want to compare data across two different categories in a PivotTable, where would you typically place the second category?
  • i) In the Row Field.
  • ii) In the Column Field.
  • iii) In the Value Field.
  • iv) As a Filter.

In the Column Field.

Thanks
Ankit J

5

Hi @EnterpriseDNA,

Here is my solution to this workout.
Questions:

  1. Why are PivotTables particularly useful in Excel?
    Ansswer:
  • ii) They provide a dynamic way to summarize large datasets.
  1. If you want to compare data across two different categories in a PivotTable, where would you typically place the second category?
    Answer:
  • ii) In the Column Field.

Interactive Tasks:
How you’d setup the pivot table:

  1. How many of each product were sold in each region?
    Answer:
  • Row Field: Product
  • Column Field: Region
  • Value Field: Quantity Sold (or you can use any field that represents the quantity sold)
  1. What’s the total revenue for each product category across different months?
    Answer:
  • Row Field: Product (or Product Category if you have a separate category column)
  • Column Field: Date (grouped by months)
  • Value Field: Sum of (Quantity Sold * Price)
  1. Which region had the highest average sales price for a specific product?

Answer:

  • Row Field: Region
  • Column Field: (Leave this field blank)
  • Value Field: Average of Price
  • Filter: Product (Select the specific product you want to analyze)

Thanks for the workout.
Keith