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?
| Column | Example | Note |
|---|---|---|
| Date | 04-Feb-2025 | A real date, not text |
| Description | Airline ticket | Optional |
| Category | Travel | Useful for a second pivot |
| Amount | 250.00 | In the original currency |
| Currency | USD | Three-letter code |
| Amount in INR | 20,750 | Amount 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?
- Click any cell in the table and choose Insert, then PivotTable.
- Place it on a new sheet.
- Drag Currency into Rows.
- Drag Amount into Values and Amount in INR into Values. Both should say Sum of.
- 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.
| Currency | Sum of Amount (original) | Sum of Amount in INR |
|---|---|---|
| EUR | 340.00 | 30,600 |
| GBP | 120.00 | 12,600 |
| INR | 18,500.00 | 18,500 |
| USD | 610.00 | 50,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
- Compare the pivot's rupee total with the SUM of the INR column.
- Check that no currency appears twice because of a typo such as usd and USD.
- 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

