Can Excel Generate a PivotTable? How to Create One

How-to basics · Updated November 19, 2026

Yes. Excel can generate a PivotTable from a range of data or an Excel table: select a cell in the data, choose Insert > PivotTable, select where to put the report, and arrange fields in the PivotTable Fields pane. A PivotTable summarizes and reorganizes records without changing the source data, making it useful for questions such as total sales by region or order count by month.

How a PivotTable works

A PivotTable groups records by fields in your source and calculates a summary for each group. Think of a source list where each row is one transaction and the columns contain fields such as Date, Region, Product, and Sales. You can place Region in the row area and Sales in the values area to see sales totals by region. Add Product as a column field to compare products across regions.

The four main areas in the field pane determine the report layout:

  • Rows: Categories shown down the left side, such as regions or product names.
  • Columns: Categories shown across the top, such as sales channels or years.
  • Values: Numbers or counts summarized in the body of the report.
  • Filters: Fields used to limit the whole report, such as a selected year.

Moving a field between these areas changes the view, not the underlying records. This is why one source list can support many different summaries.

Prepare the source data

Good source data makes the PivotTable easier to build and less likely to produce confusing results. Use one header row, with a clear and distinct name for each column. Keep each row as one record, and avoid blank rows or columns inside the dataset. For example, do not insert a separate title row above the headers or put a subtotal between groups of transactions.

Keep each column consistent: dates should be real Excel dates, amounts should be numeric, and a field such as Region should contain category labels rather than a mixture of labels and totals. A blank cell in a field can be meaningful, but a column containing both numeric values and text may not summarize as expected. Remove existing subtotals if they would be counted again in the PivotTable.

For an expanding dataset, convert the source range to an Excel table using Insert > Table and confirm that the table has headers. Give the table a useful name from the Table Design tab if you want to identify it easily. A PivotTable based on a table can include newly added table rows after you refresh the report; a PivotTable based on a fixed cell range may need its source range changed when records are added outside it.

Create a PivotTable in Excel for Microsoft 365

These steps apply to Excel for Microsoft 365 on Windows. Start with a cell inside your source range or table, then follow this sequence:

  1. Choose Insert > PivotTable. If Excel has not identified the intended data, select the correct table or cell range in the dialog.
  2. Choose New Worksheet to place the report on a separate sheet, or choose Existing Worksheet and specify a destination cell.
  3. Review the selected source and destination. Select Add this data to the Data Model only if you need to analyze related tables or use Data Model features.
  4. Select OK. Excel creates an empty PivotTable and opens the PivotTable Fields pane.
  5. Drag fields into Rows, Columns, Values, or Filters, or select their checkboxes and adjust their placement.

A new worksheet is usually the clearest choice for a first report because it keeps the summary separate from the source. If you put a PivotTable on an existing sheet, choose a clear area with enough room for it to expand; it can overwrite cells as its layout grows.

In Excel for Mac, the usual route is also Insert > PivotTable, though dialog details and ribbon labels can differ by release. Excel for the web also has PivotTable features, but some advanced options may be unavailable compared with the desktop app. If you do not see an option described here, check the Excel version and platform before assuming the source data is at fault.

Arrange fields to answer a question

Start with a specific question rather than adding every field. For example, imagine a transaction table with Date, Region, Product, and Sales columns. To answer “What were sales by region?”, place Region in Rows and Sales in Values. To compare product categories within each region, place Product in Columns as well. For a report of monthly sales, place Date in Rows and Sales in Values; Excel may group a date field into years, quarters, or months, depending on the data and the available grouping options.

Field placement is flexible. Move Product from Columns to Rows to show a longer list, or drag Region to Filters to display one selected region at a time. You can put more than one field in Rows or Columns to create nested groups, such as Region followed by Product. Use the expand and collapse controls beside grouped labels to show more or less detail.

For a quick check of the report, compare its grand total with the source data total. If the totals differ, inspect filters, missing records, mixed data types, and whether the source includes all intended rows. A PivotTable is a summary, not a guarantee that the source itself is complete or correct.

Choose the right calculation

Excel commonly sums numeric fields placed in Values and counts nonnumeric fields. It can also count numeric entries, calculate averages, and display other summaries. If Sales appears as a count instead of a sum, right-click a value in that field and choose Summarize Values By > Sum, or use Value Field Settings from the field menu. If Sum is unavailable or gives an unexpected result, check whether the source values are numbers rather than numbers stored as text.

To change a measure, open the menu for the field in the Values area and select Value Field Settings. The available choices include summary calculations such as Sum, Count, Average, and Max, as well as display calculations such as percentage of a total. The exact choices depend on the field and PivotTable type. You can also set number formatting through the value field settings so currency, dates, or decimal places display consistently in the report.

