Spreadsheets

Summarise spend by currency with a pivot table

Build an Excel pivot table that shows foreign spend by currency in its own units next to INR totals, and avoid the grand total that adds rupees to dollars.

Editorial illustration for Summarise spend by currency with a pivot table

Select your expense table, choose Insert, PivotTable, put Currency in Rows, and drop both Amount and Amount in INR into Values as sums. You get one line per currency showing what you spent in that currency, next to the rupee value. Turn off the grand total for the original-currency column, because adding dollars to euros gives a number that means nothing.

What should the source table look like?

ColumnExampleNote
Date04-Feb-2025A real date, not text
DescriptionAirline ticketOptional
CategoryTravelUseful for a second pivot
Amount250.00In the original currency
CurrencyUSDThree-letter code
Amount in INR20,750Amount x rate

Press Ctrl+T to turn the range into a table first. A pivot built on a table picks up new rows once you click Refresh, so you do not have to reselect the data every month.

How do I build the pivot?

  1. Click any cell in the table and choose Insert, then PivotTable.
  2. Place it on a new sheet.
  3. Drag Currency into Rows.
  4. Drag Amount into Values and Amount in INR into Values. Both should say Sum of.
  5. Optionally drag Date into Columns and group by months to see a trend per currency.

Here is a hypothetical result. The totals in the middle column are each in their own currency.

CurrencySum of Amount (original)Sum of Amount in INR
EUR340.0030,600
GBP120.0012,600
INR18,500.0018,500
USD610.0050,630

Why should I remove the grand total of the original amount?

The bottom line would add 340 + 120 + 18,500 + 610, which is 19,570 of mixed money. The rupee column is the only one that can be added up honestly: 30,600 + 12,600 + 18,500 + 50,630 = ₹1,12,330. On the Design tab choose Grand Totals, Off for Rows and Columns, and then write the true total yourself with =SUM of the INR column outside the pivot. Alternatively, keep the grand total but ignore the original-currency figure in it.

Can the pivot also show each currency's share?

Yes. Right-click a value in the INR column, choose Show Values As, then % of Grand Total. With the hypothetical table above, USD is 50,630 out of 1,12,330, about 45.1%, EUR is about 27.2%, INR about 16.5% and GBP about 11.2%. To see months across the top, drag Date into Columns and group by Months. That layout makes it easy to spot the month you booked a flight or paid an annual overseas subscription.

Can I get the same result with SUMIFS instead?

  • List currencies in a column, say H2:H6.
  • Original units: =SUMIFS($D$2:$D$500,$E$2:$E$500,H2)
  • Rupee value: =SUMIFS($F$2:$F$500,$E$2:$E$500,H2)
  • Rupee total: =SUM(J2:J6)

Formulas update by themselves, while a pivot needs Refresh. A pivot is faster to rearrange when you want to slice by month or category as well.

How do I see travel or international subscriptions only?

Drag Category into Filters and pick Travel, or add a Slicer under PivotTable Analyze, Insert Slicer. For subscriptions in dollars, filter Category to Subscriptions and look at the USD row. The rupee column shows what they cost you, and the original column shows whether the price in dollars changed, which is a different story from the exchange rate moving.

A quick audit

  1. Compare the pivot's rupee total with the SUM of the INR column.
  2. Check that no currency appears twice because of a typo such as usd and USD.
  3. Look for a blank row in Currency, which means a transaction without a code.

If foreign spend arrives mostly through card statements, SpendixAI can total it in a base currency and has a Foreign-currency spend report, so you do not have to maintain the pivot yourself. A pivot remains a good way to learn what your own numbers look like.

SPENDIXAI JOURNAL

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

Explore your reports