To make a PivotTable in Excel for Microsoft 365 on Windows, put your data in a clean table with one header row, select a cell in it, then choose Insert > PivotTable. Choose where to place the report, select fields for Rows, Columns, Values, or Filters, and adjust how the values are summarized. A PivotTable lets you group and compare a large list of records without changing the original data.
What You’ll Need
- An Excel workbook containing a rectangular dataset: one record per row and one type of information per column.
- A distinct, nonblank heading for each column, such as Date, Region, Product, and Sales.
- Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac and Excel for the web have PivotTable features too, though the ribbon layout and some options can differ.
You do not need to write formulas. Before you begin, decide what question the report should answer. For example, you might want to compare total sales by product, count orders by region, or see monthly totals for each department. A clear question makes it easier to choose the right fields and layout.
Step-by-Step
-
Check and prepare the source data. Arrange the records so every row describes one item, transaction, or event, and every column contains one consistent kind of value. Put the headings in the first row and make each heading unique and nonblank. Remove completely blank rows and columns within the dataset. Avoid merged cells, subtotals, and extra title rows inside the range; these can stop Excel from recognizing the data cleanly. For example, a sales list might have Order Date, Region, Product, Quantity, and Sales as its five headings.
-
Optionally convert the data to an Excel Table. Click any cell in the dataset and press Ctrl+T on Windows. Check that the displayed range includes all the records and that My table has headers is selected, then choose OK. A table expands when you add rows beneath it, which makes it a convenient source for future refreshes. This step is optional, but it is useful when the source changes regularly. On a Mac, use the Insert tab or the available table shortcut for your Excel version; the dialog also lets you confirm the header setting.
-
Start the PivotTable. Click a cell inside the data or the Excel Table. On the ribbon, choose Insert > PivotTable. Excel should detect the full source range or table name. Check the range before continuing: if it leaves out recent records, cancel and select the complete data first. In the Create PivotTable dialog, keep Select a table or range selected unless your data is already in a supported external source.
-
Choose where the report will go. In the dialog, select New Worksheet to put the PivotTable on a separate sheet, which is usually easiest to read and maintain. Choose Existing Worksheet only when you have a clear space for the report; then specify a blank starting cell. Avoid placing it over source records or other content because the report may expand and overwrite cells. Select OK to create the empty PivotTable and open the PivotTable Fields pane. On Mac or Excel for the web, the command names are similar, but the dialog may be arranged differently.
-
Choose the row categories. In the PivotTable Fields pane, select a field or drag it into the Rows area. For a sales report, drag Product to Rows to list each product on its own line. Use a field with repeated categories, such as Region or Department, when you want to compare groups. If the field contains many unique entries, the report can become long; choose a broader category or add filters to make it useful.
-
Add the values to calculate. Drag a numeric field, such as Sales or Quantity, to Values. Excel commonly summarizes numeric data by Sum. If it shows Count instead, check that the source values are actually numbers and do not contain text, spaces, or error values. To select a different calculation, click the field’s drop-down in the Values area, choose Value Field Settings, select an option such as Sum, Count, or Average, and choose OK. Sum adds the values, Count counts nonblank entries, and Average calculates their mean.
-
Add a second dimension or a filter if needed. Drag another category field to Columns to compare categories across the top. For example, put Region in Rows, Product in Columns, and Sales in Values to see sales by product and region. Drag a field to Filters to show a report for a chosen item, such as one year or salesperson. You can also use the drop-down buttons on the PivotTable headings to filter displayed row or column items. Keep the layout simple at first; extra fields can make the report harder to interpret.
-
Format and review the result. Click a value in the PivotTable, then use the Home tab’s number-format controls to show currency, whole numbers, or percentages as appropriate. You can also right-click a value and choose Number Format. Confirm that the report answers your original question and that totals make sense against the source records. The PivotTable uses its own layout and does not rearrange or edit the underlying data.
-
Refresh after changing the source. PivotTables use a stored snapshot, so edits to existing source values may not appear immediately. Click inside the report and choose PivotTable Analyze > Refresh. To update every PivotTable in the workbook, choose Data > Refresh All. If you added records to an ordinary cell range and they are outside the original source, edit the source range using PivotTable Analyze > Change Data Source. If you used an Excel Table, add records to that table and refresh.
Common Mistakes
-
Headers are missing or repeated. A blank heading or two columns with the same heading can prevent Excel from building the field list properly. Give every column a distinct name before inserting the PivotTable.
-
Numbers are stored as text. Values that look numeric may be treated as text if they include leading apostrophes, extra spaces, or inconsistent entries. Excel may count them rather than sum them. Clean the source values, verify they are numeric, and refresh the report.
-
The report does not include every record. A manually selected range can miss new rows or data beyond a blank row. Confirm the source address in the Create PivotTable dialog, or use an Excel Table so appended records are easier to include.
-
Too many fields are added at once. A crowded layout can be difficult to read and may seem to produce duplicated results. Start with one row field and one value field, check the output, then add further fields deliberately.
-
Changes are expected to show automatically. A PivotTable usually needs refreshing after source edits. Refresh it before treating the displayed totals as current.
Tips
-
Use headings that describe the information clearly, such as Order Date instead of Date1. Clear names are easier to recognize in the field list.
-
To group a date field by month or year, place it in Rows, right-click one of the displayed dates, and choose Group. Select the time units you need. Grouping works best when the source contains valid Excel dates rather than date-like text.
-
Use a slicer when you want a clickable filter. Select the PivotTable, then choose PivotTable Analyze > Insert Slicer and select the category fields to use. A slicer is especially helpful when sharing a report with someone who may not know how to use filter menus.
-
Keep the source data and the report on separate sheets, and give the sheets descriptive names. This reduces the chance of accidentally editing the source or overwriting the report.
-
For an initial report, favor a layout that answers one question clearly. You can always rearrange fields later by dragging them between Rows, Columns, Values, and Filters.
Troubleshooting
The PivotTable command is unavailable or the field list is empty: Click a cell inside the source range and check that it has a single header row with no blank headings. If the source is on a protected sheet or the selection is not part of a valid range, choose or select the data again and reopen Insert > PivotTable.
Excel says the PivotTable field name is not valid: Inspect every heading cell in the selected source range. Fill in blank headings, remove merged header cells, and make sure the selected range begins with the actual header row rather than a title or blank row. Then create the PivotTable again.
The totals are wrong or show Count instead of Sum: Check a few source cells in the affected column. Look for text-formatted numbers, blank entries, or values stored with inconsistent formats. Correct the source, then choose PivotTable Analyze > Refresh. If the numbers still do not add as expected, confirm that Sum is selected in Value Field Settings.
New rows do not appear: If the PivotTable uses a fixed cell range, choose PivotTable Analyze > Change Data Source and select the expanded range. If the source is an Excel Table, make sure the new records are actually part of that table, then refresh. For changes to existing records, refreshing is still necessary.
A date will not group by month or year: Check the source date column for blanks, text values, or invalid dates. Correct the entries to real Excel dates, refresh the PivotTable, and try grouping again. If you need to keep incomplete dates, remove or fill the blanks in a clean copy of the source data before grouping.
Frequently Asked Questions
Can I make a PivotTable without converting my data to a Table?
Yes. Select a cell in a clean range and use Insert > PivotTable. Converting to an Excel Table is optional, but it can make it easier to include added rows as the data grows.
Does creating a PivotTable change my original data?
No. A PivotTable creates a separate summary report. You can rearrange its fields without changing the source records, but you must edit the source itself to correct underlying data.
How do I change a total from Sum to Average?
In the PivotTable Fields pane, open the menu for the field in Values, choose Value Field Settings, select Average, and click OK. The available summaries depend on the type and quality of the source values.
Why is my date field grouped into years or months automatically?
Excel may group recognized dates in a PivotTable to make the report easier to summarize. You can right-click a grouped date label and choose Ungroup, or use Group to select the time units you prefer.
Can I edit the numbers directly in the PivotTable?
Not as a way to change individual source records. A PivotTable displays summaries; edit the matching record in the source data and refresh the report to show the correction.
Do I need to refresh a PivotTable every time I open the workbook?
Not necessarily. Refresh when source data has changed. You can also configure refresh behavior in the PivotTable’s data options, but for routine use, PivotTable Analyze > Refresh or Data > Refresh All is straightforward.
Related
- Balance C/D in Accounting: Meaning and Examples
- A/B Split Testing: Meaning, Method, and Common Mistakes
- Balance of Payments Theory: Meaning and How It Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram Template: Build One and Set BinsAn Excel histogram template is a worksheet or chart setup that groups numeric data into ranges (bins) and…
- Excel Pivot Table Collapse All: Steps, Limits, and TipsTo collapse the detail in an Excel PivotTable, right-click an item in the row or column field you…
- Excel Stacked Bar Chart With Negative ValuesYes. Excel can display negative values in a stacked bar chart: positive series values stack to the right…
- Microsoft Excel Pivot Charts: How to Create and UseA Microsoft Excel PivotChart is a chart linked to a PivotTable, so you can summarize and explore data…
- How to Rename a Legend in an Excel Pie ChartTo rename a legend item in an Excel pie chart, change the category name in the worksheet cell…
- Distinct Count in Excel Pivot Tables: A Practical GuideTo get a distinct count in a PivotTable in Excel for Microsoft 365 on Windows, create the PivotTable…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.