Pivot Tables

A Pivot Table is one of the most powerful tools in Microsoft Excel used to summarize, analyze, and organize large amounts of data quickly. It helps users create reports without writing complex formulas.

Why Use Pivot Tables?

Sample Sales Data

Creating a Pivot Table

Steps

1. Select the data range.
2. Go to Insert Tab.
3. Click PivotTable.
4. Verify the table/range.
5. Select New Worksheet.
6. Click OK.
7. Drag fields into Rows, Columns, Values, and Filters areas.

Pivot Table Areas

Example Pivot Table

Drag Salesperson to Rows and Amount to Values.

Pivot Table Calculations

Pivot Tables can perform different types of calculations automatically.

Common Value Calculations

Changing Calculation Type

1. Right-click any value in the Pivot Table.
2. Select Summarize Values By.
3. Choose Sum, Count, Average, Max, or Min.

Show Values As

Pivot Tables can display values in different ways without changing the original data.

Slicers

Slicers are visual filter buttons used to filter Pivot Tables quickly. They make reports interactive and easy to use.

Example Slicer

Clicking “Bangalore” instantly filters the Pivot Table to show only Bangalore records.

Steps to Insert a Slicer

1. Click anywhere inside the Pivot Table.
2. Go to PivotTable Analyze tab.
3. Click Insert Slicer.
4. Select the field (City, Product, Salesperson, etc.).
5. Click OK.
6. Use the slicer buttons to filter data.

Advantages of Slicers

Shortcut Keys