Create an Excel PivotTable with Copilot: Monthly Expenses by Category
A monthly expense comparison needs more than a plausible-looking AI summary. Ask Microsoft Copilot in Excel to create an actual PivotTable linked to your source, then inspect its fields and totals. This walkthrough uses seven fictional transactions in USD, including a refund that reduces October travel spending.
Microsoft documentation checked October 21, 2026. The example below is calculated from fictional data; it is not a report of an actual Excel or Copilot execution.
1. Prepare a small, complete expense table
Use this CSV as a reference for entering or importing the data into A1:C8. Set the Month column to Text before entry to preserve YYYY-MM. Enter Amount_USD as numbers using your Excel locale’s decimal separator; the reference uses a dot in 45.50. Check the types after import. A display-format change alone does not convert text amounts to numbers.
Month,Category,Amount_USD
2026-09,Travel,120
2026-09,Supplies,45.50
2026-09,Meals,30
2026-10,Travel,80
2026-10,Supplies,60
2026-10,Meals,42
2026-10,Travel,-20Select the headers and all seven records, then use Insert > Table. Confirm that the table has headers and name it Expenses. Before building the PivotTable, =COUNT(Expenses[Amount_USD]) should return 7 and =SUM(Expenses[Amount_USD]) should return 357.50. Keep the negative travel transaction: it represents a USD 20 refund. There is one currency throughout, so no exchange rate or currency conversion is involved. The month labels are already prepared; this example requires no date grouping.
2. Ask Copilot to build the PivotTable in the workbook
Microsoft documents PivotTable creation with Copilot. Its current getting-started instructions open Copilot from the icon in Excel’s lower-right corner. Use Allow editing mode for workbook changes; Chat only keeps responses in the conversation.
Request a real Excel PivotTable on a new worksheet using Expenses: Category in Rows, Month in Columns, and the sum of Amount_USD in Values, with row and column grand totals. Specify all seven source records, retain negative refunds, and display two decimal places. Ask it to flag missing or invalid data rather than invent replacements.
The reusable monthly expense PivotTable prompt includes that layout and optional independent checks. For this example, use Expenses as the table name and supply the seven-row totals below as the checks; replace them for your own data. A successful chat response alone does not establish that the workbook contains a PivotTable: select the output and inspect Excel’s PivotTable field controls and source.
3. Verify Sum, the refund, and the expected totals
The calculated comparison should be:
On a small screen, swipe the table to see every column.
| Category | 2026-09 (USD) | 2026-10 (USD) | Grand total (USD) |
|---|---|---|---|
| Travel | 120.00 | 60.00 | 180.00 |
| Supplies | 45.50 | 60.00 | 105.50 |
| Meals | 30.00 | 42.00 | 72.00 |
| Grand total | 195.50 | 162.00 | 357.50 |
October travel is 80 + (-20) = 60. Confirm that the source still shows -20 and that no filter excludes it. A value of 80 would miss the refund. Two decimal places control presentation, not currency conversion.
If Values shows Count of Amount_USD, inspect the source for text amounts, correct them, and select Sum through Value Field Settings or Summarize Values By. Microsoft’s summary-function guide distinguishes adding amounts from counting populated values. In this example, Count would give October travel two records, not USD 60.
4. Refresh changes or use Excel’s manual route
After editing or adding source records, confirm that they belong to Expenses, then right-click the PivotTable and choose Refresh. Recheck the totals. Do not assume a source edit has already updated the displayed report.
If Copilot is missing, the current Excel FAQ points to app, license, network, privacy, and organization settings. Use an .xlsx workbook and Automatic calculation for editing support. Meanwhile, select a cell in Expenses, choose Insert > PivotTable, and place the same three fields manually. Microsoft’s PivotTable instructions cover this route and refresh. Apply the same Sum and refund checks whichever route creates the report.
For a formula-based comparison when your source has full timestamps, see the monthly SUMIFS guide.
Photo: Microsoft 365 / Unsplash.
