An Excel PivotTable is a tool for summarizing and exploring a larger list of data without changing the original records. You choose fields such as category, date, or sales amount, then arrange them to calculate totals, counts, averages, and other summaries that can be rearranged as questions change.
What an Excel PivotTable is for
A PivotTable turns detailed rows into a compact report. Imagine a worksheet containing one row for every order, with columns for order date, region, salesperson, product, quantity, and revenue. A PivotTable can show revenue by region, then break each region down by salesperson, without requiring a separate formula for every group.
The word “pivot” describes the ability to change the view: move a field from rows to columns, add a filter, or replace a revenue total with a count. The source data remains in its original table or range. The PivotTable is a separate summary, not a transformation that overwrites or cleans the underlying records.
It is especially useful when you need to answer several questions from the same dataset, such as which products sell most, how results differ by month, or how many orders came from each region. It can also help reveal patterns that are hard to see in thousands of individual rows.
How PivotTables organize fields
When you select a cell in a PivotTable, Excel displays a field list. Each source column appears as a field that you can place into one of four areas. The arrangement determines the report’s structure:
- Rows: Lists categories down the left side, such as product or salesperson.
- Columns: Places categories across the top, such as year or region.
- Values: Calculates a summary from a field, often a sum of revenue or a count of orders.
- Filters: Adds a report-level filter so you can show one selection, such as a particular sales channel.
For example, put Region in Rows, Product in Columns, and Revenue in Values. Excel creates a grid of revenue totals by region and product, usually with row and column totals. Add Order Date to Filters to narrow the view to a chosen period. You can drag a field between areas or remove it from the layout without changing the source records.
For numeric fields, Excel commonly chooses Sum. If it cannot treat the values as numbers—for example, because amounts are stored as text—it may choose Count instead. To change the calculation, open the drop-down beside the field in Values, choose Value Field Settings, and select an option such as Sum, Count, or Average. The exact available summaries depend on the data type.
Prepare the source data first
A clear source table makes a PivotTable more reliable. Use one header row with a unique, nonblank label for every column, and keep each record on one row. Avoid merged cells, completely blank rows or columns inside the data, and subtotal rows mixed into the records. Each column should contain one kind of information: dates in a date column, amounts in an amount column, and categories in a category column.
Convert a clean dataset into an Excel Table by selecting a cell in it and choosing Insert > Table. Confirm that My table has headers is selected. Tables are convenient because they expand when you add records directly below them, and PivotTables based on the table can include those added rows after a refresh. A PivotTable built from a fixed cell range may not include records added beyond that range.
Check that dates are genuine Excel dates rather than text that only looks like a date, and that numeric values are numbers rather than text. A useful clue is alignment: numbers are often right-aligned by default, while text is often left-aligned, though formatting can change that appearance. Look for inconsistent category spellings too. “North”, “north”, and “North ” may be treated as separate labels, producing separate rows in the summary.
Create a PivotTable in Excel for Microsoft 365
In Excel for Microsoft 365 on Windows, use these steps to create a basic PivotTable:
- Click a cell in the source data. If it is not a contiguous list, select the complete range you want to summarize.
- Choose Insert > PivotTable.
- In the dialog, check the table or range shown. Choose New Worksheet for a separate report sheet, or select Existing Worksheet and specify a destination cell.
- Select OK. Excel creates an empty PivotTable and opens the PivotTable Fields pane.
- Drag fields into Rows, Columns, Values, or Filters. Adjust the layout until it answers the question you have.
Microsoft Excel for Mac also supports PivotTables, though ribbon and dialog details can differ by release. Excel for the web offers PivotTable features as well, but some advanced controls and data-model options may require opening the workbook in the desktop app. If a field list is hidden, click inside the PivotTable and look for the PivotTable Analyze tab or the field-list control on the ribbon.
Choose the right summary and layout
The best layout depends on the question, not simply on which fields are available. For a quick ranking, place Product in Rows and Revenue in Values, then sort the results from largest to smallest. For a time comparison, put Month or Order Date in Rows and Revenue in Values. For a cross-tabulation, use Region in Rows and Year in Columns. Keep the report focused: adding many fields can make it harder to spot the answer.
To sort, use the menu on a row or column label and choose a sort option. To restrict the categories displayed, use the filter arrow beside the relevant label. For interactive filtering, choose PivotTable Analyze > Insert Slicer and select a field. A slicer presents clickable buttons for its values; it is often easier for another person to use than a small drop-down filter. Slicers are available in supported desktop versions, while the exact controls can vary in Excel for the web.
For dates, Excel may group a date field automatically, or you can right-click a date in the PivotTable and choose Group to group by options such as months, quarters, or years. Grouping requires usable date values; text dates or blanks can prevent it. To display a percentage rather than a raw amount, open Value Field Settings, then use Show Values As to select a comparison such as percentage of a total. This changes how the summary is displayed, not the underlying source values.
Refreshes, updates, and source changes
A PivotTable does not necessarily recalculate its summary the moment you edit a source record. After changing or adding source data, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update PivotTables in the workbook together, use Data > Refresh All. The field layout generally remains in place while Excel recalculates the summary.
Refreshing is different from changing the PivotTable’s source. If new records sit outside a fixed source range, refresh alone may not bring them in. Use PivotTable Analyze > Change Data Source to select a range that includes them, or base the PivotTable on an Excel Table that expands with new rows. Check the source when a refresh seems to ignore recently added data.
Some workbooks can be set to refresh data when the file opens, but that setting depends on the connection and workbook setup. Refreshing can also take time for large sources or external connections. If a report is shared, remember that recipients may need access to the source connection to refresh externally linked data.
Common problems and practical fixes
A few frequent issues have straightforward causes. If a value appears as a count instead of a sum, inspect the source column for numbers stored as text, blank cells, or mixed data types. Correct the source values and refresh. If the total seems unexpectedly high, verify that each source row represents the intended unit—for example, one order line rather than one entire order—and that duplicate records are not present.
- “Cannot determine which field contains the data” or an unclear field name: Check for blank or duplicate headers and make each header distinct.
- New records do not appear: Check whether the PivotTable uses a fixed range that stops before the new rows, then expand the source or use an Excel Table.
- Unexpected categories appear separately: Standardize spelling and remove leading or trailing spaces in the source data.
- Date grouping fails: Confirm that the source column contains real dates and does not contain text dates or blanks.
- Old items remain in a filter after a refresh: Review PivotTable options for retained items, or check whether the source still contains those labels.
Formatting can also be confusing. Number formatting applied to a value field is often more stable than formatting individual displayed cells. Open Value Field Settings, choose Number Format, and set the desired currency, number, or percentage format there. A PivotTable’s layout can change as fields are rearranged, so formatting a whole value field is less likely to leave some newly displayed cells looking different.
PivotTables compared with formulas and charts
A PivotTable is usually quicker than building many separate SUMIF or COUNTIF formulas when you need summaries across several categories and want to rearrange them interactively. Formulas are preferable when a report needs a fixed layout, custom calculations tied to specific cells, or results that update immediately as inputs change. A PivotTable’s summary needs refreshing after source edits unless the workbook’s settings or data connection handle that update.
Charts answer a different question: they make patterns visible, but they do not replace the underlying summary. You can create a PivotChart from a PivotTable using Insert > PivotChart in supported desktop Excel versions. It stays connected to the PivotTable, so filtering or rearranging the report can affect the chart. For a static chart with a carefully controlled series and layout, a regular chart may be more suitable.
PivotTables are also distinct from Power Query. A PivotTable summarizes and rearranges data; Power Query imports, reshapes, and cleans data through repeatable transformation steps. A common workflow is to use Power Query to prepare inconsistent source files, load the cleaned result, and then build a PivotTable for analysis. For very large or related datasets, the Excel Data Model can support relationships and additional analysis, but it adds setup complexity. The standard PivotTable is often the better starting point for a single, tidy table.
Limits and good habits
A PivotTable is not a substitute for auditing the source. It can calculate a mathematically correct total from records that are duplicated, incomplete, or categorized incorrectly. Check a small sample against the original rows, confirm the source range, and make sure the chosen summary matches the question. A sum of order-line revenue, for example, is not necessarily the same thing as a count of orders.
Keep the source data separate from the report, use descriptive field names, and give the PivotTable a clear title outside its changing summary area. If readers will use the report, explain what the values represent and when the data was last refreshed. For distinct counts or relationships across multiple tables, investigate the Data Model rather than assuming a standard Count provides unique records. Availability of Data Model features depends on Excel platform and setup; Windows desktop Excel offers the broadest set of controls.
Frequently Asked Questions
Is a PivotTable the same as a table in Excel?
No. An Excel Table organizes source records, adds structured headers, and can expand as rows are added. A PivotTable summarizes fields from a table or range and displays grouped results. A Table can be the source for a PivotTable, but the two features serve different purposes.
Does a PivotTable change or delete my original data?
No. Creating or rearranging a PivotTable does not edit the source records. It produces a separate summary. Editing the original source can change what the next refresh reports, so keep a copy or use normal workbook safeguards when the source matters.
Why is Excel counting my numbers instead of summing them?
Excel may see some or all entries as text, or the field may contain mixed or blank values. Inspect and correct the source column, ensure the values are numeric, and refresh the PivotTable. Then use Value Field Settings to select Sum if appropriate.
How do I update a PivotTable after adding rows?
Click inside it and choose PivotTable Analyze > Refresh, or choose Data > Refresh All. If the new rows are outside a fixed source range, expand the source through PivotTable Analyze > Change Data Source. Using an Excel Table as the source makes later additions easier to include.
Can a PivotTable show percentages or averages?
Yes. Use Value Field Settings to select Average instead of Sum or Count when that calculation fits the data. To display a value as a share of a total, use Show Values As in the same settings. The percentage is based on the PivotTable’s current grouping and filters.
When should I use a PivotTable instead of a formula?
Use a PivotTable when you want to summarize many records by changing categories, filters, or levels of detail quickly. Use formulas when the report needs a fixed structure, custom cell-by-cell logic, or immediate recalculation without a refresh. Many workbooks use both: PivotTables for exploration and formulas for a tailored final report.
Related
- Row Example in Excel: How Rows Work and How to Use Them
- ISERROR in Excel: How It Works and When to Use It
- ISBLANK in Excel: Examples and How It Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Box and Whisker Plot Excel X-Axis Labels ExplainedIn Excel for Microsoft 365 on Windows, box-and-whisker plot x-axis labels usually come from the headers above each…
- Excel Pivot Table: Percentage of Two ColumnsTo show one PivotTable column as a percentage of another in Excel for Microsoft 365 on Windows, add…
- Excel Cumulative Line Graph: How to Create OneTo make a cumulative line graph in Excel, add a running-total column beside your values, then chart the…
- Excel Pivot Chart: Hide Zero Values the Right WayYes. In Excel for Microsoft 365 on Windows, hide zero-value categories in a PivotChart by applying a value…
- Power Pivot Greyed Out? Causes and FixesIf Power Pivot is greyed out in Excel for Microsoft 365 on Windows, first check whether the Power…
- Linear Trendline in Excel: Make One and Show Its EquationTo make a linear trendline in Excel for Microsoft 365 on Windows, select your paired data, choose Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.