Pivot Table Excel Data Analysis: A Practical Guide

Charts & pivot tables · Updated November 21, 2026

A PivotTable in Excel summarizes and explores a dataset without changing its original rows. To create one in Microsoft 365 for Windows, select a cell in your data, choose Insert > PivotTable, pick a location, then arrange fields in Rows, Columns, Values, and Filters to answer a question.

What a PivotTable Does

A PivotTable is an interactive report built from a rectangular source range or Excel table. It groups records and calculates summaries, such as total sales by region, order counts by month, or average delivery time by product. You can rearrange fields to investigate the same records from different angles, rather than creating a separate formula-based report for every question.

The name can be misleading: a PivotTable does not rotate or rewrite the underlying data. It changes how the summary is organized. For instance, a sales report can show regions down the left and product categories across the top; moving Product from Columns to Rows changes the view while leaving the source records intact.

PivotTables are most useful when a dataset contains repeated categories and many individual records. A list of transactions can be summarized by customer or date, while a short list with no repeated categories may gain little from a PivotTable. The tool is for analysis and reporting, not for repairing incorrect source data.

Prepare the Source Data

Good source data makes the PivotTable easier to build and less likely to produce confusing results. Arrange records in a single table: one header row, one record per row, and one field per column. For example, a sales list might have Order Date, Region, Product, Salesperson, Units, and Revenue columns.

  • Give every column a unique, nonblank heading. Avoid merged cells and multirow headings.
  • Keep each column consistent: dates should be dates, amounts should be numbers, and categories should be text.
  • Remove completely blank rows and columns within the dataset. Check for stray spaces and spelling variations in categories such as North and Noth.
  • Keep subtotals and grand totals out of the source records. The PivotTable can calculate summaries itself.

For a growing dataset, convert the source range to an Excel table first: select a cell in the records and choose Insert > Table, then confirm that the table has headers. A table expands when new rows are added, so it is a more dependable PivotTable source than a fixed range. New records still need to be included in the PivotTable by refreshing it.

Create a PivotTable in Excel

In Excel for Microsoft 365 on Windows, click a cell in the source data and choose Insert > PivotTable. Excel identifies the current range or table; check that the selection includes all intended records and headers. In the dialog, choose New Worksheet for a separate report or Existing Worksheet and specify a destination cell, then select OK.

The blank PivotTable appears alongside the PivotTable Fields pane. Drag or check fields to place them in the four layout areas. Excel may put a checked text field in Rows and a numeric field in Values automatically, but you can rearrange either choice. The report updates as you move fields.

On Excel for Mac, the core workflow is also available from Insert > PivotTable, although dialog and ribbon labels can differ somewhat by release. Excel for the web supports creating and working with PivotTables, but some advanced features and controls available in the desktop application may be absent or behave differently. If a specific command is not visible, check the relevant desktop version rather than assuming the source data is at fault.

Understand the Four Field Areas

Each field area answers a different part of a report question. Rows usually contain categories to compare, such as Region or Product. Columns place another category across the top, such as Year or Sales Channel. Values contain the figures or counts to summarize. Filters let a reader limit the whole report to a selected item, such as one department.

For example, place Region in Rows, Product in Columns, and Revenue in Values to compare product revenue across regions. Add Order Date to Filters to limit the report to a particular date or period. You can also nest fields: put Year above Region in Rows to create year-by-year regional subtotals.

When a field is in Values, open its field menu and choose Value Field Settings to change the calculation. Common choices include Sum, Count, Average, Max, and Min. Excel often sums numeric data and counts text data, but mixed values or unexpected blanks can lead it to choose Count instead of Sum. Check the setting and the source column when the result is surprising.

Work Through a Concrete Example

Imagine a worksheet with one row per sale and the columns Order Date, Region, Product, Units, and Revenue. A manager asks which products contribute most revenue in each region. Put Region in Rows, Product in Columns, and Revenue in Values. Check that the Values field says Sum of Revenue. The resulting grid gives each region’s total for each product, plus row and column totals if grand totals are enabled.

To focus on a particular year, add Order Date to Filters or use a date field in Rows and group it by year when the grouping option is available. To compare sales volume rather than value, add Units to Values as well. A second value field can show a different measure for the same row-and-column categories.

This is an aggregate report: it explains totals, not the reason a product sold more. A high revenue total might reflect many units, a higher price, or both. The underlying records remain available for follow-up, and a PivotTable can help identify which combinations deserve closer investigation.

Explore Patterns with Filters and Calculations

