Pivot Table in Excel: What It Does and How to Use It

Charts & pivot tables · Updated November 21, 2026

A PivotTable in Excel is an interactive tool for summarizing and exploring data; it is not a worksheet function or formula. In Microsoft 365 for Windows, select a cell in your data, choose Insert > PivotTable, and arrange fields into rows, columns, values, or filters to build a report.

What an Excel PivotTable does

A PivotTable reorganizes a list of records into a compact summary. Instead of writing separate calculations for each region, product, or month, you choose which fields to summarize and Excel calculates the totals or other statistics. You can then rearrange the fields to look at the same records from a different angle.

For example, suppose a sales list has columns for Date, Region, Salesperson, Product, Units, and Sales. A PivotTable can show sales by region, then be changed to show sales by product or by salesperson. The source records remain in place: the PivotTable is a separate summary view, not a replacement for the underlying data.

“Pivot table function” is a common way to ask about the feature, but Excel does not have a worksheet function called PIVOT that creates one. PivotTables are created from the ribbon. Excel does have the GETPIVOTDATA function, which can retrieve a value from an existing PivotTable, but it does not build or replace the PivotTable itself.

How the fields and layout work

When you create a PivotTable, Excel displays a field list containing the source column names. Each field can be placed in one of four areas, and its placement determines how the report is organized:

  • Rows: Creates labels down the left side, such as region or salesperson.
  • Columns: Creates categories across the top, such as year or product type.
  • Values: Calculates a summary, such as total sales, average units, or a count of records.
  • Filters: Adds a report-level filter so you can limit the displayed data, for example to one year.

Imagine four source records: North sales of $30 and $32, and South sales of $16 and $10. Putting Region in Rows and Sales in Values produces North at $62, South at $26, and a grand total of $88. Adding Product to Columns could split those totals into product categories. This is a summary of the records, not a guess: the values depend on the source data and the selected calculation.

Excel usually sums a numeric field placed in Values. If the field contains text, or a mixture that Excel cannot treat as numeric, it may count records instead. You can change the calculation through Value Field Settings, which offers choices such as Sum, Count, Average, Max, and Min. The exact choices can depend on the type of source and the field.

Prepare the source data first

A clear source list makes a PivotTable easier to create and maintain. Use one header row, with a distinct name for each column, and keep each record on a single row. Avoid blank rows or columns inside the list, merged cells, and subtotals inserted between records. Store one kind of information in each column: for example, put dates in a Date column and numeric sales amounts in a Sales column.

It is also important to keep values consistent. If a region is recorded as both “North” and “N.”, Excel treats them as separate categories. Dates stored as text may not group as calendar dates, and numbers stored as text may not sum as expected. Check the source if the PivotTable shows unexpected categories or uses Count where you expected Sum.

For a source that will grow, convert the list to an Excel table before building the PivotTable. Select a cell in the list and choose Insert > Table, confirm that the table has headers, and select OK. A PivotTable based on a table can include newly added table rows after you refresh it; simply adding data below a fixed cell range does not necessarily expand that range.

Create a PivotTable in Excel for Windows

  1. Select a cell inside the source list or Excel table. You do not need to highlight the entire list if the data is contiguous and has headers.
  2. Choose Insert > PivotTable. Excel identifies the suggested table or range in the dialog.
  3. Check the source shown in the dialog. Choose New Worksheet for a separate report sheet, or Existing Worksheet and specify a location if you want the report on a particular sheet. Select OK.
  4. In the PivotTable Fields pane, select or drag fields into Rows, Columns, Values, and Filters. Start with one category in Rows and one numeric field in Values, then add detail as needed.
  5. Review the result, including the value calculation and grand totals. Use the field list to adjust the layout until it answers the question you have.

The interface in Excel for Mac is similar: select the data and use Insert > PivotTable, then configure the fields in the PivotTable field pane. Pane placement and some ribbon details can vary by release. Excel for the web also supports creating and working with PivotTables, though available options can differ from the full desktop application. If a particular command is missing in the web version, open the workbook in desktop Excel.

Choose useful summaries and add detail

A good PivotTable begins with a question. “How much did each region sell?” calls for Region in Rows and Sales in Values. “How did monthly sales change by product?” can use a date grouping in Rows and Product in Columns. A useful report usually has only the fields needed to answer the question; adding every available field can make it difficult to read.

To change how a value is calculated, select a value in the PivotTable, open Value Field Settings, and choose a summary method. Use Sum for additive quantities such as revenue or units, Average for a mean, and Count when the question is about the number of records. A count of sales records is not the same as a count of distinct customers; ordinary Count counts nonblank entries and does not automatically identify unique people.

