Excel Pivot Tables: Analyze Data with Examples and Insights

Charts & pivot tables · Updated November 18, 2026

Yes—an Excel PivotTable analyzes a list by summarizing its values and letting you rearrange, filter, group, and compare them without changing the original data. In Microsoft Excel for Microsoft 365 on Windows, select a cell in the data, choose Insert > PivotTable, and place fields in Rows, Columns, Values, or Filters to build a report.

What a PivotTable Can Tell You

A PivotTable is an interactive summary of tabular data. Instead of manually adding separate totals for each product or region, you assign fields to a layout and Excel calculates the summaries. You can then move fields around to ask a different question of the same source—for example, switching from sales by region to sales by month and product.

Imagine a sales list with one row per order and columns for Order Date, Region, Product, Salesperson, and Revenue. A PivotTable could total revenue by region, show product totals within each region, count orders by salesperson, or display monthly results. These are different views of the same records, not copies of the underlying transactions.

PivotTables are useful for exploration and recurring summaries, especially when the source contains many rows. They do not decide what a result means: the analyst still needs to choose appropriate fields, check how values are summarized, and interpret the result in context.

Prepare the Source Data

A clean source list makes a PivotTable easier to build and less likely to produce misleading categories. Keep the source in a simple rectangular layout: one header row, one record per row, and one type of information per column. Avoid merged cells, blank header names, and subtotal rows mixed into the records.

  • Give every column a clear, unique heading, such as Region or Revenue.
  • Keep each column consistent. For example, store dates as actual Excel dates rather than a mixture of dates and text that only looks like dates.
  • Check for spelling variants such as North, north, and Noth. Excel treats different text values as separate categories.
  • Remove unnecessary blank rows and columns inside the data area, and review errors or unexpected blanks.
  • For a source that will grow, convert it to an Excel Table with Insert > Table or Ctrl+T, then confirm that the range has headers.

Using a Table is practical because new rows added directly beneath it become part of the table. After adding records, refresh the PivotTable so its summary reflects them. A PivotTable based on a fixed ordinary range may not include records added outside that original range.

Create a PivotTable

On Windows, click a cell in the source list or Table, then choose Insert > PivotTable. In the dialog, confirm the table or range, choose whether to put the PivotTable on a new worksheet or an existing one, and select OK. The PivotTable Fields pane appears beside a blank report area.

Drag field names into the four layout areas, or select their checkboxes and adjust their placement. A typical first report might put Region in Rows and Revenue in Values. Excel creates a total for each region. To see products within each region, drag Product below Region in Rows. To lay products across the top instead, put Product in Columns.

For a more repeatable source, create a Table first and use its name as the PivotTable source. The exact dialog can vary slightly when choosing an external connection or another source type, but the core process for a worksheet list is the same. On Excel for Mac, use the Insert tab and PivotTable command; ribbon placement and some dialog details can differ by release. Excel for the web also supports PivotTables, though some creation and layout options may differ from the desktop app.

Understand the Four Field Areas

The field areas determine how a summary is organized. Rows list categories down the report. Columns create categories across it. Values contain the numbers or counts Excel calculates. Filters apply a selection to the whole report. A field can be moved between areas at any time, so the layout is a way to ask a question rather than a permanent data transformation.

  • Rows: Use for categories such as Region, Department, or Product.
  • Columns: Use for a second category when side-by-side comparison is useful, such as quarter or sales channel.
  • Values: Use for measures such as Revenue, Quantity, or a field whose records you want to count.
  • Filters: Use for an overall restriction, such as viewing one year or business unit at a time.

For example, Region in Rows, Product in Columns, and Revenue in Values produces a grid of revenue by region and product. If the grid becomes too wide, place Product beneath Region in Rows instead. A compact layout may be easier to read and print, while a cross-tab can make comparisons across a small number of categories more visible.

Choose the Right Summary Calculation

Excel commonly sums numeric fields added to Values and counts text fields. That default is not always the question you intend to answer. Open the menu for a value field and choose Value Field Settings to select a calculation such as Sum, Count, Average, Maximum, or Minimum. Available options depend on the source and the type of field.

Suppose the source contains one row per order, with Revenue in one column. Sum of Revenue answers how much revenue is recorded; Count of Revenue counts nonblank revenue entries. Average of Revenue gives the mean value per included record, not the average by customer unless the source has already been summarized to one row per customer. Knowing the level of each source row prevents common interpretation errors.

Value Field Settings also includes Show Values As options, such as percent of the grand total or difference from a selected base item. These change how displayed values are expressed; they do not change the source records. For example, a revenue total shown as a percentage of the grand total answers what share each category contributes, while the underlying sum remains the same. Check both the calculation and its number format before sharing the report.

Compare Categories, Dates, and Shares

Nested row fields let you move from an overall result to detail. With Region above Product in Rows, expand a region to see its products; collapse it to return to the regional summary. Subtotals and grand totals can help show how category values relate to the overall result, but they may be distracting in a dense report. Use the PivotTable Design tab options to adjust report layout and subtotals when needed.

