Excel Pivot Table Help: Create, Fix, and Analyze Data

Charts & pivot tables · Updated November 21, 2026

To create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data range, choose Insert > PivotTable, confirm the range, and select where to place the report. Then drag fields into Rows, Columns, Values, and Filters to summarize the data. A PivotTable reorganizes a list into an interactive summary without changing the original records.

What a PivotTable Does

A PivotTable helps answer questions about a dataset by grouping records and calculating summaries. For example, a sales list with one row per order can be turned into a report showing total sales by region, product, or month. You can move a field from Rows to Columns to change the layout, or add another field to see a more detailed breakdown.

The word “pivot” describes the ability to rearrange the same fields quickly. The underlying records remain in the source range or Excel Table; the PivotTable displays a separate summary. It is useful when you need to explore several views of a list without writing formulas for every subtotal.

  • Rows create categories down the left side, such as regions or names.
  • Columns place categories across the top, such as years or order status.
  • Values hold calculations, such as sums, counts, or averages.
  • Filters let you limit the report to selected items, such as one department.

A PivotTable is not a replacement for a well-structured source list. It works best when each column has a single heading and each row represents one record, such as one order line or one survey response.

Prepare the Source Data

Before inserting a PivotTable, check that the source data is rectangular: one header row followed by records, with no completely blank rows or columns dividing the list. Give every column a unique, nonblank heading. Avoid merged cells, multi-row headings, and manually inserted subtotal rows; these layouts make it harder for Excel to identify consistent fields.

Data types should also be consistent within a column. If a Sales column contains numbers stored as text, Excel may count the values instead of summing them. Dates stored as text may fail to group into months or years. Check for leading spaces, inconsistent spellings such as “North” and “Northeast,” and blank entries that might represent missing information rather than a meaningful category.

For a list that will grow, convert the source range into an Excel Table first. Select a cell in the data and choose Insert > Table, confirm that the table has headers, and select OK. Tables typically expand when new rows are added, making them a convenient PivotTable source. The PivotTable still needs to be refreshed to include those new records.

Create a PivotTable in Excel

  1. Select a cell in the source list or Excel Table. You do not need to highlight the entire list if it is a contiguous block of data.
  2. Choose Insert > PivotTable. In the dialog, check the suggested table or range. Choose New Worksheet for a separate report, or Existing Worksheet and a destination cell if the report belongs on a current sheet.
  3. Select OK. Excel creates an empty PivotTable and displays the PivotTable Fields pane.
  4. Drag fields into the layout areas, or select their check boxes and review where Excel places them. Put categories in Rows and numbers to summarize in Values.
  5. Arrange, filter, and format the report, then refresh it after changing the source data.

For a basic example, imagine columns named Order Date, Region, Product, and Sales. Put Region in Rows, Product in Columns, and Sales in Values. Excel will usually summarize numeric Sales as a sum. The result shows sales by product within each region, with row and column totals when those totals are enabled.

Excel for Mac has PivotTable creation and field-layout features, although some ribbon labels and pane details may differ from Windows. Excel for the web also supports creating and working with PivotTables, but particular analysis and data-model features may not be available in the browser. If a command or option is missing, open the workbook in the desktop app and check the relevant PivotTable tab.

Choose the Right Summary and Layout

When a field is placed in Values, Excel chooses a summary based on the field’s contents. Numeric data is commonly summarized by Sum, while text or mixed data is commonly summarized by Count. To change it, select a value in the PivotTable, open PivotTable Analyze > Field Settings, and choose the calculation on the Summarize Values By tab. Depending on the field and Excel version, options include Sum, Count, Average, Max, and Min.

Use Count when the question is how many records exist, not how much they total. For instance, counting Order ID can show the number of order lines per region. Be clear about what is being counted: if one order can contain several product lines, counting rows is not necessarily the same as counting unique orders. A distinct count is available when creating a PivotTable with the data added to the Data Model; it is not the same as the regular Count option.

For percentages, select a value field and use Show Values As to display options such as percentage of a row total, column total, or grand total. This changes how the values are displayed, not the source figures. For example, percentage of row total can show each product’s share of a region’s sales. Apply number formatting through the value field settings so the format remains attached when the PivotTable updates.

Keep the layout suited to the question. A report with Region in Rows and Month in Columns is good for comparing trends, but too many column categories can make it wide and hard to read. Place a field in Filters for a simple report-wide selection, or use a slicer for a more visible, clickable filter.

Refresh, Expand, and Update the Report

A PivotTable does not automatically recalculate every time its source records change. After editing or adding source data, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh all PivotTables in a workbook, use Data > Refresh All. Excel for Mac and the web provide refresh commands too, though their ribbon placement can vary.