For example, a sales report can show Sum of Sales by Region, while an order identifier field can show Count of Order ID. A simple count counts populated entries; it is not automatically a distinct count. Distinct Count is available for PivotTables that use the Data Model, so it is not the right choice to assume for every PivotTable.

Filter, sort, and make the report easier to read

Use a report filter when you want to limit the whole PivotTable to a selected value. Row and column labels also have filter menus for choosing individual categories, sorting items, or applying label and value filters. For instance, a value filter can focus a region report on categories whose sales exceed a chosen threshold. Filters affect the summary view; they do not delete or edit source records.

Slicers provide clickable buttons for filtering a PivotTable, and timeline controls can filter date-based PivotTables. To add one, select the PivotTable and look for the relevant Insert Slicer or Insert Timeline command on the PivotTable Analyze tab. Availability and command placement can vary by platform and PivotTable setup. A slicer is especially handy when someone else will use the workbook, because the active filter is more visible than a small dropdown.

Use a PivotChart when a chart linked to the summary would help reveal comparisons or trends. It can be created from the PivotTable using the PivotTable Analyze or Insert tab, depending on the version and ribbon layout. Keep the report readable: too many row fields, column fields, and filters can make a useful summary difficult to interpret.

Refresh the PivotTable when data changes

A PivotTable does not automatically recalculate its report whenever you edit or append source records. After changing the source, select the PivotTable and choose PivotTable Analyze > Refresh. To update several reports in a workbook, use Data > Refresh All. On Mac, the same general refresh options are available, though tab names and placement can vary slightly.

Refreshing updates the summary from the current source; it does not necessarily expand a fixed source range to include records added beyond its boundary. An Excel table is often the simpler source for growing data: append records to the table, then refresh. If the report still omits new rows, select the PivotTable and use PivotTable Analyze > Change Data Source to check or replace its source.

Refresh may also affect formatting or the order of items, depending on the report settings. If a report is shared, make the refresh step part of the workflow after importing or updating data. For workbooks connected to external sources, refresh behavior can also depend on connection permissions and settings.

Common problems and how to fix them

  • Insert PivotTable is unavailable or the source is not recognized: Check that you selected a cell within a normal range or table, and that the workbook or sheet is not protected in a way that prevents the action.
  • Sales is counted instead of summed: Inspect the source column for text values, inconsistent entries, or numbers stored as text, then change the value summary or correct the source and refresh.
  • A date field will not group by month: Check for blank cells, text that only looks like a date, or invalid date entries. Correct the source values, refresh, and try grouping the date field again.
  • New records do not appear: Refresh the PivotTable and confirm its source includes the new rows. A fixed range does not automatically cover rows entered outside that range.
  • Labels appear more than once: Look for inconsistent spelling, extra spaces, or different underlying values that look alike. Clean the source categories, then refresh.

When a result seems wrong, inspect the source and the report filters before rebuilding the PivotTable. A selected filter, a field placed in the wrong area, or an incorrect calculation can make a valid source appear incomplete. Keeping a copy of the original source sheet also makes it easier to diagnose changes.

When to use a PivotTable, and alternatives

Use a PivotTable when you want to summarize, compare, filter, or rearrange a dataset interactively without writing formulas for each group. It is a strong choice for recurring reports, exploratory analysis, and totals across categories. It is also useful when a reader needs to change the grouping themselves.

A regular Excel table with filters may be enough if the goal is simply to inspect or sort individual records. SUMIFS or COUNTIFS formulas can work well when a report needs a fixed layout and values that update directly as source cells change, but formulas require more setup and maintenance as categories change. Power Query is better suited to repeatable data-import and transformation steps; it can prepare data for a PivotTable, but it is not itself the same kind of interactive summary.

For very large or related datasets, Excel’s Data Model and Power Pivot features can support analysis across multiple tables, subject to the Excel edition and platform. That setup is more involved than a basic PivotTable. Start with a normal range or table when one clean dataset answers the question; use the more advanced options only when the data or analysis requires them.

Frequently Asked Questions

Can Excel automatically create a PivotTable?
Excel can suggest a layout through Insert > Recommended PivotTables, where available. Review the suggested summaries and select one to create it; the result is a starting point that you can modify in the field pane.

Does creating a PivotTable change my original data?
No. A PivotTable displays a summary of its source and does not rewrite the source records. Editing the source still requires refreshing the PivotTable to update its report.

Can a PivotTable update when I add more rows?
It can include added rows when its source is an expanding Excel table, but you generally still need to refresh the report. With a fixed range, change the source if the new rows fall outside it.

Can I make a PivotTable from multiple sheets?
Yes, but combining separate tables may require the Data Model or another preparation step. If the sheets have the same column structure, you can often append their records into one table first; that is simpler for a basic PivotTable.

Why does my PivotTable show Count instead of Sum?
Excel may choose Count when the value field contains text or mixed types. Check that the source values are numeric, correct the data if needed, refresh, and select Sum in the value field settings.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.