Create a Pivot Table in Excel: Steps and Tips That Work

Charts & pivot tables · Updated November 6, 2026

To create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a well-organized data range, choose Insert > PivotTable > From Table/Range, confirm the source and location, then drag fields into Rows, Columns, Values, and Filters. The result is an interactive summary you can rearrange without changing the original records.

What a PivotTable Does

A PivotTable groups and summarizes records so you can examine a large list from different angles. Instead of manually adding sales by region or counting orders by month, you place relevant fields into areas in the PivotTable Fields pane. Excel then calculates the summary and lets you change its layout by moving fields.

For example, imagine a sales list with one row per order and columns for Date, Region, Product, Salesperson, and Revenue. A PivotTable can show total revenue for each region, put products across the columns, or count orders by salesperson. The underlying list stays intact; the PivotTable presents a separate view of it.

PivotTables are useful for exploration and recurring reports, but they do not repair poor source data or automatically include every later change. Good source structure and a refresh when appropriate are essential.

Prepare the Source Data

Before inserting a PivotTable, make the source a consistent rectangular list. Each column should describe one kind of information, and each row should represent one record, such as an order or transaction. Use a single header row with a distinct, nonblank name for every column.

  • Remove completely blank rows and columns within the data.
  • Keep values of the same type together in each column. Dates should be dates, amounts should be numbers, and category names should be text.
  • Avoid merged cells, multi-row headings, and manually inserted subtotal rows in the source list.
  • Check for inconsistent category spellings, such as “West” and “West ” with a trailing space, which Excel may treat as separate items.

For a source that will grow, convert the range to an Excel Table first: select a cell in the data and choose Insert > Table, then confirm that My table has headers is selected. A Table expands as you add records below it, and a PivotTable based on that Table can use the expanded source after you refresh. This is generally more reliable than selecting a fixed range that may leave out new rows.

Create the PivotTable in Excel

  1. Click any cell inside the source range or Table. You normally do not need to select the entire list if it is a continuous block with headers.
  2. Choose Insert > PivotTable > From Table/Range. Excel opens a dialog showing the detected table or range.
  3. Check the source, then choose New Worksheet or Existing Worksheet. For an existing sheet, specify a destination cell with room for the report.
  4. Select OK. Excel creates an empty PivotTable and displays the PivotTable Fields pane.
  5. Drag field names into the areas at the bottom of the pane, or select their checkboxes and adjust their placement afterward.

In Excel for Mac, use the Insert tab and choose PivotTable; the dialog and ribbon wording can vary slightly by build. Excel for the web also supports PivotTables, though some controls and advanced options may differ from the Windows desktop app. The steps above describe Microsoft 365 for Windows.

Arrange Fields to Answer a Question

The four field areas determine the report’s structure. Rows create groups down the page; Columns create groups across it; Values calculate a summary; and Filters let you restrict the report to selected items. A field can be moved between areas, removed, or used more than once if that helps answer the question.

For the sales example, drag Region to Rows, Product to Columns, and Revenue to Values. The resulting grid compares revenue by product within each region, with totals at the edges when enabled. To see one product at a time instead, put Product in Filters. To compare revenue by salesperson, replace Region in Rows with Salesperson.

Excel chooses a default calculation for a Values field. Numeric fields are usually summed, while text fields are usually counted. If Revenue appears as “Count of Revenue” rather than “Sum of Revenue,” check the source column for text values or blanks that have caused Excel to treat the field differently. You can also change the calculation: open the menu for the value field in the pane, choose Value Field Settings, and select an option such as Sum, Count, or Average.

Use a calculation that matches the question. Sum answers “How much in total?”; Count answers “How many records?”; and Average answers “What was the mean value?” An average of transaction amounts is not the same as total revenue, and a count of customers is not necessarily a count of distinct customers. Distinct counts are available in PivotTables created with the Data Model, but that setup is a separate choice during creation.

Format and Explore the Results

A useful summary should be readable as well as correct. To format a value field as currency or a percentage, open its Value Field Settings and choose Number Format. Applying a number format to the field this way is preferable to formatting individual cells, because the field’s format is more likely to remain consistent as the PivotTable changes.