PivotTables can also display values as percentages or differences rather than just raw totals. In Value Field Settings, the Show Values As options can compare a value with a row or column total, or show a running total for supported layouts. This changes the display calculation, not the source figures. Check the row and column arrangement carefully, because the comparison depends on which field defines the base item.

For date fields that Excel recognizes as dates, you can group records into periods such as months or years. Right-click a date in the PivotTable and choose Group, then select the intervals you need. If Group is unavailable or dates appear as individual text labels, inspect the source for blanks, text-formatted dates, or inconsistent entries.

Filter, sort, and refresh the report

Use the dropdowns beside row or column labels to filter categories or sort the displayed summary. A field placed in Filters provides a report-level selector. These controls affect what is shown in the PivotTable; they do not delete or edit the original records. If users need a more visible way to switch among categories, select the PivotTable and use PivotTable Analyze > Insert Slicer. A slicer presents clickable buttons for filtering supported fields.

A PivotTable does not always update immediately when the source values change. Right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To update multiple PivotTables in a workbook, use Data > Refresh All. Refresh recalculates the summary from its source; it does not fix a source range that excludes newly entered records. If the report is based on a fixed range, use PivotTable Analyze > Change Data Source to correct it, or build the report from an Excel table that can expand.

Refresh behavior also matters when the source is an external connection or a data model. Access, credentials, connection availability, and workbook settings can affect whether the newest data is retrieved. For a normal worksheet list, the key distinction is simpler: editing a value in an included source row requires a refresh, while adding records outside a fixed source range may require changing the source as well.

Common PivotTable problems

  • Excel counts instead of sums: Check whether the source values are genuine numbers and whether the field contains text or errors. Then review Value Field Settings.
  • A new category is missing: Refresh the report and check whether the new row is inside the PivotTable’s source range. A formatted Excel table is useful for expanding sources.
  • Similar labels appear separately: Look for spelling variations, extra spaces, or inconsistent abbreviations in the original column.
  • Dates will not group: Confirm that source entries are real Excel dates and that the field does not contain blanks or text mixed with dates.
  • The report shows old totals: Refresh the PivotTable after changing source records. If the numbers still look wrong, verify the source range and selected summary method.

When a report is confusing, trace one displayed total back to the source records. This is often quicker than changing several settings at once. Also remember that a PivotTable may retain items that are no longer present in the source, depending on its settings and history. If obsolete labels remain after a refresh, inspect PivotTable Options and the source data rather than assuming the totals include them.

PivotTables compared with formulas and charts

PivotTables are especially useful for exploratory summaries: you can move fields around, filter categories, and change calculations without writing a separate formula for every group. They are not the only way to summarize data. Functions such as SUMIFS and COUNTIFS are useful when a worksheet needs a fixed calculation in a specific cell, or when a report layout must remain customized. Those formulas require criteria and ranges to be managed explicitly; PivotTables usually make category-based rearrangement easier.

A PivotChart can visualize PivotTable summaries and respond to the report’s filters. A standard chart based directly on a carefully prepared summary may be simpler when the layout should stay fixed. A PivotTable is also not a database: it is a reporting tool, so it should not be used to clean, merge, or edit the underlying transaction records. Correct the source or use appropriate data-preparation tools when the records themselves need repair.

For a small list with one simple total, a formula may be faster. For repeated questions across many categories, a PivotTable often saves time and makes the relationships easier to inspect. The choice depends on whether you need a flexible analysis view or a fixed calculation embedded in a worksheet.

Frequently Asked Questions

Is a PivotTable an Excel function?
No. A PivotTable is an Excel feature for summarizing and rearranging data, created from Insert > PivotTable. GETPIVOTDATA is a worksheet function that can retrieve a value from a PivotTable that already exists.

How do I add a new row to a PivotTable?
Add the record to the source data, then refresh the PivotTable. If the new row is outside a fixed source range, change the data source or use an Excel table as the source so added table rows can be included on refresh.

Why does my PivotTable show Count instead of Sum?
Excel may interpret the value column as text or find nonnumeric entries, so it chooses Count. Check the source values and data types, then open Value Field Settings and choose Sum if the field contains usable numbers.

Can I edit the values inside a PivotTable?
Not as if they were the original records. A PivotTable displays summaries derived from the source; edit the underlying source record and refresh the report to reflect the change.

Does a PivotTable update automatically?
Usually, changing source cells does not immediately recalculate an existing PivotTable. Use Refresh or Refresh All. Also ensure that new records are included in the source range before refreshing.

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.