Excel Pivot Tables for Beginners: Build Your First Summary

Charts & pivot tables · Updated November 21, 2026

An Excel PivotTable quickly summarizes a list of records without changing the original data. In Microsoft 365 for Windows, select a cell in a clean data range, choose Insert > PivotTable, confirm the range and destination, then drag fields into Rows, Columns, Values, or Filters to build a report.

What a PivotTable Does

A PivotTable is an interactive summary of tabular data. Instead of writing formulas for each category, you tell Excel which fields to group and which numbers to calculate. For example, a sales list with dates, products, regions, and amounts can become a report of total sales by region, product, or month. You can rearrange the fields to ask a different question without rebuilding the source list.

The word “pivot” describes that ability to turn the view: a field displayed as rows can be moved to columns, or removed from the report altogether. The PivotTable does not normally edit or reorganize the source records. It presents a separate summary, which makes it useful for exploring data while preserving the underlying list.

PivotTables can calculate sums, counts, averages, minimums, maximums, and other summaries. They are not a substitute for every kind of analysis: a custom calculation or a carefully controlled model may call for formulas, Power Query, or another tool. But for grouping and comparing a typical spreadsheet list, they are often the quickest place to start.

Prepare the Source Data First

Good source data is the foundation of a useful PivotTable. Put each record on its own row and each attribute in its own column. The first row should contain distinct, descriptive headings such as Date, Region, Product, and Sales. Avoid merged cells, blank header cells, and decorative title rows within the data range.

For example, a small sales list might have columns named Date, Region, Product, and Sales, with one transaction per row. If a product was sold five times, represent that as five transaction rows (or as one row with a quantity and a unit price, if those are the fields you need). A PivotTable can only summarize information that is actually present in the source.

  • Keep one type of information in each column. Do not combine a customer name and address in the same field if you may want to group by either one.
  • Use consistent values: “North,” “NORTH,” and “Northern” may be treated as different categories.
  • Store dates as real Excel dates and amounts as numbers, not as text that merely looks like a date or currency.
  • Remove fully blank rows and columns from the middle of the list, and check for accidental spaces or spelling variations.

For a list that will grow, convert the source range into an Excel Table first: select a cell in the data and choose Insert > Table, then confirm that the table has headers. A PivotTable based on a Table can include newly added rows when you refresh it, whereas a PivotTable based on a fixed cell range may not include records added beyond that range.

Create Your First PivotTable

  1. Click any cell inside the source list or Excel Table. Selecting one cell is usually enough if the data is contiguous and has clear headings.
  2. Choose Insert > PivotTable. Excel displays a dialog showing the suggested Table/Range and asks where to place the report.
  3. Check that the source range is correct. Choose New Worksheet for a separate report sheet, or Existing Worksheet and specify a destination cell if you want the report beside other content.
  4. Select OK. Excel creates an empty PivotTable and displays the PivotTable Fields pane, usually at the right of the window.
  5. Drag fields from the field list into the areas labelled Rows, Columns, Values, and Filters. The summary updates as you place or move fields.

In Excel for Mac, the core Insert > PivotTable workflow is similar, although ribbon placement and some dialog details can differ. Excel for the web also supports PivotTables, but some creation and layout options may be arranged differently or be less extensive than in the Windows desktop app. If a specific command is not visible, check the PivotTable ribbon tabs or use the desktop app for the full set of controls.

Understand the Four Field Areas

The four areas determine what the report looks like and what it calculates. A field can be moved between areas by dragging it, and you can remove it by dragging it out or clearing its checkbox.

  • Rows: Lists categories down the left side, such as Region or Product. Putting Product below Region creates nested groups: products are shown within each region.
  • Columns: Spreads categories across the top. For example, putting Year in Columns and Region in Rows creates a cross-tab of regions against years.
  • Values: Calculates a summary from the selected field. Numeric fields are often summed; text fields are commonly counted. The calculation depends on the data and Excel’s interpretation of it.
  • Filters: Adds a report-wide filter, such as selecting one year or one salesperson to display. A filter affects what appears in the PivotTable, not the original source list.

For a simple first report, drag Region to Rows and Sales to Values. The result should show a total for each region and a grand total. Then add Product below Region in Rows to see the product breakdown within each region. Putting Product in Columns instead displays products across the top, which may be easier to compare when there are only a few products.

A Concrete Sales Example

Suppose the source contains these four records: North, Apples, 120; North, Pears, 80; South, Apples, 150; and South, Pears, 50. Put Region in Rows, Product in Columns, and Sales in Values. Excel summarizes the records into a two-way table: North has 120 in Apples and 80 in Pears, for a total of 200; South has 150 in Apples and 50 in Pears, for a total of 200. The grand total is 400.

This view makes a comparison easier than scanning four separate transactions. If you move Product from Columns to Rows, Excel changes the arrangement rather than changing the values in the source. If you add Date to Filters, you can limit the report to a selected date or period, provided the source dates are valid and the field is represented appropriately.

