Introduction to Pivot Tables

Jul 9, 2024

Introduction to Pivot Tables

Series by John from Excel Campus

Context

  • John introduces Andy, who needs to create a dashboard for a presentation.
  • Andy has a large data set and a list of required reports.
  • Aim: Use Pivot Tables and Pivot Charts to create an interactive dashboard.
  • Pivot Tables: Easy to use, no complex formulas or VBA macros required.
  • Introduction to an add-in called Pivot Pal.

Video 1: Basics of Building a Pivot Table

Steps to Create a Pivot Table

  1. Familiarize with Source Data: Exported data from general ledger software containing order details for December (order date, customer info, salesperson, region, shipping info, product info, price, revenue, quantity).
  2. Ensure Data is in Tabular Format: Headers at the top, no blank columns/rows, no merged cells.
  3. Insert Pivot Table:
    • Go to Insert tab -> Pivot Table
    • Excel auto-detects the range if a cell inside the data set is selected.
    • Choose to place the pivot table on a new worksheet.
  4. Pivot Table Fields List: Appears on the right; contains all fields of the data set and areas to drag fields into.
  5. Drag Salesperson Field to Rows Area: Lists sales reps in rows.
  6. Add Revenue to Values Area: Calculates sum of revenue for each sales rep.
  7. Understanding Pivot Table Calculations: Auto-calculates summary (e.g., sum of revenue for each sales rep).
  8. Sample Standard Report Structure: Sales reps in rows, quarters on top, sum of revenue in the middle, year filter.

Enhancing the Pivot Table

  1. Format Numbers:
    • Right-click any cell in the values area -> Value Field Settings -> Number Format -> Choose Currency -> OK.
  2. Sort Data:
    • Right-click any cell in values area -> Sort -> Largest to Smallest.
  3. Create Pivot Chart:
    • Select any cell in the pivot table -> Analyze/Options tab -> PivotChart -> Choose Bar Chart -> OK.

Cleaning Up Pivot Chart

  1. Hide Field Buttons: Unnecessary for the dashboard.
  2. Reverse Order of Sales Reps: Format Axis -> Categories in reverse order.
  3. Remove Unnecessary Labels and Lines: Delete unnecessary elements (horizontal labels, legends, vertical lines).
  4. Add Data Labels: Click Plus button -> Data Labels.
  5. Adjust Bar Width: Right-click a bar -> Format Data Series -> Adjust Gap Width.
  6. Add Title to Chart: E.g., Sales by Rep for December 2014.

Recap Pivot Table and Chart Features

  • Easy summarization and visualization of data.
  • Not as intimidating as they may seem.

Additional Tools: Pivot Pal

  • Pivot Pal Features:
    • Built-in search for fields.
    • Quick addition of fields to Pivot Table Areas using buttons or shortcuts.
    • Auto-formats values based on source formatting.
    • Allows custom formatting options.

Next Video Preview

  • Learning more advanced features of pivot tables.
  • Exploring different calculation types, adding data, applying filters, and more.

Conclusion

  • Encouraging feedback and comments.
  • John’s excitement about sharing Pivot Table functionalities with the audience.