← Back to Resources

Data Analytics

PivotTables & Charts Guide

Master summarizing raw logs, building interactive dashboards with Slicers, and visualizing sales and support metrics.

📥 Download Pivot Practice Workbook

Get the companion Excel file containing the raw data for both mini-cases below.

Download Workbook

1. Core Pivot Table Concepts

A Pivot Table summarizes large tables of raw data in seconds. Rather than writing dozens of complex conditional formulas (like SUMIFS), you drag fields into four key areas:

The Four Field Areas

  • Rows: Vertical groups. Dragging a field here lists its unique categories as table rows.
  • Columns: Horizontal groups. Dragging a field here lists unique categories across the columns.
  • Values: What you want to calculate (e.g. sum of sales, count of orders, average rating).
  • Filters: Limits the overall Pivot Table data using dropdown selections.

2. Step-by-Step Tutorial

  1. Insert Table: Click any cell inside your raw data block. Go to the Insert Tab and click PivotTable. Choose to place it in the same worksheet or a new sheet.
  2. Build Layout: Drag categories to Rows (e.g. Sales Rep) and numerical data to Values (e.g. Revenue).
  3. Value Settings: Excel defaults to summing numeric fields and counting text. To change calculations: Right-click a number inside the Pivot Table, select Value Field Settings, and choose **Average**, **Count**, etc. Format the numbers directly inside this dialog!
  4. Add Slicers: Click inside the Pivot Table. Go to the PivotTable Analyze tab and click Insert Slicer. Check fields (e.g., Region, Category) to add clickable filter buttons.
  5. Add Pivot Chart: Click inside the Pivot Table. Go to the Insert Tab and click PivotChart. Choose a Column, Bar, or Line chart. It will update dynamically whenever you click a Slicer!

3. Practical Mini-Cases

Open your downloaded practice workbook and try solving these real-world scenarios:

Case 1: Retail Sales Audit Workbook Tab: Case 1

You have a raw sales history log containing transactions from various regions, sales representatives, product categories, and revenues.

📋 Analytics Requirements:

  • Who is the top-performing Sales Representative by total revenue?
  • Which product Category generates the largest share of sales?
  • How does clothing revenue compare across different regions?

💡 Solution Steps:

  1. Insert Pivot Table at cell I12.
  2. Drag Sales Rep to Rows, and Revenue to Values. Right-click any cell in the revenue column, choose Sort > Sort Largest to Smallest. (Result: Chetan is top).
  3. Drag Category to Rows instead of Sales Rep. Highlight the table, click Insert > PivotChart > Pie Chart to show revenue distributions.
  4. Add a Slicer for **Region** to inspect how categories perform in specific territories.
Case 2: Support Ticket Audit Workbook Tab: Case 2

You have a raw log of customer support tickets containing Agent names, Priority levels, Ticket Statuses, response times, and ratings.

📋 Analytics Requirements:

  • What is the total ticket volume by Priority level and Status?
  • Which Agent handles resolving tickets with the lowest average Response Time?
  • Who has the highest average Customer Rating?

💡 Solution Steps:

  1. Insert a Pivot Table at cell I12.
  2. Drag Priority to Rows, Status to Columns, and Ticket ID to Values. Make sure it calculates by COUNT of Ticket ID.
  3. Create a second Pivot Table: Drag Agent to Rows, and Response Time (Hrs) to Values. Right-click the values header, select Value Field Settings, change calculation to **Average**, and click Number Format to format it to 1 decimal place.
  4. Add **Rating (1-5)** to Values, change its calculation to **Average** as well. Compare agent performance.