A06:Excel Pivot Chart

You are given a retailer order dataset in Orders worksheet.
Your task is to use Pivot Tables and Pivot Charts to analyze the data and present key insights in a simple dashboard.

This assignment evaluates your ability to:

• construct Pivot Tables independently
• select appropriate chart types
• use multiple measures meaningfully
• format large values for readability
• organize outputs clearly

Important Notes:

• Convert the dataset into an Excel Table first.
• Always use Pivot Tables (no manual totals).
• Charts must be Pivot Charts.
• Add clear titles and labels for charts and axes.
• Large numbers should be formatted for readability
  (i.e. show data without decimal, in thousands or millions)
• Formatting to show numbers in thousands or millions:
  Thousands: #,##0,” K” Thousands with $ and one decimal point: $ #,##0.0,” K”
  Millions: #,##0,,” M” Millions with $ and two decimal points: $ #,##0.0,,” M”

Submission: .xlsx file only. Make sure you are not submitting a file that starts with ~ or $. Also, make sure you are submitting the file that you worked on. Check the file modification date if needed.

Setup Requirements (3 pts)

0A. Convert the dataset into an Excel Table. (1 pt)
0B. Create two worksheets named Report and Charts.
All Pivot Tables must be placed in Report and all Pivot Charts must be in Charts. (2 pt)

PART A Pivot Tables (25 pts)

Task 1 Revenue by Region (7 pts)

1A. Create a Pivot Table showing total Revenue by Region. (4 pts)
Rename fields or headings so the table reads professionally.
1B. Sort results to clearly show the highest-performing region. (2 pt)
1C. Apply readable number formatting (thousands or millions) (1 pt)

Task 2 Category Drill-Down (8 pts)

2A. Create a Pivot Table that allows analysis of Profit across both ProductCategory and
ProductSub-Category. (6 pts)
Structure the table so drill-down between levels is possible.
(ProductCategory -> ProductSub-Category)
Ensure that the drill-down and drill-up both work.

2B. Apply readable formatting and clear labels (thousands or millions) (2 pts)

Task 3 Revenue Trend Over Time (10 pts)

Create a Pivot Table showing Revenue trends by month and CustomerSegment

3A. Extract Year and Month information from Order Date in Orders worksheet. (3 pts)

3B. Create Pivot Table so time is in rows and CustomerSegment is in columns. (6 pts)
Structure the table so the time sequence is logical (Year followed by Month).

3C. Apply formatting so data is shown with no decimal (1 pt)

PART B Pivot Charts (25 pts)

Create all charts in the Charts worksheet.
Charts must be Pivot Charts.

Task 4 Category Comparison Chart (5 pts)

Create a chart that compares Revenue across ProductCategory.

4A. Use an appropriate chart type for comparing categories. (2 pts)
4B. Add clear title and axis labels. (2 pts)
4C. Apply readable number formatting. (1 pt)

Task 5 Revenue Trend Chart (6 pts)

Create a chart that shows how Revenue changes over time.

5A. Use a chart type appropriate for trends. (4 pts)
Ensure the time sequence is correct and logical.
5B. Add clear title and axis labels. (1 pt)
5C. Add Slicer to filter chart by Product Category and ensure it filters the chart. (1 pt)

Task 6 Share of Revenue Chart (6 pts)

Create a chart showing how total Revenue is distributed across CustomerSegments.

6A. Choose an appropriate chart type for showing proportion. (4 pts)
6B. Add labels to show % distribution across CustomerSegments. (2 pts)
Make sure that the data labels and % are clearly visible.

Task 7 Two-Measure Comparison Chart (8 pts)

Create a chart showing both Revenue and Profit by Region.

7A. Select a chart type that can meaningfully display two measures. (6 pts)
Use axes appropriately so both measures are visible.
Hint: This chart will require two vertical axes.

7B. Add titles and Axis labels that clearly explain what is shown. (2 pts)

PART C Simple Dashboard (7 pts)

Task 8 Create Dashboard Sheet (7 pts)

8A. Create a worksheet named Dashboard. (1 pt)
8B. Move at least three charts to this sheet. (3 pts)
8C. Resize and arrange charts neatly. (2 pts)
8D. Add a title to the dashboard. (1 pt)