Written and maintained by CASRAI Editorial Board
Last updated
To do a pivot table in Excel, select your data, choose Insert > PivotTable, pick where to put it, then check fields in the PivotTable Fields pane so they land in Rows, Columns and Values. Excel puts numeric fields in Values and sums them by default, which is the step that most often needs changing for research data. This guide follows Microsoft’s current documentation for Microsoft 365 Excel for Windows, and menu labels can differ slightly in other versions.
What a PivotTable is used for in research
A PivotTable summarises a flat table of records without altering it. In research work that usually means counting participants per site or condition, totalling or comparing a measured value across groups, or collapsing dates into months or years to see how a variable moves over time. It produces descriptive summaries, not inferential tests: it will tabulate group totals, but it will not tell you whether a difference is statistically meaningful.
If your goal is a two-way table of counts that you then test with a chi-square, that workflow has its own page: Crosstabs in Excel builds the count table with a PivotTable and runs the test. This page covers the general PivotTable skills that sit underneath it: preparing the source, choosing Sum or Count, grouping, and refreshing.
Install or access (official source only)
PivotTables are built into Excel; there is nothing separate to install. The commands live on the Insert tab. Microsoft documents the feature on its support site under “Create a PivotTable to analyze worksheet data” (support.microsoft.com, checked 2026-10-29). Two version notes from that page:
- Insert > Recommended PivotTable is available only to Microsoft 365 subscribers. Every other version can still build one by hand with Insert > PivotTable.
- In Excel for the web, you change how a value is summarised by right-clicking a value and choosing Summarize Values By, rather than using the dialog described below for Windows.
Step by step: create a PivotTable
Prepare the source data
Microsoft’s guidance on source data is short and worth following before you click anything:
- Use a single header row. Avoid double rows of headers and merged cells.
- Leave no blank rows or columns inside the data.
- Keep one data type in each column.
- Where possible, format the data as an Excel table. Rows or columns added to the table are picked up the next time the PivotTable is refreshed.
For a research dataset, this means one row per record (participant, sample, observation) and one column per variable, with a plain one-line variable name at the top. The same discipline is covered more broadly in data wrangling.
Build the PivotTable (Windows)
- Select any cell in the data, or select the whole range.
- Choose Insert > PivotTable.
- Confirm the table or range Excel shows. Optionally tick “Add this data to the Data Model”.
- Choose New Worksheet or Existing Worksheet for the location, then select OK.
- In the PivotTable Fields pane, check a field to add it. By default Excel places non-numeric fields in Rows, date and time fields in Columns, and numeric fields in Values.
- Drag fields between the Rows, Columns and Values areas until the layout answers your question.
Change Sum to Count (or another summary)
Values default to Sum. If Excel reads a column as text, the default becomes Count instead. To change it on Windows:
- Select the arrow beside the field name in the Values area.
- Choose Value Field Settings.
- Under Summarize Values By, choose the summary you want.
The same dialog has Show Values As, which re-expresses values relative to a total, for example as a percentage of Grand Total.
Group dates or numbers
Per Microsoft’s grouping documentation (support.microsoft.com, checked 2026-10-29), Excel automatically detects and groups time-related fields. To group manually:
- Right-click a value in the PivotTable and choose Group.
- In the Grouping box, set Starting at and Ending at.
- Under By, choose a time period, or a numeric interval for number fields, then select OK.
To group selected items instead, hold Ctrl, select two or more values, then right-click and choose Group. To undo, right-click any item in the group and choose Ungroup. To rename a group, select it and use Analyze > Field Settings > Custom Name.
Refresh after the data changes
Right-click inside the PivotTable and choose Refresh. To refresh every PivotTable in the workbook, select any PivotTable cell, open PivotTable Analyze, and use the arrow under Refresh to choose Refresh All.
Output and how to read it
Each cell in the body of the PivotTable is one summary of all source rows that match its row and column labels. Before you read any number, check what the Values area says in its field label: “Sum of Score” and “Count of Score” look alike but answer different questions. A count tells you how many records fell in a group; a sum adds up the measurements in it. Because Excel switches to Count when it reads a column as text, a column of numbers stored as text will quietly report counts where you expected totals.
Worked illustration (hypothetical data, for explanation only): a sheet with columns Site, Visit date and Score. Put Site in Rows and Score in Values. If the table says “Sum of Score”, each cell is the total score at that site. Changing Summarize Values By to Count turns the same cells into the number of scored records at each site, which is the figure you need for a sample-size table.
For the same descriptive task inside a statistics package, see descriptive statistics in SPSS.
Troubleshooting
The Values area shows Count instead of Sum
Excel read the column as text. Microsoft states that text-read data defaults to Count. Fix the column so it holds true numbers, or change the setting under Value Field Settings > Summarize Values By.
New rows are missing
A PivotTable does not update itself when the worksheet changes. Use Refresh or Refresh All. If you added rows below a plain range, the PivotTable’s range may not include them; building from an Excel table avoids this, because rows added to the table are picked up on refresh.
The PivotTable will not build or looks wrong
Check the source against the preparation list above: a double header row, merged cells, blank rows or columns, or mixed data types in one column are the problems Microsoft’s guidance warns against.
Recommended PivotTable is missing
That command is available only to Microsoft 365 subscribers. Use Insert > PivotTable instead.
Free alternatives
| Tool | What its own documentation says |
|---|---|
| Google Sheets | Select the source cells (each column needs a header), then choose Insert > Pivot table. Add Rows, Columns and Values in the side panel. The pivot table refreshes whenever the source cells change. (Google Docs Editors Help, checked 2026-10-29.) |
| LibreOffice Calc | Has a Pivot Table feature, formerly called DataPilot, for combining, comparing and analysing large amounts of data. Its Help has separate topics on creating, editing, filtering and updating pivot tables. (LibreOffice Help, checked 2026-10-29.) |
FAQ
How do I make a pivot table in Excel step by step?
Prepare data with one header row, select it, choose Insert > PivotTable, choose the location, select OK, then check fields in the PivotTable Fields pane and arrange them in Rows, Columns and Values.
Why does my pivot table count instead of sum?
Because Excel is reading the column as text. Change the summary under Value Field Settings > Summarize Values By, and correct the column so it holds numbers.
How do I group dates in a pivot table?
Right-click a date value, choose Group, set Starting at and Ending at, pick a time period under By, and select OK.
How do I update a pivot table when my data changes?
Right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh > Refresh All for every PivotTable in the workbook.
Can I use a pivot table for a chi-square test?
A PivotTable can build the table of counts; the test itself is a separate step. See Crosstabs in Excel.
Sources
- Microsoft Support, Create a PivotTable to analyze worksheet data, checked 2026-10-29.
- Microsoft Support, Group or ungroup data in a PivotTable, checked 2026-10-29.
- Google Docs Editors Help, Create and use pivot tables, checked 2026-10-29.
- LibreOffice Help, Pivot Table, checked 2026-10-29.
