How to Create a PivotTable in Excel: Beginner Guide

A PivotTable turns a long list of records into a compact summary without changing the source data. Instead of writing separate formulas for every region, product, or month, you arrange fields to answer questions such as “Which region produced the most revenue?” or “How did each product contribute to the total?”

The useful skill is not merely inserting a PivotTable. It is knowing how to prepare the source, place fields in the four report areas, verify the calculation, and refresh the result when the source changes. This guide takes you through that complete beginner workflow.

What a PivotTable does

A PivotTable groups records that share the same labels and summarizes their values. You can rearrange the report by dragging fields rather than rebuilding the source or writing new formulas.

PivotTables are best for structured lists in which each row is one record and each column describes one attribute of that record. They are not a substitute for cleaning inconsistent source data, and they do not automatically explain why a pattern exists.

Prepare the source data first

A reliable PivotTable begins with a rectangular data set. Before inserting the report, check the source against these rules:

• Use one header row with a unique, nonblank name for every column.
• Keep one type of information in each column: dates with dates, amounts with amounts, and category labels with category labels.
• Store one record per row.
• Remove completely blank rows and columns from inside the data set.
• Avoid merged cells, manual subtotals, and grand totals in the source.
• Make category spelling consistent. North and North can behave like different items when one contains a trailing space.

Converting the range to an Excel Table is a strong default. Select a cell in the source, choose Insert > Table, confirm the range, and select My table has headers. Rows added to the table can be included when the PivotTable is refreshed, while a fixed cell range may need its source reference expanded.

Keep totals out of the source: a source Total row is treated as another record and may be added again by the PivotTable, producing an inflated result.

Use a small sales example

The walkthrough uses nine transactions. Enter the headers in A1:F1 and the records in A2:F10:

This sample sales dataset is structured correctly for a PivotTable, with one header row and one record per row.

The first report will calculate revenue by region. The correct totals are 5,320 for North, 2,900 for South, 4,530 for West, and 12,750 overall. These known answers make it easy to verify the PivotTable.

Create your first PivotTable

The following path applies to current desktop versions of Excel. Excel for the web and Excel for Mac offer the same core PivotTable concept, although some labels and placement options can differ.

1. Select any cell in the source table or select the complete range A1:F10.
2. Choose Insert > PivotTable.
3. Confirm the table or range shown in the dialog box.
4. Choose New Worksheet so the report has room to expand.
The Create PivotTable dialog lets you confirm the source range and choose where the new PivotTable will be placed.

5. Select OK. Excel creates a blank PivotTable and displays the PivotTable Fields list.

If you are unsure how to arrange the fields, Insert > Recommended PivotTables can provide a starting layout. Treat the suggestion as a draft and verify that its calculation answers your actual question.

Microsoft’s current PivotTable creation guide includes platform-specific instructions and data-format guidance.

Understand the four field areas

The Fields list shows the source column names at the top and four destination areas below. Each area gives a field a different job:

You can select a field name and let Excel choose an area, but dragging fields gives you more control. A field is not locked to one role: the same field can appear in more than one area when the report needs it.

Build a revenue-by-region summary

1. Drag Region to the Rows area.
2. Drag Revenue to the Values area.
This PivotTable groups the sales data by region and sums the revenue values to show totals for North, South, and West.

3. Confirm that the Values area says Sum of Revenue, not Count of Revenue.
4. Compare the regional totals and Grand Total with the known answers above.

Excel normally uses Sum for a numeric field and Count for a field it treats as nonnumeric. If Revenue is counted instead of added, check for numbers stored as text or mixed data types. Correct the source values, then refresh the PivotTable.

To rank the regions, right-click a value in the Sum of Revenue column and choose Sort > Sort Largest to Smallest. Sorting a value field reorders the summarized items; it does not rearrange the original transaction rows.

Add a second dimension

To compare products inside each region, drag Product to the Columns area. The report now has Region down the rows, Product across the columns, and summed Revenue at each intersection.

Adding Product to the Columns area creates a two-dimensional PivotTable that compares revenue across both regions and products.

This two-dimensional view should show, for example, 3,600 in the North–Laptop cell and 1,500 in the West–Monitor cell. Row totals answer “How much revenue came from each region?” Column totals answer “How much came from each product?”

If the report becomes too wide, place Product beneath Region in the Rows area instead. Field order matters: dragging Product above Region creates a product-first hierarchy, while placing Region first creates a region-first hierarchy.

Change Sum to Count, Average, or a percentage

The field in Values determines what Excel calculates. To change the summary:

1. Open the menu beside Sum of Revenue in the Values area.
2. Select Value Field Settings.
Use Value Field Settings to change how a PivotTable summarizes a field, such as switching Revenue from Sum to Count.

3. On Summarize Values By, choose Sum, Count, Average, Max, Min, or another appropriate function.
4. Select Number Format if the result needs currency, percentage, or another numeric display.
5. Select OK.

Summarize Values By changes the calculation itself. Show Values As keeps the underlying summary but displays it in another way, such as % of Grand Total, % of Row Total, or a running total.

The same Revenue field can be added twice to Values, with one copy showing the sum and the other displayed as a percentage of the grand total.

For example, keep Revenue summarized by Sum, then choose Show Values As > % of Grand Total to see each result as a share of all revenue. Microsoft’s PivotTable calculation reference explains the available summary functions and custom displays.

Filter the report without deleting data

Drag Channel to the Filters area. A filter appears above the PivotTable, where you can select Online, Partner, or Retail. The source records remain intact; the report recalculates for the visible item.

Adding Channel to the Filters area lets you display only Online sales without changing the source data.

You can also filter row and column fields:

• Use the field’s check boxes to show selected items.
• Use Label Filters to match text or other label conditions.
• Use Value Filters to show items above, below, or within a summarized-value condition.
• Use the search box when a field contains many items.

Always check the filter indicators before interpreting or sharing a result. A perfectly calculated PivotTable can still be misleading when an old filter remains active.

Format and lay out the report

Select a cell in the PivotTable to display the contextual PivotTable Analyze and Design tabs in desktop Excel. Use them to control the report without formatting each result cell manually.

• Use Value Field Settings > Number Format so formatting stays attached to the value field.
• Use Design > Report Layout to switch among Compact, Outline, and Tabular forms.
• Use Subtotals and Grand Totals only when they help answer the question.
• Rename a value field to a reader-friendly label, provided the custom name does not duplicate an existing field name.
• Leave empty space below and to the right because the report may grow when fields or source records change.

Do not type over PivotTable results: the displayed cells are generated by the report. Change the source, field arrangement, calculation, or number format instead.

Refresh after the source changes

A PivotTable may not immediately reflect edits to its source. After changing a value or adding records, select the report and choose PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. Use Refresh All when the workbook contains several reports or connections that need updating.

Right-click inside the PivotTable and choose Refresh to update the report after changes to the source data.

If the source is an Excel Table, new table rows can join the source automatically when the PivotTable refreshes. If the report uses a fixed range and new records fall outside it, choose PivotTable Analyze > Change Data Source and expand the range.

Microsoft documents an automatic-refresh option for some newer Microsoft 365 builds and update channels, but availability and settings vary. A deliberate manual refresh remains the safest final check before publishing or distributing a report. See Microsoft’s refresh instructions for current Windows, web, and Mac options.

Fix common PivotTable problems

Frequently asked questions

Verify the answer before sharing

A finished PivotTable should make its question and calculation obvious. Check the field arrangement, summary function, number format, filters, source range, and refresh status. Then compare at least one subtotal or Grand Total with a value you can verify from the source.

Once those checks pass, the report becomes easy to reshape. Move a field from Rows to Columns, change Sum to Average, or add a filter—and the same source can answer a new question without being rebuilt.