Excel Pivot Tables

A Pivot Table is one of the most powerful data analysis tools in Excel. It allows large amounts of data to be summarized, analyzed, and explored quickly — without writing a single formula. With a few clicks, thousands of rows of data can be grouped, counted, summed, and compared in a clean, interactive table.

What is a Pivot Table?

A Pivot Table takes raw data (a flat list of rows and columns) and automatically summarizes it. It can answer questions like:

• What are the total sales per region?
• How many orders were placed per product category each month?
• Which salesperson had the highest average deal value?

The word "pivot" refers to the ability to rotate and rearrange the table dynamically — changing what rows, columns, and values are shown without altering the original data.

Preparing Data for a Pivot Table

Before creating a Pivot Table, the source data should be properly formatted:

• Each column must have a unique header in row 1.
• There should be no blank rows or blank columns within the data.
• Each row represents one record (e.g., one transaction, one employee).
• Avoid merged cells in the data range.

Example Source Data

  | A         | B       | C        | D      | E    |
1 | OrderDate | Region  | Product  | Sales  | Qty  |
2 | 01/01/24  | North   | Laptop   | 45000  | 3    |
3 | 02/01/24  | South   | Phone    | 20000  | 5    |
4 | 03/01/24  | North   | Tablet   | 15000  | 4    |
5 | 04/01/24  | East    | Laptop   | 60000  | 4    |
6 | 05/01/24  | South   | Tablet   | 18000  | 6    |
7 | 06/01/24  | North   | Phone    | 12000  | 3    |

Creating a Pivot Table

1. Click any cell inside the data range.
2. Go to Insert → PivotTable.
3. In the dialog box, confirm the data range (Excel usually detects it automatically).
4. Choose where to place the Pivot Table: New Worksheet (recommended) or the existing sheet.
5. Click OK.

A blank Pivot Table and the PivotTable Field List panel will appear on the right side of the screen.

The PivotTable Field List

The Field List shows all available column headers from the source data. These fields are dragged and dropped into four areas:

• Filters: Add a field here to filter the entire Pivot Table by that field. A dropdown appears above the table.
• Columns: Fields placed here appear as column headers in the Pivot Table.
• Rows: Fields placed here appear as row labels on the left side.
• Values: Fields placed here are calculated (summed, counted, averaged, etc.).

Building a Simple Pivot Table

Goal: Total Sales by Region

1. Drag Region to the Rows area.
2. Drag Sales to the Values area.

Result

  | Region | Sum of Sales |
  | East   | 60000        |
  | North  | 72000        |
  | South  | 38000        |
  | Grand Total | 170000  |

Goal: Sales by Region and Product

1. Drag Region to Rows.
2. Drag Product to Columns.
3. Drag Sales to Values.

Result

  | Region | Laptop | Phone | Tablet | Grand Total |
  | East   | 60000  |       |        | 60000       |
  | North  | 45000  | 12000 | 15000  | 72000       |
  | South  |        | 20000 | 18000  | 38000       |
  | Grand Total | 105000 | 32000 | 33000 | 170000 |

Changing the Value Calculation

By default, the Values area uses SUM for numbers. To change this:

1. Click the dropdown arrow next to the field name in the Values area.
2. Select Value Field Settings.
3. Choose from: Sum, Count, Average, Max, Min, Product, etc.

Example

  Change "Sum of Sales" to "Average of Sales":
  → Now the table shows the average sale amount per region.

Applying Filters

Using the Filter Area

Drag a field to the Filters area. A dropdown appears above the Pivot Table. Click it to show only data for selected values.

Row/Column Label Filters

Click the dropdown arrow on any row or column label header to sort or filter specific items within the table.

Refreshing a Pivot Table

When source data is changed, added, or deleted, the Pivot Table does not update automatically. To refresh it:

• Right-click anywhere inside the Pivot Table → Refresh.
• Or go to PivotTable Analyze → Refresh.

If new rows have been added outside the original data range, the data source range must be updated first via PivotTable Analyze → Change Data Source.

Formatting a Pivot Table

Applying a Style

Click inside the Pivot Table. Go to PivotTable Design tab on the Ribbon. Choose a color scheme from the gallery of Pivot Table styles.

Number Formatting

1. Click the dropdown next to a value field in the field list.
2. Select Value Field Settings → Number Format.
3. Choose Currency, Number, or Percentage as needed.

Grouping Data in a Pivot Table

Dates can be automatically grouped by month, quarter, or year in a Pivot Table.

Grouping Dates

1. Right-click any date in the Row or Column area.
2. Select Group.
3. Choose grouping options: Days, Months, Quarters, Years.

Grouping Numbers

Numbers can also be grouped into ranges (e.g., 0–1000, 1001–2000) for frequency analysis.

Slicers

Slicers are visual filter buttons that make filtering a Pivot Table faster and more user-friendly.

Adding a Slicer

1. Click inside the Pivot Table.
2. Go to PivotTable Analyze → Insert Slicer.
3. Select the field(s) to use as slicers.
4. Click OK. Slicer buttons appear on the sheet.

Clicking a button in the slicer immediately filters the Pivot Table. Multiple values can be selected by holding Ctrl and clicking.

Calculated Fields

A Calculated Field adds a custom formula-based column to the Pivot Table using existing fields.

Example: Adding Profit Margin

1. Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field.
2. Name it "Profit Margin".
3. Formula: =Sales / Revenue
4. Click OK. The new field appears in the Values area.

Summary

• A Pivot Table summarizes large datasets instantly without formulas.
• Data is dragged into four areas: Filters, Rows, Columns, and Values.
• Values can be calculated as Sum, Count, Average, Max, Min, and more.
• Pivot Tables must be manually refreshed when source data changes.
• Dates can be grouped by month, quarter, or year with a right-click.
• Slicers provide visual, clickable filters for fast interactive analysis.
• Calculated Fields allow custom formulas to be added to the Pivot Table.