Module 8: Manage Advanced Charts and Tables Part II
Reflection 2: How does changing field selections in a PivotTable help you view your
data from different perspectives?
Good answers might be something like: Changing field selections in a PivotTable lets
you reorganize how the data is grouped and summarized, helping you look at the
information in different ways. For example, you can switch from viewing total
bonuses by team to viewing them by individual employee or by quarter, which gives
you new insights and helps you better understand trends or patterns in the data.
Reflection 4: Discuss some ways in which grouping data in a PivotTable help to
simplify data analysis and improve decision-making.
Good answers might be something like: Grouping data in a PivotTable allows users
to organize and summarize large datasets in a more manageable and meaningful
way. For example, grouping dates by months or quarters helps users quickly identify
trends over time, while grouping numerical data into ranges (e.g., sales figures into
low, medium, and high categories) makes it easier to spot patterns or outliers. This
simplification reduces the complexity of large datasets, allowing decision-makers to
focus on the most relevant information. Grouping also allows for more granular
insights, helping users analyze data at different levels, whether it's by time period,
categories, or numerical ranges. As a result, grouping helps improve decision-
making by making data more accessible and actionable.
Module Project:
For this project, copy this sample dataset into an Excel workbook. Your task is to
analyze the sales performance using this sales data from four regional teams
Create a PivotTable to do the following:
1. Display total revenue by region and product category
2. Calculate average units sold by each salesperson
3. Group dates by month and display revenue per month
4. Add slicers for customer type and region
5. Calculate the following:
a. Profit = Revenue - Cost
b. Profit Margin = Profit / Revenue
Orde Regio Salespers Product Product Unit Unit Revenu Cos Custom
r n on Categor s Price e t er Type
Date y Sol
d
2025 North Alice Electroni Laptop 5 800.0 4000.0 320 New
-01- Green cs 0 0 0
12
2025 South John Furniture Desk 2 300.0 600.00 420 Returnin
-01- Smith 0 g
15
2025 West Maria Clothing Shirt 10 25.00 250.00 175 New
-02- Lopez
03
2025 East Ravi Books Novel 8 15.00 120.00 84 New
-02- Kumar
10
2025 North Alice Furniture Chair 6 150.0 900.00 630 Returnin
-01- Green 0 g
27
2025 South John Electroni Tablet 4 500.0 2000.0 150 New
-02- Smith cs 0 0 0
14
2025 West Maria Books Textbook 3 60.00 180.00 135 Returnin
-03- Lopez g
01
2025 East Ravi Electroni Laptop 2 850.0 1700.00 1275 New
-03- Kumar cs 0
12
2025 North Alice Clothing Jacket 7 120.0 840.00 588 New
-01- Green 0
22
2025 South John Books Novel 5 15.00 75.00 60 Returnin
-03- Smith g
08
2025 East Ravi Furniture Desk 3 350.0 1050.00 735 Returnin
-02- Kumar 0 g
16
2025 West Maria Clothing Jeans 6 60.00 360.00 252 New
-03- Lopez
15
2025 North Alice Books Magazine 9 10.00 90.00 63 Returnin
-02- Green g
04
2025 East Ravi Electroni Smartpho 3 600.0 1800.00 135 New
-03- Kumar cs ne 0 0
17
2025 South John Clothing Shirt 12 30.00 360.00 270 New
-01- Smith
05
Review the rubric below to see how you will be graded.
Sample Project:
Sample project attached below.
Excel_M8_Sample
[Link]
Manage Advanced Charts and Tables Part II
Points
Category Excellent Good Needs Improvement
Possible
Creating 3-4 points Demonstrated a 2 points Demonstrated a 0-1 points Demonstrated 4
PivotTables understanding of
strong satisfactory understanding of little to no understanding of
the objective. The task was the objective. The task was the objective. The task was
performed accurately. performed adequately with a not performed well.
few errors.
Modifying field 3-4 points Demonstrated a 2 points Demonstrated a 0-1 points Demonstrated 4
selections and strong understanding of satisfactory understanding of little to no understanding of
options the objective. The task was the objective. The task was the objective. The task was
performed accurately. performed adequately with a not performed well.
few errors.
Creating slicers 3-4 points Demonstrateda 2 points Demonstrated a 0-1 points Demonstrated 4
strong understanding of satisfactory understanding of little to no understanding of
theobjective. The task was the objective. The task was the objective. The task was
performed accurately. performed adequately with a not performed well.
few errors.
Grouping 3-4 points Demonstrated a 2 points Demonstrated a 0-1 points Demonstrated 4
PivotTable data strong understanding of satisfactory understanding of little to no understanding of
theobjective. The task was the objective. The task was the objective. The task was
performed accurately. performed adequately with a not performed well.
few errors.
Adding and 3-4 points Demonstrated a 2 points Demonstrated a 0-1 points Demonstrated 4
configuring strong understanding of satisfactory understanding of little to no understanding of
calculated fields the objective. The task was the objective. The task was the objective. The task was
performed accurately. performed adequately with a not performed well.
few errors.
Total Points: 20