AI and MCP

Asking AI for a pivot table of your expenses

How to describe your expense columns to an AI assistant, get step-by-step pivot table instructions for Excel or Google Sheets, and verify the grand total.

Editorial illustration for Asking AI for a pivot table of your expenses

Describe your columns to the assistant, ask for numbered steps for your exact app, build the pivot yourself, and then check that its grand total equals a plain SUM of your amount column. The assistant is good at explaining where to click. It is not a reliable calculator, so the check at the end is not optional.

Step 1: describe your sheet precisely

Vague descriptions get vague steps. Tell the assistant the app (Excel or Google Sheets), the columns in order, the header row, and how many rows you have. A prompt that works:

Step 2: build the pivot

In Excel, select any cell in your data, choose Insert, then PivotTable. In Google Sheets, select the data and choose Insert, then Pivot table. Put Category in Rows, Date in Columns and Amount in Values, set to Sum. If your assistant gives menu names that do not match your screen, tell it exactly what you see; versions differ.

Dates arrive as individual days, which makes a very wide table. In Excel, right-click a date in the pivot and choose Group, then months and years. In Google Sheets, right-click a date and choose Create pivot date group, then Year-Month.

What should the result look like?

Here is a small hypothetical result for three categories over three months.

CategoryOctNovDecRow total
Rent₹18,000₹18,000₹18,000₹54,000
Food₹7,200₹8,100₹7,650₹22,950
Transport₹2,100₹1,900₹2,400₹6,400
Column total₹27,300₹28,000₹28,050₹83,350

How do you verify the grand total?

Add the amount column separately and compare. For data in C2 to C400, type this in an empty cell:

  • =SUM($C$2:$C$400)
  • If your data has several years, restrict to the months in the pivot: =SUMIFS($C$2:$C$400, $A$2:$A$400, ">="&DATE(2026,10,1), $A$2:$A$400, "<"&DATE(2027,1,1))

The result should match the pivot's grand total, here ₹83,350. Cross-check the rows too: 54,000 + 22,950 + 6,400 = 83,350 and 27,300 + 28,000 + 28,050 = 83,350. If the two totals differ, the next section is almost certainly the cause.

Why do pivot tables go wrong?

  • Amounts stored as text, often with a rupee sign or a trailing space. They appear left-aligned and are skipped in the sum.
  • Dates stored as text, so grouping by month is greyed out.
  • Blank rows or a total row inside the range, which counts spend twice.
  • Debits and credits in two columns, when the pivot expects one. Create a single Amount column first.
  • A new row added below the range after you built the pivot. Refresh it, or extend the range.

A quick way to find text-formatted amounts is to put two counts side by side: =COUNT($C$2:$C$400) and =COUNTA($C$2:$C$400). COUNT only counts real numbers, while COUNTA counts every non-empty cell. With 399 data rows below a header, both should say 399. If COUNT says 387, twelve amounts are stored as text, and you know exactly how many rows to repair.

Do the same for dates with =COUNT($A$2:$A$400). A lower number than the row count means some dates are text, which is why the month grouping option refuses to work.

Prompts for the second round

  1. "Change the pivot to show average monthly spend per category."
  2. "Show only the top five categories by total."
  3. "Add a filter so I can pick one account."
  4. "Explain what the Show values as percent of column total option does."

When a sheet stops being enough

Pivot tables are great until you have to categorise the rows by hand first. If your records come from bank or card statements, SpendixAI can take statements in PDF, CSV or Excel, let you review the rows before saving, and offer category reports and a CSV download, so the pivot is built from rows already sorted. Until then, the steps above are all you need.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports