To create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in your data, choose Insert > PivotTable, confirm the table or range, select New Worksheet, and click OK. In the PivotTable Fields pane, place fields in Rows, Columns, Values, or Filters to build a summary; for example, put Region in Rows and Sales in Values to total sales by region.
What a PivotTable does
A PivotTable summarizes a larger list without requiring you to rearrange or rewrite the source data. You choose which fields to group and which numeric fields to calculate. Excel then generates a compact report that can be rearranged by moving fields between areas. This makes it useful for questions such as how much each store sold, which product category had the most orders, or how many transactions occurred in each month.
Suppose a sales list has columns for Date, Region, Product, Units, and Revenue, with one transaction per row. A PivotTable can show Revenue by Region, then break each region down by Product. Moving Product from Rows to Columns changes the layout without changing the original list. The source records remain intact; the PivotTable is a separate summary view.
Despite its name, a PivotTable does not require a formal database. It does, however, work best when the source is a consistent, rectangular list with a header row and no blank rows or columns inside it.
Prepare the source data first
Good source data prevents many PivotTable problems. Use one column for each kind of information and one row for each record. For example, a sales table should have a single Revenue column rather than separate columns for each region. Give every column a unique, nonblank heading, such as Date or Revenue. Avoid merged cells, subtotals inserted between records, and decorative blank rows within the data.
Check that each column contains a consistent type of value. If a Revenue column contains numbers mixed with entries such as “pending,” Excel may treat the field as text and count records instead of summing revenue. Dates should be genuine Excel dates, not text that only looks like a date. Remove or correct errors in the source before building a report when practical.
It is usually helpful to convert the source range to an Excel Table. Click inside the data and choose Insert > Table, or press Ctrl+T on Windows. Confirm that My table has headers is selected, then click OK. A Table expands when you add records directly below it, so it is a more dependable PivotTable source than a fixed range. On Mac, use Command+T to open the Create Table dialog; the ribbon path is also available.
Create a PivotTable step by step
- Click a cell inside the source data or Excel Table. You do not need to select every cell if the list is contiguous and has a header row.
- On the ribbon, choose Insert > PivotTable. Excel opens a dialog showing the proposed table or range.
- Check that the source is correct. Under Choose where you want the PivotTable to be placed, select New Worksheet for a separate report sheet, or Existing Worksheet and specify a destination cell.
- Click OK. Excel creates an empty PivotTable layout and opens the PivotTable Fields pane.
- Choose fields in the pane or drag them into Rows, Columns, Values, and Filters. Start with one category in Rows and one numeric measure in Values, then add fields as needed.
For the sales example, drag Region to Rows and Revenue to Values. Excel displays a total for each region and usually adds a grand total. Add Product to Columns to compare products across regions. The same steps apply to a basic list of expenses, inventory, survey responses, or event registrations; the field choices determine what the summary answers.
Excel for Mac also provides PivotTable creation from the Insert tab, though the dialog and pane can look different from the Windows version. Excel for the web supports creating and working with PivotTables, but some advanced options and the arrangement of commands can differ. If a particular command is unavailable in the browser, open the workbook in the desktop app.
Arrange fields and choose a calculation
The four areas in the field pane control different parts of the report:
- Rows: Groups items vertically, such as regions, employees, or product names.
- Columns: Places categories across the top, useful for side-by-side comparisons such as sales by region and year.
- Values: Calculates a summary, such as a sum, count, or average.
- Filters: Adds a report-level filter so a reader can display a chosen subset, such as one department or year.
Excel generally sums a field containing numeric values when it is added to Values. It may use Count when the field contains text or a mixture of values. To change the calculation, select the field in the Values area, open its drop-down menu, choose Value Field Settings, and select a calculation such as Sum, Count, or Average. Available choices depend on the data and the field type.
Use Count when you want to count records, not add their values. For example, counting Order ID can show the number of orders in each region. If Excel displays Count of Revenue when you expected Sum of Revenue, inspect the source column for text, blanks, or inconsistent entries, then correct the data and refresh. Changing the summary calculation does not repair the underlying values.
Format and explore the summary
Once the basic report is correct, make it easier to read. Click the PivotTable and use the PivotTable Design tab to select a style, show or hide headers, and adjust report layout. For currency or percentages, right-click a value, choose Number Format, and set the format there. Applying number formatting through the value field settings is often more reliable than formatting individual cells, because a refresh can change which cells contain results.
Use the field drop-down arrows in the report to sort or filter categories. A filter can show only selected products, while sorting can bring the largest totals to the top. For interactive filtering, select the PivotTable and choose PivotTable Analyze > Insert Slicer, then choose a field. A slicer presents clickable buttons for filtering. For date-based reports, PivotTable Analyze > Insert Timeline can provide a visual date filter when the source contains valid dates.
Dates can often be grouped into months, quarters, or years. Right-click a date in the PivotTable and choose Group, then select the intervals you want. If Group is unavailable or Excel reports that it cannot group the selection, check for blank cells, text dates, or invalid date values in that source field. A PivotChart can also provide a chart connected to the summary; select the PivotTable and choose PivotTable Analyze > PivotChart.
Refresh after changing the source
A PivotTable does not always update immediately when source values change. After editing or adding records, right-click inside the PivotTable and choose Refresh, or select PivotTable Analyze > Refresh. To refresh multiple connections and PivotTables in a workbook, use Data > Refresh All. On Windows, Alt+F5 refreshes the selected PivotTable; Ctrl+Alt+F5 refreshes all data connections and PivotTables. Keyboard shortcuts can differ on Mac, so use the ribbon or context menu there if a shortcut is unavailable.
If the PivotTable is based on an Excel Table, newly added rows are included in its source after a refresh. If it is based on a fixed cell range, records added beyond that range may be left out. Check the source through PivotTable Analyze > Change Data Source and expand the range, or convert the source to a Table and build the PivotTable from that. Refresh updates the summary from its existing source; it does not automatically correct an incorrect source selection.
Common problems and how to fix them
- The PivotTable command is unavailable or the result is wrong: Check that the selection is inside a normal data list and that the source has a clear header row. Also check whether the worksheet or workbook is protected.
- Excel counts instead of summing: Look for text values, spaces, or mixed types in the numeric source column. Correct the source values, then refresh and set the desired summary calculation.
- New records do not appear: Refresh the PivotTable and confirm that its source includes the new rows. An Excel Table helps avoid a range that stops too early.
- Repeated or missing categories appear: Check for inconsistent spelling, leading or trailing spaces, and blank entries in the source. For example, “North” and “North ” may be treated as separate labels.
- Dates will not group: Make sure all entries in the date field are valid dates and that there are no blanks or text values preventing grouping.
When a report looks unexpectedly large or detailed, inspect the fields in Rows and Columns. Placing a unique identifier such as Invoice Number in Rows creates one line per invoice, which may defeat the intended summary. Removing that field or moving it to Filters can restore a concise view.
When to use a PivotTable or another tool
A PivotTable is a strong choice for interactive summaries that people need to regroup, filter, and refresh. It is often faster to build than a report made from many formulas, especially when users need to explore the same source from several angles. It is also useful when the exact categories or number of records change over time.
A regular Excel Table with filters may be enough when the goal is simply to view, sort, or filter individual records. Formulas can be preferable when a report must fit a fixed layout or update cell by cell as inputs change. Power Query is designed for importing and reshaping data, such as combining files or cleaning columns; it can prepare data for a PivotTable but is not itself a substitute for the summary view. For very large or related datasets, Excel’s Data Model and Power Pivot offer more advanced options, though they require additional setup and are not necessary for most basic summaries.
Frequently Asked Questions
Can I create a PivotTable from multiple worksheets?
Yes, but the best method depends on the data structure. For similar tables that need combining, use Power Query to append them into one clean table, then create a PivotTable from the result. Excel also has options for working with the Data Model and multiple related tables.
How do I update a PivotTable when I add data?
Refresh it using right-click > Refresh or PivotTable Analyze > Refresh. If new rows are not included, check the source range. A PivotTable based on an Excel Table can include added rows after a refresh.
Why does my PivotTable show Count instead of Sum?
Excel may have detected text or mixed values in the source field. Check that the column contains numeric values consistently, refresh the PivotTable, and use Value Field Settings to select Sum.
Can I change the source data after creating a PivotTable?
Yes. Edit the source and refresh, or choose PivotTable Analyze > Change Data Source to select a different range or table. Changing the source does not remove the need to refresh the summary.
Can I make a PivotTable without headers?
A normal PivotTable source needs a header for every column. Add a descriptive, unique heading to each column before creating the PivotTable; blank or duplicate headings can prevent Excel from interpreting the source correctly.
Related
- Excel Group Duplicate Rows Without Losing Data
- Excel Group Rows: How to Expand and Collapse Rows Easily
- Excel File Corrupted? How to Recover and Repair It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Charts: Can They Show Grand Totals?Excel PivotCharts do not plot a PivotTable’s grand-total row or column as chart data. You can show the…
- Open Excel Power Pivot: Enable and Use the Add-InTo open Power Pivot in Excel for Microsoft 365 on Windows, enable its add-in: select File > Options…
- Steps Progress Chart in Excel: Build a Clear TrackerTo create a steps progress chart in Excel, list each step and its completion percentage, calculate the remaining…
- Excel Waterfall Charts: Show Multiple Data Sets?Excel’s built-in Waterfall chart is designed to show one running sequence of values at a time, not several…
- Edit a Pivot Table in Google Sheets: Steps and TipsTo edit a pivot table in Google Sheets, click any cell inside it to open the Pivot table…
- Pivot Chart Line Graphs in Excel: How They WorkYes. In Excel, a PivotChart line graph displays values from a PivotTable as a line chart, making it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.