📊 Guide: How to Create Revenue by Country (EUR) from Xero (Multi-Currency)

🎯 Objective

Produce a 2025 Revenue by Country report in EUR when:

• Country is not a Tracking Category

• Invoices exist in multiple currencies

STEP 1 — Export Invoice Data from Xero

1. Go to Business → Invoices (under Sales)

2. Click the relevant tab:

   • Paid

   • (Optionally also include Awaiting Payment if required)

3. Use the search/filter panel:

   • Set Invoice Date

   • From: 01 Jan 2025

   • To: 31 Dec 2025

4. Click Export → CSV

You should now have a spreadsheet containing:

• Invoice Date

• Invoice Number

• Contact

• Country

• Currency

• Line Amount

• (Possibly Exchange Rate)

STEP 2 — Check the Structure of the Export

Before building the report, confirm:

• Each invoice may have multiple lines

• You have a Line Amount column (not just Invoice Total)

• You have a Currency column

• You may or may not have an Exchange Rate column

⚠ Important:
If multiple lines exist per invoice, always use Line Amount, not Total.

STEP 3 — Create a EUR Conversion Column

Because invoices are in:

• AUD

• BRL

• CAD

• EUR

• GBP

• NGN

• USD

You must convert everything into EUR to match the P&L.

Option A (Best Practice) — If You Have Exchange Rate per Invoice

Assume:

• AH = Exchange Rate

• AI = Line Amount

• AJ = Currency

• AG = New EUR column

In AG2, enter:

=IF(AJ2="EUR", AI2, AI2*AH2)

Copy down the column.

This gives you the EUR base value per invoice line.

Option B — If You Do NOT Have Exchange Rate Column

These were the rates used in 2025 - you will need to update this for 2026

• USD → 0.875

• GBP → 1.175

• AUD → 0.60

• CAD → 0.62

• BRL → 0.16

• NGN → 0.00062

Formula example:

=IF(AJ2="EUR", AI2,

 IF(AJ2="USD", AI2*0.875,

 IF(AJ2="GBP", AI2*1.175,

 IF(AJ2="AUD", AI2*0.60,

 IF(AJ2="CAD", AI2*0.62,

 IF(AJ2="BRL", AI2*0.16,

 IF(AJ2="NGN", AI2*0.00062, "")))))))

⚠ Note: This will not exactly match Xero P&L because rates vary by invoice date.

STEP 4 — Create Pivot Table in Google Sheets

1. Select your full data range

   • On Mac: Command + A

2. Click Insert → Pivot Table

3. Choose New Sheet

4. Click Create

STEP 5 — Build Revenue by Country

In Pivot Editor:

• Rows → Country

• Values → Sum of EUR Column (AG)

Ensure:

• Values are set to SUM

• Numbers formatted as Currency

STEP 6 — Validate Against P&L

Compare:

• Pivot Grand Total

• 2025 Profit & Loss Revenue

If different, check:

✔ VAT inclusion
✔ Credit notes included/excluded
✔ Invoice Status (exclude Draft / Void)
✔ Currency conversion method

Common Issues Identified

Final Output

You now have:

✅ Revenue by Country for 2025
✅ Converted into EUR
✅ Summarised via Pivot Table

🧠 Recommended Improvement for Next Year

To avoid this manual process:

• Set up Country as a Tracking Category in Xero

• Or ensure Base Currency Amount is included in exports

• Or run a Receivable Invoice Detail report including Base Amount

That would eliminate manual FX calculations entirely.