To create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data range, choose Insert > PivotTable > From Table/Range, confirm the source and destination, and select OK. Then drag fields into Rows, Columns, Values, and Filters to summarize the data; for example, put a sales region in Rows and an amount in Values to see sales by region.
What a PivotTable does
A PivotTable reorganizes and summarizes rows of source data without changing the original records. Instead of manually adding totals or copying rows into separate reports, you choose which fields to group and which numeric fields to summarize. You can rearrange those choices as questions change, such as switching from sales by region to sales by month.
Think of each source row as one record, such as one order, and each column as an attribute of that record: Order Date, Region, Product, and Amount. A PivotTable can count orders, add amounts, or group records by a category. It does not repair inaccurate source data, and it is not a live formula result: after source values change, you generally need to refresh the PivotTable.
Prepare the source data
Good source data makes PivotTables easier to build and less likely to produce confusing totals. Use a simple rectangular list: one header row followed by records, with one kind of information in each column. Avoid blank rows or columns inside the list, merged cells, and multi-row headings. Every column needs a distinct, nonblank header, such as “Order Date” or “Net Sales.”
- Keep one record per row. If an order has several products, store each order line as its own row rather than putting multiple products in one cell.
- Use consistent data types within a column. Store dates as dates and amounts as numbers, not a mixture of numbers and text such as “$25” typed into some cells.
- Check for unexpected spaces, spelling variations, and blank values in fields you plan to group. “North,” “north,” and “North ” can behave like separate labels.
- For a source that will grow, convert the range into an Excel Table first: select a cell in the data and choose Insert > Table, confirm that the table has headers, and select OK.
An Excel Table is useful because new rows added directly below it become part of the table. The PivotTable still needs a refresh to show those new records, but its source range is less likely to remain stuck at the original row count.
Create a PivotTable step by step
- Select the source. Click any cell in the data range or Excel Table. Excel usually detects the full contiguous list. If the detected range is wrong, cancel and select the correct range before opening the PivotTable dialog.
- Open the creation dialog. On the Windows ribbon, choose Insert > PivotTable > From Table/Range. Check the displayed table or range name so the report is based on the intended data.
- Choose where it goes. Select New Worksheet for a separate report sheet, or choose Existing Worksheet and specify a destination cell. A separate sheet is usually easier to manage, especially when the source list is on another sheet.
- Build the summary. In the PivotTable Fields pane, drag fields into the four layout areas. Put a category such as Region in Rows, a numeric measure such as Net Sales in Values, and optionally a field such as Year in Filters.
- Check the result. Make sure the displayed totals and categories match the question you meant to answer. If a numeric field appears as a count rather than a sum, or dates appear as individual entries, adjust the field settings or investigate the source values.
In Excel for Mac, the basic process also starts from Insert > PivotTable; the dialog and ribbon arrangement can differ slightly by release. Excel for the web also supports PivotTables, though available features and the exact interface can vary. The instructions here describe the Windows desktop version of Microsoft 365.
Understand the four field areas
The field areas determine how the report is arranged. A field is a source column; placing it in a different area changes the view, not the underlying data.
- Rows: Lists categories down the left side. Use Region, Product, or Salesperson to compare groups.
- Columns: Spreads categories across the top. For example, put Year in Rows and Quarter in Columns to compare quarters within each year.
- Values: Calculates a summary, usually from a numeric field such as Units or Revenue. You can add more than one value field to compare measures.
- Filters: Adds a report-level selector above the PivotTable. Use it when you want to view one year or department at a time without changing the row and column layout.
For a concrete example, suppose a sales list has Order Date, Region, Product, Units, and Revenue. Drag Region to Rows and Revenue to Values to see revenue by region. Add Product below Region in Rows to see each region broken down by product. Move Product to Columns instead to compare products side by side.
Choose the right summary and number format
Excel commonly sums a numeric field placed in Values and counts a field containing text. If a field is being counted unexpectedly, open its menu in the Values area, choose Value Field Settings, and select the appropriate calculation, such as Sum, Count, or Average. The available choices depend on the kind of source data and the PivotTable type.
A surprising count can indicate that some entries in an otherwise numeric column are stored as text, or that Excel has interpreted the field differently than intended. Inspect the source column before accepting the result. Also distinguish Count, which counts nonblank entries, from Count Numbers, which counts numeric entries; they can give different answers when blanks or text values are present.
Set number formatting through Value Field Settings > Number Format when you want a consistent format for the PivotTable measure. This is preferable to formatting individual cells because the field-level format is more likely to remain consistent when the PivotTable is refreshed or rearranged. For a revenue total, choose a currency or number format suited to the data rather than changing the source values just to display a symbol.
Filter, sort, and group the report
Use the drop-down controls beside row or column labels to filter particular categories, or sort labels and values to bring important results to the top. For instance, sorting a regional sales summary by its value can quickly reveal the highest and lowest totals. A filter hides records from the current view; it does not delete them from the source.
For interactive filtering, select a cell in the PivotTable and choose PivotTable Analyze > Insert Slicer, then select the fields to use as buttons. A slicer is often clearer than a drop-down when a report will be used by someone who needs to change categories quickly. To filter date-based reports by periods, a Timeline may be available through PivotTable Analyze > Insert Timeline when the PivotTable contains a suitable date field.
Excel may group date fields automatically, or you can right-click a date in the PivotTable and choose Group to select intervals such as months, quarters, or years. Grouping works best when the source values are real dates. Text that merely looks like a date may not group correctly. If you see individual dates instead of months, check the source data and try grouping the date field.
Refresh and maintain the PivotTable
A PivotTable is a summary built from a source range or table, so edits to the source do not necessarily appear immediately. To update it, right-click inside the PivotTable and choose Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables together. Refresh after changing source values or adding records.
If new rows are missing, check the PivotTable’s source rather than assuming the refresh failed. A PivotTable based on a fixed cell range may not include records added below that range. Convert the source to an Excel Table, then set or update the PivotTable source to use that table. To inspect or change the source, select the PivotTable and use PivotTable Analyze > Change Data Source.
Keep the source and report easy to understand: give the source columns meaningful headings, avoid placing unrelated notes inside its data range, and consider naming the Excel Table from the Table Design tab. If the workbook is shared, explain which sheet contains the source and which filters have been applied. A filtered report can be accurate while showing only part of the available data, so make the current selection visible to readers.
Common problems and how to fix them
- “The PivotTable field name is not valid.” Check that every source column has a nonblank heading and that there are no merged or irregular header cells. Confirm that the selected source starts at the actual header row.
- The total is a count, not a sum. Set the calculation in Value Field Settings, then check for numbers stored as text or other nonnumeric entries in the source column.
- New records do not appear. Refresh the report and verify that its source includes the new rows. A Table-based source is generally easier to expand reliably than a fixed range.
- Categories appear more than once. Look for spelling differences, leading or trailing spaces, or inconsistent labels in the source. Correct the source values, refresh, and review the categories again.
- Dates will not group. Confirm that the date column contains valid Excel dates and not text, blanks, or error values that interfere with grouping.
- The layout changes after refresh. Review the PivotTable’s layout and field settings. If you need a fixed, presentation-ready snapshot, copy the report and paste values into a separate area, understanding that the copied figures will no longer update as a PivotTable.
When to use a PivotTable instead of another tool
A PivotTable is a strong choice for quickly exploring a list by categories, comparing totals, or producing several views from the same source. It is especially useful when the questions may change: moving a field from Rows to Columns is usually faster than rebuilding a set of manual summaries. PivotCharts can provide a visual view of PivotTable results when a chart makes comparisons easier to understand.
A regular formula-based report may be preferable when you need a precisely arranged form, calculations that combine several sources in a custom way, or values that update immediately as source cells change. Power Query is useful for importing and transforming data before analysis, while a PivotTable is commonly used to summarize it. These tools can work together: clean or combine data first, then build a PivotTable from the resulting table. A PivotTable is not a substitute for checking whether the source is complete or whether the chosen calculation answers the business question.
Frequently Asked Questions
How do I add a field to an existing PivotTable?
Select a cell in the PivotTable to show the PivotTable Fields pane. Check a field or drag it into Rows, Columns, Values, or Filters. Its placement determines how it changes the current summary.
How do I update a PivotTable after changing the data?
Right-click inside the PivotTable and choose Refresh, or choose Data > Refresh All. If newly added rows are still missing, check whether the source range includes them.
Can a PivotTable show percentages instead of totals?
Yes. Open the value field’s settings and use Show Values As to display options such as a percentage of a row, column, or grand total. Choose the comparison that matches the question, and retain a clear label so readers understand what the percentage represents.
Why does Excel count my numbers instead of adding them?
The Values field may be set to Count, or some entries may be stored as text. Choose Sum in Value Field Settings and inspect the source column for text, blanks, or inconsistent entries that prevent a numeric total.
Can I make a PivotTable from multiple tables?
Yes, but it requires a suitable data model or another supported method for relating the tables; simply selecting separate, unrelated ranges does not make them one coherent source. For a straightforward first PivotTable, combine or prepare the records as one clean table. For related tables, use Excel’s Data Model and confirm that the relationships between them are defined correctly.
Related
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Power Sioux Falls, SD: What to KnowIf you are looking for Pivot Power in Sioux Falls, South Dakota, the name alone is not enough…
- Excel Gantt Chart Schedule Template: Build, Format, and UseAn Excel Gantt chart schedule template combines a task list with a dated timeline, then uses conditional formatting…
- Power Pivot From Multiple Excel Files: How It WorksYes. In Excel for Microsoft 365 on Windows, use Power Query to import or combine data from multiple…
- Excel Pivot Field Name Is Not Valid? Causes and FixesThe Excel error “The PivotTable field name is not valid” usually means the selected source data has at…
- How to Make a Graph in Excel From Your Spreadsheet DataTo make a graph in Excel, arrange your information in labeled columns or rows, select the cells you…
- Excel Trendline Values: How to Display and Calculate ThemTo get trendline values in Excel, select the chart’s data series, add a trendline, and tick Display Equation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.