Pivot tables for monitoring data
Learn how to use Excel pivot tables to summarise monitoring data by location, month or group, with clear steps, a worked layout and common mistakes.
Short answer
A pivot table is an Excel tool that totals and counts your data by any column without formulas. Put clean monitoring data in a table with one header row, choose Insert > PivotTable, then drag fields into Rows, Columns and Values. You can summarise participants by location and month in minutes. Refresh the pivot table whenever you add new records.
Key points
- Start with clean data: one header row, no blanks, no merged cells.
- Drag fields into Rows, Columns, Values and Filters to build a summary.
- Use Count for records and Sum for numbers, and check which one Excel chose.
- Refresh the pivot table after you add or change data.
- Copy final results to a report sheet and keep the pivot table as your working tool.
What is a pivot table, and why use it for monitoring?
Monitoring data grows quickly. After a few months of distributions, trainings or surveys, you may have thousands of rows. Your donor wants totals by location, month, sex and age group. Writing formulas for every combination takes time and invites errors.
A pivot table solves this. Microsoft describes it as a tool to calculate, summarise and analyse data that lets you see comparisons, patterns and trends. You choose the columns, and Excel builds the summary. It is one of the most useful skills for MEAL and programme staff.
How do you build a pivot table?
Step 1: Prepare your data
A pivot table is only as good as the data under it. Microsoft's guidance is clear on the layout:
- Organise data in columns, with one header row of unique, non-blank labels.
- Avoid double header rows and merged cells.
- Do not mix data types in a column, for example dates and text.
- Use an Excel table as the source, because rows you add to a table are included when you refresh.
Clean the data before you build anything. Check spelling of location names, remove duplicate records and make sure numbers are stored as numbers. Our guide to data quality checks lists simple checks.
Step 2: Insert the pivot table
- Click any cell in your data table.
- Select Insert > PivotTable.
- Choose New Worksheet and select OK.
- A blank pivot table and a PivotTable Fields pane appear.
Step 3: Drag fields to build the summary
The Fields pane has four areas.
| Area | What it does | Example |
|---|---|---|
| Rows | Lists unique values down the left | Township |
| Columns | Lists unique values across the top | Month |
| Values | Calculates a number | Sum of participants |
| Filters | Lets you narrow the whole table | Activity type |
A worked example: your attendance sheet has the columns Date, Township, Activity, Sex and Participants. Drag Township to Rows, Month to Columns and Participants to Values. You now see participants by township and month. Drag Sex to Columns under Month to disaggregate. Drag Activity to Filters to view one activity at a time. For more on why to split results by group, see disaggregated data.
Step 4: Check the calculation
By default, numeric fields in Values use Sum. Microsoft notes that if Excel reads your data as text, it shows a Count instead. If your totals look like record counts, the column probably holds text or blanks. Fix the data, then refresh.
To change the calculation, click the arrow next to the field and choose Value Field Settings. Use:
- Sum to add numbers, such as participants or money.
- Count to count records, such as the number of visits.
- Average for a mean, such as average score.
Step 5: Refresh when data changes
A pivot table does not update by itself. Microsoft explains that it works on a snapshot of your data, so you must refresh after adding records. Right-click the pivot table and choose Refresh. If you have several, use Refresh All.
Step 6: Move results into your report
Treat the pivot table as your working tool. For a report, copy the final numbers into a clean sheet or a Word table, add a title and a source note, and check one total against the source data. This avoids changes that you do not mean to make when you edit a pivot table.
Common mistakes
- Merged cells or two header rows. The pivot table will not build correctly.
- Different spellings of one place. "Hpa-an" and "Hpaan" show up as two rows. Use drop-down lists when you collect data.
- Forgetting to refresh. You report old numbers.
- Counting people twice. If one person appears in several rows, a Sum of participants counts them each time. Add a unique ID and count unique people if your version of Excel supports it, or deduplicate first.
- Sharing personal data. A pivot table file still contains the raw data. Remove names and contact details before sharing.
Where to go next
Once you can summarise data, the next step is to show it in a dashboard. Read our guides on Power BI basics for programme dashboards and dashboards for MEAL.
How EMPO can help
EMPO is a trusted Yangon-based training and consultancy provider and one of Myanmar's leading names in practical training for NGOs and CSOs, with 1,200+ people trained and 15 partner organisations. We provide MEAL and data training online or in person across Myanmar, in English and Burmese, and can design a session around your own monitoring files. See our training services or contact us.
Sources
- Create a PivotTable to analyze worksheet data — Microsoft Support
- Overview of Excel tables — Microsoft Support
Questions people ask
Why does my pivot table show Count instead of Sum?
Excel counts a column when it reads some of its values as text. Check for numbers stored as text or blank cells, fix them, then refresh. You can also change the calculation in Value Field Settings.
Do pivot tables change my original data?
No. A pivot table reads a snapshot of your data and does not change the original. If you add new data, you must refresh the pivot table to see it.
Can I make a pivot table from KoboToolbox data?
Yes. Export the data to Excel, clean the header row, convert it to a table and insert a pivot table. Remove personal data from the export before sharing the file.