Create a Pivot Table That Shows Where the Money Went

1. Start with a clean source

Open PivotSource in the free workbook:

Use one header row, no merged header cells, numeric amounts and consistent category names. This example represents one currency; keep currencies separate in a real report.

The source is already an Excel Table named Expenses. To build your own, click inside the range, press Ctrl+T, confirm the selected cells and check My table has headers. A Table makes the source easier to extend than a fixed range that stops at last month's final row.

2. Insert the PivotTable and place two fields

Click inside the source Table, then choose Insert → PivotTable → From Table/Range. Confirm the Table name and choose a new worksheet as the destination.

In the field list:

1. Drag Category to Rows.
2. Drag Amount to Values.
3. Confirm the values field reads Sum of Amount, or an equivalent sum label.

Your summary should show 450, 300 and 600. The workbook also includes a working PivotTable on PivotSummary, so you can compare it with the one you create.

Microsoft's PivotTable instructions show the field list and the menu differences between platforms.

3. If it says Count of Amount, check the data

Count answers “how many records?” Sum answers “how much?” In this exercise, a category count of two is not the same answer as a category expense total of 450.

If Excel selected Count, inspect the source Amount column. Text amounts, blanks or inconsistent types can change how Excel interprets the field. Use a helper such as ISNUMBER to find suspect cells, correct a reviewed copy and refresh the PivotTable.

To select the calculation, open the values field's menu and choose Value Field Settings → Sum. Do not stop there if the original issue was a text amount: selecting Sum does not make every invalid source value numeric.

Use Number Format within Value Field Settings to apply the amount format to the values field, so the formatting follows the PivotTable as it changes.

4. Trace a category back to its rows

In desktop Excel, double-click a category's amount to extract the contributing detail into a new sheet. For Software, the two amounts are 200 and 250; together they explain 450.

Treat that extracted sheet as a snapshot. It is useful for checking the subtotal, but it does not become a new authoritative source or automatically stay synchronized with later changes.

When adapting the exercise to dates, filter the intended month explicitly. Use actual date values, and keep the date and currency scope visible in the report. The six-row exercise is deliberately small so the source remains easy to inspect.

5. Change a source value and refresh

On a working copy, change the first Software amount from 200 to 210. Refresh the PivotTable. Software should become 460, and the grand total should become 1,360.

Restore the source to 200 and refresh again. The expected total returns to 1,350. That checks more than the initial screenshot: it proves the PivotTable reads the source you intended.

Use PivotTable Analyze → Refresh, or right-click inside the PivotTable and choose Refresh. If adding new rows to a fixed source range, check Change Data Source as well. A refresh cannot include a row outside the defined range.

Refresh behavior varies by Excel version and connection. Newer versions can automatically refresh some local sources; older setups may need manual refresh or refresh-on-open settings. Microsoft's refresh guide describes those options.

Finish by confirming that the source sum and PivotTable grand total agree for the same filters. If the numbers differ, check source range, filters, value type and refresh state before rebuilding the report.

Download the exercise workbook and recreate the summary from PivotSource. Then turn the result into a small, checkable dashboard or use the file-combination guide when your source arrives in several exports.