A useful habit is to check the grand total against the source data. If it does not match the expected total, investigate the source range, filters, and calculation type before trusting the report. A PivotTable is only as reliable as the records and settings behind it.

Change Calculations and Improve Readability

If Excel shows Count of Sales when you expected Sum of Sales, open the drop-down beside the value field in the PivotTable Fields pane and choose Value Field Settings. Select Sum, then OK. If Sum is unavailable or the result is unexpectedly a count, check whether the source column contains numbers stored as text or nonnumeric entries. Correct the source values, then refresh the PivotTable.

In Value Field Settings, you can also choose calculations such as Average, Count, Min, or Max. These answer different questions: Sum of Sales gives total revenue amounts in the source, while Count of Sales counts entries. Average of Sales gives the mean value per record; it does not automatically calculate a weighted average if each row represents a different quantity or exposure.

Use the Design tab to make the report easier to scan. Design > Report Layout > Show in Tabular Form displays row fields in separate columns; Design > Report Layout > Repeat All Item Labels can make repeated category labels visible in each row. Number formatting can be set through Value Field Settings > Number Format so totals appear as currency, whole numbers, or percentages as appropriate. Applying number formatting to the value field is more dependable than formatting only a few visible cells, because PivotTable refreshes can alter the displayed layout.

Refresh the Report When Data Changes

A PivotTable is a summary built from its source; it does not necessarily recalculate immediately when you edit a source record. After changing or adding source data, right-click a PivotTable cell and choose Refresh, or use PivotTable Analyze > Refresh. To refresh multiple PivotTables in the workbook, use Data > Refresh All.

If new rows are missing, first confirm that the PivotTable source includes them. A source based on an Excel Table generally expands when you add records directly below it, but the PivotTable still needs a refresh to use the updated contents. With an ordinary fixed range, choose PivotTable Analyze > Change Data Source and select the expanded range, or convert the source to a Table before building future reports.

Refresh updates the summary; it does not repair inconsistent source values or decide that two differently spelled categories should be merged. If you correct a label in the source, refresh and check whether the revised category appears as expected.

Common Problems and How to Fix Them

  • The PivotTable Fields pane disappeared: Click inside the PivotTable, then choose PivotTable Analyze > Field List. The contextual PivotTable tabs appear when the PivotTable is selected.
  • Some records or categories are missing: Check whether the source range covers every row and whether filters are active. Also inspect the source for blanks or inconsistent category labels.
  • Dates appear as individual days: If the source contains valid dates, right-click a date in the PivotTable and choose Group, then select a suitable interval such as Months or Years. Grouping may fail if the source date field contains text, blanks, or invalid entries; clean those values first.
  • The same category appears more than once: Look for differences such as trailing spaces, alternate spellings, or text-versus-number values in the source. A PivotTable groups values as represented; it does not automatically standardize them.
  • The report is stale after an edit: Refresh it and verify the source range. Refreshing cannot include rows outside a fixed source range unless you change the source.

Another common misconception is that dragging a field into Values always means “add these numbers.” Excel may count a field instead, particularly when it detects text or mixed data. Always read the value heading—such as Sum of Sales or Count of Sales—and verify that the displayed calculation answers the question you intend to ask.

When to Use a PivotTable Instead of Another Tool

Choose a PivotTable when you have a structured list and want a flexible summary by one or more categories. It is especially useful for quick comparisons, totals, counts, and date-based overviews. It also gives readers filters and rearrangeable views without requiring them to edit the source records.

Use formulas when a result must be calculated in a particular cell, shown alongside other calculations, or updated according to a custom rule. Use Excel’s regular Sort and Filter commands when you only need to inspect or order individual records rather than summarize them. Power Query is often a better fit for repeatable data cleaning and combining files; it prepares data, while a PivotTable can summarize the prepared result. These tools can also be used together.

Frequently Asked Questions

Are PivotTables difficult for beginners?

The first report usually requires only choosing a source and dragging a few fields into areas. The main learning curve is understanding the structure of the source data and checking whether Excel is summing, counting, or otherwise calculating a field as intended.

Can I make a PivotTable from text data?

Yes. Text fields work well as row labels, column labels, and filters. If you put a text field in Values, Excel will generally count its entries rather than sum them, because text has no numeric total.

Can a PivotTable update automatically?

Editing the source does not always update the report immediately; use Refresh or Refresh All. A PivotTable based on an Excel Table can pick up newly added table rows on refresh. A fixed range may need its source changed to include additional rows.

Why is my PivotTable counting instead of summing?

Excel may have interpreted the value field as text or found nonnumeric content in that column. Check the source values, convert genuine numeric entries to numbers, refresh, and select Sum in Value Field Settings if needed.

Can I use one source list for more than one PivotTable?

Yes. You can create multiple PivotTables from the same source and arrange each to answer a different question, such as sales by region in one report and sales by product in another. Refresh each report when the underlying data changes.

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.