Use the field drop-downs to filter individual row or column categories. For a more visible, clickable filter, select a PivotTable and choose PivotTable Analyze > Insert Slicer, then choose fields for the slicer. A date-based PivotTable can also use PivotTable Analyze > Insert Timeline where supported. Slicers and timelines make it easier for report readers to change views without opening small filter menus.

Values can be displayed in different ways as well as summarized. In Value Field Settings, the Show Values As tab offers options such as percentage of a grand total, percentage of a row total, or running total for supported layouts. These options change the display calculation, not the source values. A percentage of grand total is useful for showing contribution, while a running total can show cumulative progress over time.

Use these displays carefully: a percentage is meaningful only when its denominator matches the question. If a report is filtered to one region, its percentage of the visible total may differ from its share of all company sales. State the scope of the report clearly when sharing it.

Format and Share the Report

With a PivotTable selected, use the PivotTable Design tab to choose a style, adjust banded rows or columns, and control subtotals and grand totals. For number formatting, open Value Field Settings and choose Number Format. Formatting the value field there is preferable to formatting individual cells, because the field-level format is more likely to remain consistent when the PivotTable changes.

Use clear labels and a descriptive report title in a nearby cell. Rename a displayed value label such as Sum of Revenue to a reader-friendly heading, while keeping it distinct from any source field name. A compact report is easier to read, but do not remove totals or filters that readers need to interpret the results. For a presentation, consider copying the PivotTable and pasting values into a separate sheet only when a static snapshot is appropriate; that copy will not respond to filters or refreshes.

Refreshes, Limitations, and Common Problems

A PivotTable generally does not update its cached summary immediately when source records change. Select a PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables. If new records fall outside a fixed source range, change the source using PivotTable Analyze > Change Data Source. Using an Excel table as the source helps avoid that range-extension problem, but a refresh is still required.

  • Unexpected Count instead of Sum: inspect the source column for text-formatted numbers, blanks, or mixed content, then set the calculation in Value Field Settings.
  • Missing new records: refresh the report and verify that the source includes those rows. A fixed source range may need to be expanded.
  • Duplicate-looking categories: check for leading or trailing spaces, spelling differences, and inconsistent punctuation in the source.
  • Dates will not group: confirm that every entry is a valid Excel date, rather than text, and look for blanks or errors that interrupt grouping.
  • Old items remain in a filter: cached items can persist after source values are removed. In PivotTable Options, check the Data settings for the number of items to retain per field, then refresh.

Another limitation is that a conventional PivotTable summarizes fields already present in its source; it is not a substitute for cleaning data or defining a sound measure. A Data Model PivotTable can use relationships among tables and offers a Distinct Count calculation when the data is added to the Data Model. Availability of Data Model and Power Pivot tools varies by Excel edition and platform, so do not expect every Windows desktop option in Excel for the web or on Mac.

PivotTables Compared with Formulas and Charts

PivotTables are a strong choice when the question changes often and the user needs to regroup, filter, or drill into categories. They require little formula writing and can summarize thousands of records into a compact view. Their trade-off is that the report is a separate summary that needs refreshing and may be less flexible than a custom-designed formula model.

Formulas such as SUMIFS are useful when a report layout must remain fixed, calculations need to be embedded in a larger model, or results should update immediately as source cells change. They take more planning and can be harder to maintain when the categories or dimensions change. Charts complement either approach: a PivotChart can visualize a PivotTable and respond to its filters, while a standard chart may be preferable for a fixed presentation. Neither chart type replaces checking the underlying figures.

Frequently Asked Questions

What is a PivotTable in Excel used for?
A PivotTable quickly groups and summarizes records, such as calculating sales by region, counts by category, or monthly averages. It lets you change the report layout without rearranging the source data.

How do I add new data to an Excel PivotTable?
Add records to the source, then refresh the PivotTable. If its source is an Excel table, new rows are included in the table automatically; if the PivotTable uses a fixed range, expand the source through PivotTable Analyze > Change Data Source before refreshing.

Why does my PivotTable show Count instead of Sum?
Excel may use Count when the selected field contains text, blanks, or inconsistent data rather than numbers throughout. Check the source values and select Sum in Value Field Settings if the field is genuinely numeric.

Can a PivotTable show percentages instead of totals?
Yes. Open Value Field Settings, select Show Values As, and choose a suitable option, such as percentage of grand total. The choice changes how the summary is displayed; it does not convert or alter the original source values.

Do PivotTables update automatically?
Usually, changes to source cells do not immediately recalculate an existing PivotTable. Refresh it with PivotTable Analyze > Refresh or use Data > Refresh All. Workbook settings and connections can affect refresh behavior, so verify the displayed results before sharing a report.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.