Add three columns to your expense sheet: Amount (in the original currency), Currency (a code such as USD) and Rate (INR per one unit), then a fourth column that multiplies them. A small rate table on its own sheet and an XLOOKUP formula fill the Rate column for you, so the original amount and the rupee amount stay side by side.
How should the columns be laid out?
| Column | Heading | Example row |
|---|---|---|
| A | Date | 12-Mar-2025 |
| B | Description | Hotel booking |
| C | Amount | 150.00 |
| D | Currency | USD |
| E | Rate to INR | =XLOOKUP(...) |
| F | Amount in INR | =C2*E2 |
Keep one row per transaction and never overwrite the original amount with the converted one. If you only store the rupee figure, you cannot check it against a statement later.
What does the rate table look like?
Create a sheet named Rates with codes in column A and the INR value of one unit in column B. These numbers are hypothetical, so replace them with rates you have chosen to use.
| Code | INR per 1 unit |
|---|---|
| INR | 1.00 |
| USD | 83.00 |
| EUR | 90.00 |
| GBP | 105.00 |
| SGD | 62.00 |
Include INR itself with a rate of 1, so rupee rows go through the same formula and you do not need an IF around it.
Which formula fills the Rate column?
- Excel 365 and 2021: in E2 enter =XLOOKUP(D2, Rates!$A$2:$A$20, Rates!$B$2:$B$20, "Check code")
- Older Excel: =IFERROR(INDEX(Rates!$B$2:$B$20, MATCH(D2, Rates!$A$2:$A$20, 0)), "Check code")
- In F2 enter =C2*E2 and fill both formulas down.
- The fourth XLOOKUP argument is the text shown when the code is missing, so typos stand out instead of becoming a silent zero.
With a hypothetical USD 150 hotel booking and the rate above, F2 shows 150 x 83.00 = ₹12,450. A EUR 40 meal at 90.00 shows ₹3,600. A rupee row of ₹500 shows ₹500.
How do I catch mistakes in the currency column?
- Add a data validation list to column D that points to Rates!$A$2:$A$20, so only listed codes can be typed.
- Count the problem rows with =COUNTIF($E$2:$E$500,"Check code") and keep that cell at the top of the sheet; it should read 0.
- Format the Amount column with two decimals but the Rate column with four, since some rates need more than two.
- Make the range in the formulas wider than your table, so new currencies fit without editing every formula.
How do I total the converted amounts?
Total the INR column for the month with =SUM($F$2:$F$500), or by category with SUMIFS on column F. To see how much you spent in each currency in its own units, use SUMIFS on column C with the currency in column D as the criterion, such as =SUMIFS($C$2:$C$500,$D$2:$D$500,"USD"). Never add column C across different currencies, because 150 dollars plus 40 euros is not 190 of anything. In the earlier examples the three rows come to 12,450 + 3,600 + 500 = ₹16,550.
Is one rate per currency enough?
A single rate per currency suits a rough budget or a short trip. If you track spending across months, rates move, and a date-based table is a better fit: see the approach that picks the latest rate on or before each transaction date. If you compare with a card statement, the rupee figure the statement billed is the most reliable number, so type it in as the converted amount for those rows and keep the formula for the rest.
Where a spreadsheet stops being the easiest tool
When most of your foreign spend arrives on card statements, retyping amounts and currency codes gets tedious. SpendixAI lets each expense carry its own currency, totals everything in a base currency using the rate saved with each transaction, and has a Foreign-currency spend report. You choose the base currency before importing, and it cannot be changed once transactions exist.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