If the source is an Excel Table, adding records to the table makes them available as source data, but refresh the PivotTable to include them in the summary. If the source is an ordinary range and new rows fall outside the original selection, change its source through PivotTable Analyze > Change Data Source, or create the PivotTable from a Table to reduce this maintenance.

To refresh data when the workbook opens, right-click the PivotTable, choose PivotTable Options, and look for the Data tab’s refresh-on-open setting. The exact dialog labels can vary by platform. Refreshing may change displayed totals and categories, so review the report if it is used for a recurring presentation.

Filters, Groups, Slicers, and PivotCharts

Use the dropdown beside a row or column label to select which items appear. A report filter applies a selection to the whole PivotTable. Slicers offer buttons for filtering fields: select the PivotTable, choose PivotTable Analyze > Insert Slicer, select the fields, and choose OK. A timeline can provide a visual date filter when the source contains dates recognized by Excel.

Grouping can make long lists easier to read. For example, right-click a date in the PivotTable and choose Group to group by months, quarters, or years when Excel recognizes the values as dates. You can also group selected numeric items into ranges. If Group is unavailable or produces unexpected results, check for blank or text entries in the source field; a single invalid value can prevent date grouping.

A PivotChart is linked to a PivotTable and changes as the report is filtered. Select a cell in the PivotTable and choose PivotTable Analyze > PivotChart, then choose a chart type. PivotCharts are useful for exploring summarized data, but their field buttons and filtering behavior differ from those of ordinary charts. For a fixed chart layout, a regular chart based on a carefully arranged summary may offer more formatting control.

Common PivotTable Problems and Fixes

  • Excel counts instead of sums: The source field may contain numbers stored as text or a mixture of numbers and text. Correct the source values consistently, then refresh. If Count is actually intended, keep it and confirm the report’s meaning.
  • New rows are missing: Refresh the PivotTable and check whether its source includes the new rows. Use an Excel Table as the source for a list that grows over time.
  • Months will not group: Verify that the source column contains real Excel dates, with no blanks or text values that resemble dates. Correct the source, refresh, and try grouping again.
  • Old items remain in a filter: A PivotTable may retain items that were previously in its source. Check PivotTable Options for the number of items to retain per field, set it to None if appropriate, and refresh.
  • Totals look duplicated or inflated: Confirm what each source row represents and whether the selected fields summarize the right level of detail. Repeated order totals on every product line, for example, can be summed more than once.
  • Field list is missing: Select the PivotTable and use PivotTable Analyze > Field List to show the pane. If the pane is visible but fields are missing, check the source range and header row.

A PivotTable reports what is present in the source; it does not determine whether those records are accurate or whether a particular calculation answers the business question. Check a few subtotals against the source list, especially when source data has duplicates, partial records, or unusual exceptions.

PivotTables Compared with Formulas and Other Tools

Choose a PivotTable when you want to summarize a list interactively, rearrange categories, and filter the same data in several ways. It can be faster to build and revise than a grid of formulas, particularly when the fields or questions may change. The tradeoff is that PivotTables have their own layout and refresh behavior, which users must understand.

Formulas such as SUMIFS are often better when a report must have a fixed layout, update immediately as source values change, or feed other calculations cell by cell. Excel Tables, charts, and dynamic array functions can also help with different reporting tasks. Power Query is useful for importing and reshaping data before analysis; a PivotTable can then summarize the cleaned result. These tools can work together rather than being mutually exclusive.

For a small, one-off list, sorting, filtering, and using the Total Row in an Excel Table may be enough. For repeated reporting across large or multiple related tables, consider whether the Data Model or Power Query is appropriate. The best choice depends on how the data is structured, how often it changes, and how much interaction the report needs.

Frequently Asked Questions

Why is my PivotTable not updating automatically?
PivotTables generally need to be refreshed after source data changes. Right-click inside the report and choose Refresh, or select Data > Refresh All. Confirm that new records fall within its source range or are part of the source Excel Table.

Can I add new data without rebuilding the PivotTable?
Yes. Add records to the source Excel Table, then refresh the PivotTable. If the source is a fixed cell range and the new rows fall outside it, update the source with PivotTable Analyze > Change Data Source or switch to a Table-based source.

How do I show a percentage instead of a total?
Open the value field’s settings and choose an appropriate option under Show Values As, such as percentage of row total or grand total. This displays the existing summarized values as proportions; it does not alter the source data.

Can I edit a value directly in a PivotTable?
No. A PivotTable displays a summary of its source and is not a place to edit an individual source record. Change the value in the source data and refresh the report. A calculated result can be adjusted by changing the source or the PivotTable’s available calculation settings.

Can I make a PivotTable from multiple tables?
Yes, Excel can use the Data Model to analyze related tables, provided the tables have suitable columns for relationships. This involves a more advanced setup than selecting one range; for a straightforward summary, a single well-prepared table is usually simpler.

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.