Pivot Tables for Data Analysis

What is a Pivot Table?

A PivotTable is Excel's most powerful analytical tool. It allows you to summarise, group, count, and aggregate large datasets in seconds without writing a single formula. You drag fields into rows, columns, and values, and Excel instantly recalculates the summary.

PivotTables are used daily by business analysts, finance teams, and operations managers to answer questions like:

• What are total sales by region and product category?
• How many orders were placed each month?
• What is the average order value per customer segment?

Learning to use PivotTables effectively can compress hours of manual calculation into minutes.

Creating a PivotTable

Before creating a PivotTable, ensure your data is structured correctly:

• One row per record (no merged cells)
• Column headers in the first row
• No completely blank rows or columns
• Formatted as an Excel Table (recommended)

To create a PivotTable:

1. Click anywhere in your data
2. Go to Insert > PivotTable
3. Confirm the data range (Excel usually detects it automatically)
4. Choose where to place the PivotTable (a new worksheet is recommended)
5. Click OK

The PivotTable Field List appears on the right side. You now drag fields into four areas:

• Rows: Categories to group by (e.g., Region, Product)
• Columns: Secondary grouping (e.g., Month)
• Values: What to calculate (e.g., Sum of Sales)
• Filters: Top-level filters applied to the whole table

Common PivotTable Operations

Changing the Value Calculation

By default, PivotTables sum numeric fields. To change this:

1. Click the dropdown arrow on the value field in the Values area
2. Select "Value Field Settings"
3. Choose from: Sum, Count, Average, Max, Min, Count Numbers, StdDev

Showing Values as Percentages

Right-click any value in the PivotTable > Show Values As:

• % of Grand Total
• % of Column Total
• % of Row Total
• % Difference From (useful for year-on-year comparison)
• Running Total In

Grouping Dates

If you have a date field in Rows, you can group by:

• Years, Quarters, Months, Weeks, Days Right-click a date in the PivotTable > Group > select grouping level

Sorting and Filtering

• Click the dropdown arrow on a Row or Column field header to filter specific values
• Right-click any value and select Sort to sort by value descending (useful for top-N analysis)

PivotTable Best Practices

Refresh your data: When your source data changes, right-click the PivotTable and select Refresh. The PivotTable does not update automatically.

Separate your source data: Keep the raw data on one sheet and the PivotTable on another. Never modify your raw data manually after loading.

Use descriptive field names: Rename value fields in the PivotTable (right-click the field in the Values area > Value Field Settings > Custom Name) so the output is readable.

Duplicate PivotTables: If you need multiple views of the same data, you can duplicate a PivotTable sheet rather than rebuilding from scratch.

Slicers: Visual Filters for PivotTables

A Slicer is a visual button panel that filters a PivotTable interactively. They are particularly useful for dashboards.

To add a Slicer:

1. Click anywhere in the PivotTable
2. Go to PivotTable Analyse > Insert Slicer
3. Select the fields to create slicers for
4. Click OK

Slicers can be connected to multiple PivotTables on the same data source (right-click the slicer > Report Connections).

Calculated Fields

You can create custom calculations within a PivotTable that are not in your source data. For example, calculating profit margin from revenue and cost columns.

To add a Calculated Field:

1. Click in the PivotTable
2. Go to PivotTable Analyse > Fields, Items, & Sets > Calculated Field
3. Enter a name and formula

Example calculated field for profit margin:

= Revenue / (Revenue + Cost)

Note: Calculated fields have limitations. They cannot reference individual cells, only field names. For complex calculations, it is often better to add a calculated column to the source data instead.

PivotCharts

A PivotChart is a chart linked to a PivotTable that updates automatically when the PivotTable data changes.

To create a PivotChart:

1. Click anywhere in the PivotTable
2. Go to PivotTable Analyse > PivotChart
3. Select chart type

PivotCharts are particularly useful for dashboards because they maintain the interactive filtering capabilities of the PivotTable.

Practical PivotTable Analysis Workflows

Top N Analysis

1. Add the dimension field (e.g., Product) to Rows
2. Add the metric (e.g., Revenue) to Values
3. Right-click the metric column > Sort > Largest to Smallest
4. Add a filter to show only the top 10

Month-over-Month Comparison

1. Add Date to Rows, group by Month and Year
2. Add Revenue to Values twice
3. For the second Revenue field: Show Values As > % Difference From > Previous

Heatmap View (Conditional Formatting)

After building a PivotTable, apply conditional formatting to the values area to create a colour-coded heatmap that makes patterns immediately visible.

Key Takeaways

• PivotTables are Excel's most powerful summarisation tool, allowing you to group, count, and aggregate large datasets in seconds without formulas.
• The four areas of a PivotTable (Rows, Columns, Values, Filters) control how data is grouped and what is calculated.
• Right-click any value and use "Show Values As" to display percentages of totals, running totals, or period-over-period changes.
• Slicers provide visual interactive filtering that makes PivotTables suitable for dashboard use.
• Refresh your PivotTable (right-click > Refresh) whenever the source data is updated, as PivotTables do not update automatically.

Practice Exercise

Using a sales dataset with at least 200 rows, create three PivotTables:

1. Total revenue and order count by Region and Month (group dates by month)
2. Top 10 products by revenue, sorted descending
3. Revenue by product category showing both the absolute value and percentage of grand total

Add a slicer for Region that filters all three PivotTables simultaneously. Format the values area of table 1 with conditional formatting to create a heatmap.