Pivot Table Excel: How to Create and Use One

Charts & pivot tables · Updated November 18, 2026

To create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data range or Excel table, choose Insert > PivotTable, confirm the source and choose where to place it, then drag fields into Rows, Columns, Values, or Filters. For example, put Region in Rows and Sales in Values to summarize sales by region without writing a formula.

What a PivotTable does

A PivotTable is an interactive summary of data in a worksheet. It can group records, calculate totals or counts, and rearrange the view so you can examine the same dataset from different angles. Instead of building a separate formula for every region or product, you place fields into areas and let Excel aggregate the matching records.

Imagine a sales list with one row per order and columns for Date, Region, Product, Sales, and Units. A PivotTable can show total sales by region, products across the columns, or monthly unit totals. The source records remain in place; the PivotTable displays a summary rather than rewriting the original list.

PivotTables are useful when a dataset is too large or repetitive to review row by row. They are less suitable when you need a fixed, custom report layout or calculations that depend on detailed row-by-row logic. In those cases, formulas, charts, or Power Query may be a better fit.

Prepare the source data

Good source data makes the PivotTable easier to create and more reliable. Arrange the data as a simple rectangular list: one header row followed by records, with one type of information in each column. Avoid merged cells, blank header cells, completely blank rows or columns inside the list, and separate subtotals inserted between records.

  • Give each column a unique, descriptive header, such as Date, Region, Product, and Sales.
  • Keep one record per row. If an order contains several products, use a separate row for each product line when that is the level you need to analyze.
  • Store dates as Excel dates and amounts as numbers, not as text that merely looks like a date or currency.
  • Use consistent labels. “North,” “north,” and “North ” may be treated as different items if they are stored as different text values.

For a source that will grow, consider converting the list to an Excel table first. Select a cell in the data and press Ctrl+T on Windows, confirm the range, and make sure “My table has headers” is selected. On Mac, use Insert > Table if the shortcut differs in your Excel setup. Tables expand as you add records, making them a convenient PivotTable source; after adding records, you still need to refresh the PivotTable to include them.

Create a PivotTable step by step

  1. Click any cell in the source list or table. If Excel does not identify the full range correctly, select the complete range first, including the headers.
  2. Choose Insert > PivotTable. Excel opens a dialog showing the proposed table or range as the source.
  3. Check the source range. Choose New Worksheet for a separate summary sheet, or Existing Worksheet and specify a cell if the summary should sit on an existing sheet.
  4. Select OK. Excel creates an empty PivotTable and displays the PivotTable Fields pane.
  5. Drag fields into the pane’s Rows, Columns, Values, and Filters areas, or select their checkboxes and adjust their placement afterward.

For the sales example, place Region in Rows and Sales in Values. Excel creates a total for each region. Add Product to Columns to compare product totals across regions. If the PivotTable Fields pane is hidden, click inside the PivotTable and use PivotTable Analyze > Field List to show it again. In Excel for Mac and Excel for the web, the ribbon layout and pane wording can vary slightly by version, but the basic process is the same.

Understand Rows, Columns, Values, and Filters

Each area controls a different part of the summary. Rows lists categories vertically; Columns places categories across the top; Values performs calculations; and Filters adds a report-level selector so you can restrict what the PivotTable includes. You do not need to use all four areas.

  • Rows: Use Region, salesperson, or product to create one line per category.
  • Columns: Use Product or Year to create side-by-side categories. Too many column items can make a report wide and difficult to read.
  • Values: Use a numeric field such as Sales or Units for totals, averages, or other summaries.
  • Filters: Use a field such as Year when readers need to choose which period appears in the summary.

Field placement changes the question answered, not the underlying data. Putting Product in Rows and Region in Columns gives the same underlying cross-tabulation as swapping them, but the report’s orientation changes. To remove a field, drag it out of its area or clear its checkbox in the field list.

Choose the right calculation

Excel commonly sums numeric fields in Values, but it may use Count when a field contains text, blanks, or a mixture of values that Excel cannot treat consistently as numbers. Check the label above the values, such as “Sum of Sales” or “Count of Sales,” rather than assuming the calculation is a total.

To change a calculation, click a value in the PivotTable, open Value Field Settings, and choose an option under Summarize Values By, such as Sum, Count, or Average. The available choices depend on the field and the type of PivotTable. In the same settings, the Show Values As tab can display a result as a percentage of a row, column, or grand total. This changes how the values are displayed; it does not change the source records.

For example, if the PivotTable shows a region’s sales as 20% of the grand total, that figure means the region contributes one fifth of all sales represented in the report. It is not the same as a 20% profit margin or a percentage increase. Check the field names and calculation settings before interpreting a percentage.

Filter, sort, and group the summary

Use the dropdown beside a row or column label to filter which items appear or sort them. A field placed in Filters provides a selector above the report. For more interactive reports in desktop Excel, click inside the PivotTable and choose PivotTable Analyze > Insert Slicer, then choose the fields to turn into clickable filter buttons. Slicers are especially helpful when a report will be used by someone who may not know how to open the field dropdowns.

Date fields can often be grouped into larger periods. In a desktop PivotTable, right-click a date item and choose Group, then select a suitable interval such as Months or Years. If Group is unavailable or Excel groups dates unexpectedly, check that the source column contains actual Excel dates and does not include text entries or blank values mixed into the date field. Grouping is a summary view; it does not convert the source dates.

Sorting and filtering affect what is visible, so a reader should be able to tell when a report is restricted. Check the filter buttons and slicer selections before copying totals into another report. A filtered grand total usually represents only the visible selection, not necessarily the full source dataset.

Refresh and manage changes

A PivotTable does not always update immediately when its source records change. After editing source values or adding rows, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update multiple PivotTables in the workbook, use Data > Refresh All. On Mac or the web, command placement can vary, but look for Refresh in the PivotTable or Data controls.

If newly added records do not appear, check whether the source is an Excel table or a fixed cell range. A table is generally easier to extend. For a fixed range, select the PivotTable and use PivotTable Analyze > Change Data Source to update the source range, then refresh. Also confirm that the new rows have the same column headers and are directly part of the intended data list.

Refreshing can change the order of items or update totals, so review the result after doing it. If several PivotTables use the same source, Refresh All is convenient, but it can take longer in a workbook with many connections or large datasets.

Format and present results clearly

Use PivotTable Design options to choose a style, adjust subtotals, and control grand totals. For sales totals, apply a number or currency format to the values so readers can interpret them quickly. Right-click a value and choose Number Format where available; formatting the value field this way is preferable to applying a temporary cell format that may not carry through after a refresh.

Keep the layout aligned with the question. A compact report with a few row fields may work well for exploration, while a wider cross-tabulation is useful for comparing categories. Rename headings such as “Sum of Sales” to a reader-friendly label when appropriate, but keep the meaning clear. If you copy PivotTable results elsewhere, remember that a copied summary may not refresh like the original PivotTable.

A PivotChart can visualize a PivotTable and respond to its filters. Select a cell in the PivotTable and use Insert > PivotChart if the command is available in your Excel version. A regular chart may be a better choice when you want a static chart with precise formatting and do not need it tied to PivotTable filtering.

Common problems and how to fix them

  • The PivotTable says Count instead of Sum: Check the source column for numbers stored as text, unexpected text entries, or inconsistent data. Correct the source values and refresh.
  • New rows are missing: Refresh the PivotTable and confirm its source includes the new rows. Using an Excel table as the source can reduce range-maintenance work.
  • One category appears more than once: Look for spelling differences, extra spaces, or inconsistent labels in the source. Correct them there, then refresh.
  • Dates will not group: Check for text dates, blank cells, or invalid entries in the date column. Make the source values consistent and refresh before trying to group again.
  • The field list disappeared: Click in the PivotTable and choose PivotTable Analyze > Field List.
  • Totals seem too high: Confirm the source has one row per intended record and that the selected value field is being summed at the correct level. Duplicate source records will also be counted in a sum.

When investigating an unexpected result, inspect a small sample of the source records as well as the PivotTable settings. A PivotTable can summarize its source correctly while still answering the wrong question because the source contains duplicates, the wrong field was used, or a filter is active.

PivotTables versus formulas and alternatives

Choose a PivotTable when you want to explore categories quickly, rearrange dimensions, or summarize many records without creating a grid of individual formulas. Choose formulas when each output cell needs a deliberately customized calculation or must update in a specific layout. Excel functions such as SUMIFS can produce criteria-based totals, but the criteria and output structure must be planned and maintained.

Power Query is useful when the main task is importing, cleaning, combining, or reshaping data before analysis. It can prepare a consistent table that a PivotTable then summarizes. A PivotTable is not a substitute for fixing bad source data: it can hide detail in a compact summary, but it cannot determine whether the underlying records are accurate.

Frequently Asked Questions

Is a PivotTable the same as an Excel table?
An Excel table organizes and manages the source records, with features such as header filters and automatic expansion. A PivotTable summarizes records from a source. You can use an Excel table as the source for a PivotTable.

Do I need to know formulas to make a PivotTable?
No. The basic process uses the Insert menu and field placement. You can select a calculation such as Sum or Average through Value Field Settings without writing a formula.

Why does my PivotTable not update automatically?
PivotTables generally need to be refreshed after source data changes. Click inside the report and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables.

Can I make a PivotTable from multiple sheets?
Yes, but combining the data into one consistent table or using an appropriate data model is often easier to maintain. The exact setup depends on how the sheets are structured and whether their columns match.

Can I delete the PivotTable without deleting my source data?
Yes. The PivotTable is a separate summary from the source list. Select the PivotTable output and delete it, taking care not to select or remove the source data on another part of the sheet.

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.