How to Track Sales in USD, GBP and EUR in One Spreadsheet

This story was written with the assistance of an AI writing program. I edited it, and I checked every formula in it on sample rows before publishing.

I have 18+ years of work experience, and I build spreadsheets for small businesses with Excel, VBA, Power Query, Power BI, Google Sheets and Apps Script. A very common mess is a seller who gets paid in several currencies and has a sheet that simply adds the numbers up. $45 plus £15 plus €25 isn't 85 of anything.

Here's a small structure that fixes it. It takes about 20 minutes to set up.

1. A rates table you control

Create a tab called Rates. Put the currency codes in A2:A4 (USD, GBP, EUR) and, in B2:B4, how many US dollars one unit of each is worth: 1 for USD, roughly 1.32 for GBP, roughly 1.12 for EUR. Add a "last updated" date and write down where the rates came from, for example the European Central Bank's euro reference rates.

Update it on a fixed day each week. Live rates are possible (GOOGLEFINANCE in Google Sheets, STOCKHISTORY in Excel 365), but they make last month's totals change every time you open the file. For tracking, a fixed weekly rate is usually clearer. For accounting, use whatever rates your accountant tells you to.

2. A base-currency dropdown

On a Dashboard tab, pick a cell for your base currency (I use B3). Then Data → Data validation → List, and enter USD,GBP,EUR. That one cell decides which currency every total is shown in.

3. One conversion formula per row

In your sales log, put the amount in column E and the currency in column F (give F the same dropdown). The converted value goes in column G:

=IF(E4="","",E4*XLOOKUP(F4,Rates!$A$2:$A$4,Rates!$B$2:$B$4)/XLOOKUP(Dashboard!$B$3,Rates!$A$2:$A$4,Rates!$B$2:$B$4))

Read it as: convert the amount into US dollars, then from US dollars into the base currency. The IF(E4="","",…) part keeps empty rows empty.

XLOOKUP needs Excel 2021, Microsoft 365 or Google Sheets. On older Excel, use INDEX/MATCH:

=IF(E4="","",E4*INDEX(Rates!$B$2:$B$4,MATCH(F4,Rates!$A$2:$A$4,0))/INDEX(Rates!$B$2:$B$4,MATCH(Dashboard!$B$3,Rates!$A$2:$A$4,0)))

4. Totals that respect the month

The all-time total is just =SUM(G4:G500). This month only, with dates in column A:

=SUMIFS(G4:G500,A4:A500,">="&EOMONTH(TODAY(),-1)+1,A4:A500,"<="&EOMONTH(TODAY(),0))

Switch the dropdown from USD to GBP and every figure recalculates.

The three mistakes that break it

1. Free-typed currency codes. "Eur " with a trailing space isn't "EUR". Without any error handling, that row shows #N/A and so does your total, which is annoying but at least visible. The real trap is wrapping the lookup in IFERROR to make the errors go away. Then the bad row quietly uses a fallback rate and your total is wrong with no warning. My own workbook wraps its lookups in IFERROR, which is exactly why every currency cell in it is a dropdown: the typo can't get in. The fix is the dropdown, not IFERROR.

2. Counting the header row. A COUNTA over a whole column says you made one more sale than you did. Start every range at the first data row.

3. Overwriting the original amount. Always keep the amount and currency exactly as you were paid, and convert in a separate column. When a rate changes, or your accountant asks, you can always recalculate.

Test it before you trust it

Add three fake rows: a blank row, a row with a mistyped currency, and a refund entered as a negative amount. The blank row should stay blank, the typo should be impossible because of the dropdown (or show up as an error), and the refund should reduce the total. If all three behave, you can trust the sheet with real numbers. Then delete the test rows.

---

Start free: if your bigger headache is "who still owes me money?", there's a free Order & Payment Tracker (.xlsx workbook, no macros) with orders, customers, aging buckets and the AI prompts used to build it: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker

About me, and a disclosure: I'm Ashish, an Excel/Power BI developer with 18+ years of work experience, and I build these systems with AI assistance under my direction. I also sell a ready-made multi-currency version of this setup as an Excel workbook, with expense tracking, goals and a product catalog: the Passive Income Command Center ($27). Despite the name, it's a tracking tool. It doesn't generate income or promise any, and it isn't financial or tax advice. Everything in this article works without it. Link: https://ashishsrivastavajay.gumroad.com/l/hytkwb