Excel can group compatible date fields into periods such as months, quarters, or years. Right-click a date item in the PivotTable and choose Group, then select the desired intervals. Grouping depends on the source dates being recognized as dates; text values or blanks in a date field can prevent grouping or cause inconsistent results. Numeric fields can also be grouped into intervals in appropriate cases, which can help create bands such as value ranges.

To compare proportions, use Show Values As rather than confusing a raw total with a share. To compare time periods, put a date field in Rows or Columns and choose a suitable grouping. Keep the question explicit: a percentage of grand total, a percentage of row total, and a month-to-month difference describe different things.

Filter and Visualize the Report

Use the filter dropdowns on row and column labels to include or exclude categories. A field in the Filters area adds a report-level control above the PivotTable. For a more visible interactive control, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then choose fields for buttons such as Region or Product. A slicer can make the current selection easier for another person to see.

For date fields, PivotTable Analyze > Insert Timeline can provide a date-range control in supported desktop versions. A PivotChart can turn the summary into a chart that follows the PivotTable layout and filters. Choose a chart type that fits the comparison: bars or columns are often readable for category totals, while a line chart can help show a sequence over time. A chart does not fix a confusing layout, so label the measure and make the selected filters clear.

On Windows, the PivotTable Analyze and Design tabs appear when a PivotTable is selected. Their names and available controls can vary by platform and Excel release. On Mac, look for the contextual PivotTable tabs after selecting the report; where a command is not in the same ribbon location, the command search feature can help locate it.

Refresh Data and Fix Common Problems

A PivotTable stores a summarized view based on its source. It does not necessarily update immediately when source cells change. Right-click inside the PivotTable and choose Refresh, or use Data > Refresh All to update connected data and PivotTables. If newly added records are missing, check whether the source is an expanding Excel Table or whether the PivotTable source range needs to be changed.

  • Unexpected Count instead of Sum: Check whether the source values are stored as numbers consistently. Text-formatted numbers or mixed entries can make a field behave differently.
  • Duplicate-looking categories: Inspect spaces, spelling, punctuation, and inconsistent capitalization in the source text.
  • Dates will not group: Look for blanks, text dates, or invalid values in the date column, correct the source, and refresh.
  • New rows do not appear: Confirm the source range includes them. An Excel Table helps when records are appended to the table.
  • The result seems inflated: Check whether the source contains duplicate records, whether the intended calculation is Sum or Count, and whether each row represents the expected unit.

When making structural changes to the source—such as adding a field—refresh and confirm the field is available in the field list. If the report still does not reflect the new structure, inspect Change Data Source from the PivotTable Analyze tab. Refreshing updates the report from its source; it does not repair inaccurate source data.

Limits and Alternatives

A PivotTable is not a substitute for a well-designed data model or a clear source list. It summarizes fields that exist; it cannot infer missing categories, correct inconsistent records, or establish causation. Its totals can also be misleading if the source has duplicate rows, if each row represents a different unit, or if a field is summarized using the wrong calculation.

For a small, fixed summary, SUMIFS, COUNTIFS, or a straightforward chart may be simpler, particularly when a report needs a precise custom layout. Excel Tables and filters may be enough when the goal is to inspect records rather than aggregate them. Power Query is useful for importing, cleaning, and reshaping repeatable data sources; a PivotTable can then summarize the prepared results. Choose based on whether the main task is transforming records, calculating a fixed result, or exploring summaries interactively.

One misconception is that a PivotTable changes or rearranges the source list. Its report is separate from the source data, so moving fields in the report does not reorder the original records. Another is that a PivotTable is automatically a live report: changes to source values typically require a refresh before the displayed summary is current.

Frequently Asked Questions

How do I analyze data with a PivotTable in Excel?
Select a cell in a clean source list, choose Insert > PivotTable, and place categories in Rows or Columns and measures in Values. Add filters or slicers to focus the report, then verify the value calculation and refresh after source changes.

Why is my PivotTable counting instead of adding?
Excel may be treating some entries as text, or the field may contain mixed data types. Check the source column for text-formatted numbers and inconsistent values, correct them, refresh the PivotTable, and select Sum in Value Field Settings if it is appropriate.

How do I add new data to an existing PivotTable?
If the source is an Excel Table, add the records to the table and refresh the PivotTable. If the source is a fixed range, update its source through PivotTable Analyze > Change Data Source, then refresh.

Can a PivotTable show percentages instead of totals?
Yes. Open Value Field Settings for the value field and choose a relevant option under Show Values As, such as percent of grand total. Confirm whether the intended denominator is the grand total, a row, or a column before interpreting the percentages.

Can I use a PivotTable to analyze dates by month?
Yes, if Excel recognizes the source entries as dates. Put the date field in Rows or Columns, right-click a date item, choose Group, and select Months or another suitable interval. Correct blanks or text dates first if grouping is unavailable.

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.