Spreadsheets

Export from an expense app to Excel and build your own pivot reports

Export expenses from an app to Excel, turn the CSV into a table, and build two pivot reports: category by month and merchant totals, with checks.

Editorial illustration for Export from an expense app to Excel and build your own pivot reports

To analyse expenses in Excel, export your transactions as a CSV from the app, open it, turn the data into a Table, and build a PivotTable with Category in rows, Month in columns and Amount as the value. A second pivot with Merchant in rows gives your biggest payees. The app captures the data and Excel gives you the freedom to slice it however you like.

What should I export, and how do I open it?

In SpendixAI you can download a CSV from a report, filtering and grouping with the Report builder first, or use the Export centre's standard all-transactions file. Look at the column headings in your file before anything else, because the order and names are what drive the pivot. Then open it in Excel with Data, Get Data, From Text/CSV, which shows a preview and lets you set the date column as a date. Double-clicking a CSV also works, but dates can be misread when the day and month are swapped.

How do I get the data ready?

  1. Click inside the data and press Ctrl+T to make a Table. Tick My table has headers. Tables grow when new rows are pasted under them and pivots can use the table name as their source.
  2. Check the Date column is right-aligned. Left-aligned dates are text and will not group by month.
  3. Check the Amount column is numeric. A left-aligned amount or one with a trailing character needs converting.
  4. If the export includes refunds or credits, find out how they appear (negative numbers, or a separate type column) before you total anything.
  5. If you spend in more than one currency, use the amount in your base currency, which is the one totals use, and not the original amount.

How do I build the category by month pivot?

  1. With a cell in the Table selected, choose Insert, then PivotTable, then OK.
  2. Drag Category into Rows and Amount into Values (set to Sum).
  3. Drag Date into Columns. Excel 2016 and later group dates automatically; if it does not, right-click a date, choose Group and select Months, and Years if the data spans more than one.
  4. Sort by Grand Total, largest first, with the sort option in the Row Labels drop-down.

Here is a tiny made-up export of six rows: April Food ₹1,200, April Food ₹800, April Fuel ₹2,000, May Food ₹1,500, May Fuel ₹1,800 and May Fuel ₹400. The pivot reads:

CategoryApril (₹)May (₹)Total (₹)
Fuel2,0002,2004,200
Food2,0001,5003,500
Total4,0003,7007,700

Check by adding the six rows: 1,200 plus 800 plus 2,000 plus 1,500 plus 1,800 plus 400 is 7,700, which matches the grand total.

How do I list merchant totals?

Create a second pivot on a new sheet. Put Merchant in Rows, Amount (Sum) in Values, and drag Amount in a second time and change it to Count, which shows how often you paid them. Then open the Row Labels filter, pick Value Filters, then Top 10, and set it to show the top 10 by sum. A merchant with a high count and a small sum is a habit; one with a low count and a large sum is a one-off. Spot-check any merchant name that appears twice with slightly different spellings, because those are rows that your pivot will treat separately.

What do I do when the numbers change?

A quick reconciliation habit

  • Filter the app to the same period and compare its total with the pivot's grand total.
  • Count rows in Excel with =ROWS(Table1) and compare with the row count you expected.
  • Look for duplicates with Data, then Remove Duplicates on a copy, never on the only copy.

The split that tends to work is to let SpendixAI handle capture (statement uploads reviewed before saving, plus receipts attached to their transactions) and to use Excel for the one-off questions, such as how much went to a single merchant over a financial year.

SPENDIXAI JOURNAL

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

Explore your reports