Use the Design tab to select a PivotTable style, adjust banded rows, or control whether row and column grand totals appear. You can sort labels or values and filter items from the drop-down menus beside the row or column headings. If you need to compare months, place a date field in Rows and use Excel’s date grouping options; right-click a date in the PivotTable and choose Group, then select suitable intervals such as months or years. Grouping works best when the source contains real Excel dates rather than date-like text.

For interactive filtering, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then select a categorical field such as Region. A slicer provides buttons for selecting one or more items. For a date field, PivotTable Analyze > Insert Timeline can provide a time-based filter when the source dates are recognized as dates. These controls filter the PivotTable; they do not alter the original source records.

Refresh and Update the Source

A PivotTable stores a summary based on its source data, so edits to the source are not always reflected immediately. After changing records, select the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables together. If new rows are missing, confirm that the PivotTable’s source is the Excel Table or that its selected range includes those rows.

Refresh updates the summary from the existing source; it does not fix errors in that source. For instance, correcting a misspelled region in the data and refreshing should update the categories, but a manually typed correction in the PivotTable itself is not a substitute for correcting the record. If you delete or rename source columns, Excel may report that a field is unavailable. Check the headers and source range, then adjust the PivotTable source if needed.

When sharing a workbook, explain whether readers should refresh the report and where its source data lives. A PivotTable may show an older summary if it has not been refreshed, even though the source list has changed.

Common Problems and Practical Fixes

  • The wrong rows are included: Check the source range in PivotTable Analyze > Change Data Source. A Table-based source helps prevent newly appended rows from being excluded.
  • Numbers are counted instead of summed: Inspect the source values for text, blanks, or inconsistent entries, then set the calculation in Value Field Settings.
  • Two labels that look alike appear separately: Look for extra spaces, spelling differences, or inconsistent capitalization in the source and standardize the entries there.
  • A field is missing from the pane: It may be absent from the source header row, or the PivotTable may be based on an older range. Confirm the source and refresh.
  • The report shows old results: Refresh the PivotTable. If the source was extended, verify that its range or Table includes the added records.
  • Dates will not group: Check for text dates, blanks, or invalid values mixed into the date column. Correct the source data and refresh before trying Group again.

One common misconception is that a PivotTable changes or reorganizes the source list. It does not: rearranging fields changes the summary layout, not the original rows. Another is that every PivotTable automatically updates as soon as source cells change. In many ordinary workflows, you need to refresh it.

PivotTables Compared with Other Summary Methods

Use a PivotTable when you want to explore categories, change the grouping quickly, or let someone filter a report without writing formulas. It is especially convenient when the same source needs summaries by region, product, month, or another field.

Formulas may be a better fit when a report needs a fixed layout, custom calculations, or values embedded in a presentation-style worksheet. Excel functions such as SUMIFS can calculate totals using specified criteria, while a PivotTable provides a more interactive grouping interface. A PivotChart, created from a PivotTable, is useful when you want a chart that responds to the same filters and field choices. These approaches can also complement each other: a PivotTable can explore the data, while formulas or a chart present selected results.

Frequently Asked Questions

Can I create a PivotTable from multiple sheets?
Yes, but the standard From Table/Range workflow uses one source range or Table. For data spread across several tables, consider combining it with Power Query or using the Data Model, depending on how the data relates. The right setup depends on whether the sheets share the same columns or describe related tables.

How do I add new data to an existing PivotTable?
Add records to the source Table, then select the PivotTable and choose PivotTable Analyze > Refresh. If the source is a fixed range, expand the source through PivotTable Analyze > Change Data Source before refreshing.

Can a PivotTable show percentages instead of totals?
Yes. Open Value Field Settings, use the Show Values As tab, and choose a calculation such as % of Grand Total or % of Row Total. This changes how the displayed values are calculated; it does not change the source data.

Can I edit a value directly in a PivotTable?
Usually not as a way to change an individual summary result. Edit the corresponding source record, then refresh. A PivotTable summarizes its source rather than serving as a data-entry form.

Does a PivotTable update automatically?
Do not assume so. Refresh it after source changes, using PivotTable Analyze > Refresh or Data > Refresh All. A Table-based source makes it easier for the PivotTable to include appended records when refreshed.

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.