Excel WhisperExcel Whisper

How to Use Pivot Tables in Excel

Master Excel Pivot Tables for powerful data analysis

Excel Pivot Tables are powerful tools for data analysis that allow you to summarize, analyze, explore, and present your data in a dynamic and flexible way. Whether you're dealing with sales reports, financial data, or any large dataset, Pivot Tables can help you extract meaningful insights quickly and efficiently.

Try Online

Create Pivot Tables instantly in your browser - faster and more intuitive than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

Pivot Table Guide

Comprehensive guide to creating and using Pivot Tables

Basic Structure

A Pivot Table consists of four main areas: Rows, Columns, Values, and Filters. Values can be summarized using various functions like Sum, Count, Average, etc.

Creating a Pivot Table

1

Select your data range including headers and all data

2

Click on the 'Insert' tab in Excel's top menu

3

In the 'Tables' group, click 'PivotTable'

4

In the dialog box, confirm your data range and choose where to place the PivotTable (new worksheet or current worksheet)

5

After clicking 'OK', the PivotTable area and Field List pane will appear

6

Drag and drop fields into the four areas: Filters (to filter the entire table), Columns (creates column headers), Rows (creates row headers), and Values (data to summarize)

7

When a field is placed in the Values area, Excel performs a SUM or COUNT by default; right-click to change to average, max, etc.

8

Use the 'PivotTable Analyze' and 'Design' tabs at the top of the PivotTable for further formatting and customization

Common Use Cases

Sales Analysis

Analyze sales performance by region, product, time period, or any combination of these dimensions

Financial Reporting

Create dynamic financial reports that can be easily updated and modified

Customer Analysis

Understand customer behavior patterns and segment performance

Inventory Management

Track stock levels and movement across different locations and categories

Pro Tips & Best Practices

  • 1Use well-structured source data with clear column headers and no blank rows
  • 2Format your source data as an Excel Table (Insert > Table) so your PivotTable will automatically update to include newly added rows
  • 3Right-click on the PivotTable and select 'Refresh' to update the data, or set it to refresh automatically when the file opens
  • 4Use 'Value Field Settings' to customize how values display (totals, percentages, differences, etc.)
  • 5Add 'Calculated Fields' (PivotTable Analyze > Formulas > Calculated Field) to create new calculations based on existing fields
  • 6Create multiple PivotTables to analyze the same data from different perspectives
  • 7Use 'Slicers' and 'Timelines' (Insert tab) to provide interactive filtering controls
  • 8Double-click on cells in the Values area to see the detailed data that makes up that summary
  • 9Visualize your PivotTable with a 'PivotChart' (PivotTable Analyze > Tools)

Frequently Asked Questions about Pivot Tables

Common questions and solutions for Excel Pivot Tables

Pivot Tables don't automatically update when source data changes. You need to refresh the Pivot Table by right-clicking on it and selecting 'Refresh', or by clicking the 'Refresh All' button in the Analyze/Options tab. To auto-refresh, store your data in an Excel Table (Insert > Table), then create your Pivot Table from this Table, which will automatically expand as new data is added.

Right-click on any value in the Values area, select 'Show Values As', then choose 'Percent of Grand Total' (for percentage of all data), 'Percent of Column Total' (for percentage within each column), or 'Percent of Row Total' (for percentage within each row). This allows you to see relative proportions rather than absolute values.

Excel automatically groups dates in Pivot Tables. To change this behavior: 1) Right-click on a date field in the Row/Column area, 2) Select 'Group', 3) Choose your preferred grouping (Years, Quarters, Months, etc.), or 4) To see exact dates, select 'Ungroup'. Alternatively, format the source data column as text before creating the Pivot Table to prevent automatic grouping.

To add a calculation not in your source data: 1) Click anywhere in the Pivot Table, 2) Go to PivotTable Analyze/Options tab, 3) Click 'Fields, Items & Sets', then 'Calculated Field', 4) Name your field and enter a formula using existing fields, 5) Click Add and OK. This lets you create custom metrics like profit margins or performance indices directly in your Pivot Table.

Standard Pivot Tables only offer Count or Count Numbers, which count all occurrences. For unique counts: 1) In Excel 365 or 2021, use the UNIQUE function in your source data, 2) In Excel 2019 with Power Pivot, use the DISTINCTCOUNT measure, 3) In older versions, use a helper column with a COUNTIF formula to identify unique occurrences before creating your Pivot Table, or 4) Use the Data > Remove Duplicates command to create a filtered list first.

Create a Timeline: 1) Click anywhere in your Pivot Table, 2) Go to PivotTable Analyze/Options tab, 3) Click 'Insert Timeline', 4) Select your date field and click OK. The Timeline allows you to filter by various time periods (years, quarters, months, days) with an interactive slider. Alternatively, use Slicers with date fields or create report filters with dynamic ranges.

By default, Pivot Tables show zeros for empty cells. To display blanks instead: 1) Right-click anywhere in the Pivot Table and select 'PivotTable Options', 2) Go to the Layout & Format tab, 3) Under Format, check 'For empty cells show:' and leave the box blank, 4) Click OK. This makes your Pivot Table cleaner and easier to read by showing only actual data.

Create a Pivot Cache: 1) Create your first Pivot Table normally, 2) For the second Pivot Table, select Insert > PivotTable, 3) Choose 'Use an external data source', 4) Click 'Choose Connection', 5) Go to the Workbook Connections tab, 6) Select the existing Pivot Table connection and click OK. This creates multiple Pivot Tables that share the same cache but can be filtered independently, improving performance and reducing file size.