Lesson 2.12 – Pivot Charts & Practical Exercises

Lesson Overview

Pivot Charts are visual representations of Pivot Table data, allowing dynamic analysis and easy reporting. In this lesson, you will learn how to create Pivot Charts and apply formatting for effective visualization.

1. What is a Pivot Chart?

• Definition: A Pivot Chart is a chart linked to a Pivot Table that updates automatically when the Pivot Table changes.

• Purpose:

  • Visualize trends and patterns in data

  • Easily compare categories

  • Interactive and dynamic updates

2. Creating a Pivot Chart

Step-by-Step:

1. Select any cell in the Pivot Table.

2. Go to Insert → PivotChart.

3. Choose a chart type (Column, Bar, Line, Pie, etc.).

4. Click OK.

Result: A Pivot Chart linked to your Pivot Table appears.

3. Modifying Pivot Chart Fields

• Use the PivotChart Fields pane to:

  • Drag fields to Axis (Categories), Legend (Series), or Values

  • Apply filters to focus on specific data

Example: Display total sales by product category for a specific region.

4. Formatting Pivot Charts

• Chart Styles: Select chart → Chart Design → Chart Styles

• Change Colors: Format → Shape Fill / Outline

• Add Data Labels: Chart Elements → Data Labels

• Adjust Axis: Right-click axis → Format Axis → Customize scale, labels

5. Benefits of Using Pivot Charts

• Dynamic updates when Pivot Table changes

• Interactive filtering and slicing

• Easy to understand visual summaries for stakeholders

6. Practical Exercises for Topic 2

1. Sorting Exercise: Sort sales data by Region in descending order.

2. Filtering Exercise: Filter sales data to show only top 5 products by revenue.

3. Data Validation Exercise: Create a drop-down list for Month selection in the dataset.

4. Subtotal Exercise: Add subtotals to show total sales by Region.

5. Data Cleaning Exercise: Remove duplicate entries and split full names into first and last names.

6. Pivot Table Exercise:

   • Create a Pivot Table showing Total Sales by Product Category.

   • Add Region as column labels.

   • Display % of Grand Total for sales values.

7. Pivot Chart Exercise: Create a Column Pivot Chart from the Pivot Table and format it with data labels and distinct colors.

7. Topic 2 – Practice Questions

1. What is the difference between a Pivot Table and a Pivot Chart?

2. How do you filter data in a Pivot Table to show only certain values?

3. Explain how to group dates in a Pivot Table.

4. How can you display Pivot Table values as percentages of the total?

5. Give an example of using a Pivot Chart to visualize data for a sales report.