How to convert currency in Excel

One rate in one cell, locked with $

The sheet is a UK company's supplier invoices in several currencies. Start with the simplest case: converting the USD invoice to pounds with today's rate. Never type the rate into the formula itself. Put it in a cell (J2 here) so you can update it in one place and everyone can see which rate was used. The rates in this example are for illustration; type the rate you actually use.

The dollar signs in $J$2 make it an absolute reference, so the formula still points at J2 when you fill it down the column. Without them, the next row looks at J3, which is empty, and returns #DIV/0!.

Multiply or divide? Read the rate's direction

A rate is always "units of one currency per 1 unit of another". The table here says 1.331 USD per 1 GBP. To go from dollars to pounds you divide by it, and to go from pounds to dollars you multiply. If your source quotes the other way round (0.7513 GBP per 1 USD), you multiply to get pounds. Using the wrong one gives an answer that is off by the rate squared, which is easy to miss when the rate is close to 1.

Label the rate cell with both currencies, as in "USD per 1 GBP", and the right operation becomes obvious. To flip a rate, divide 1 by it.

Many currencies: a rate table with XLOOKUP

When invoices come in different currencies, keep a small rate table (G1:H7) with one row per currency code, all quoted against your home currency. XLOOKUP finds the rate for each row's code and the formula divides by it. Lock the table with $ so it stays put when you fill down. XLOOKUP needs Excel 365 or 2021; in older versions use VLOOKUP(C2,$G$2:$H$7,2,FALSE).

Codes are the weak spot. XLOOKUP ignores upper and lower case, so "eur" matches "EUR", but a trailing space does not match, so INV-2213's "eur " returns #N/A. Use TRIM on the code, and add a fourth argument such as "Rate missing" so a currency you forgot to add is obvious instead of a cryptic error.

Round each line to 2 decimals (and watch the total)

Divide 3,180.40 EUR by 1.152 and you get 2760.7638888..., which nobody can pay. ROUND(...,2) gives the cents or pence figure that will appear on the payment. Formatting with 2 decimal places only hides the extra digits; they are still in the value and still affect totals.

Decide once whether the total is the sum of the rounded lines or the rounded sum of the raw conversions, because they can differ by a penny or more. Accounting normally adds the rounded lines, so the total matches the individual payments. This SUMPRODUCT converts and rounds all six invoices at once; SUMIF looks up each rate. If a currency is missing from the table, SUMIF returns 0 and the formula shows #DIV/0!.

Currency formats don't convert

Changing a cell from $ to € or £ with Home > Number Format, or with Format Cells > Currency, only changes the symbol shown. 12,749.99 stays 12,749.99. This is one of the most common currency mistakes in shared workbooks: a column of dollars relabelled as euros looks converted and is not.

Use formats to show which currency a column is in, after you have converted it with a formula. If you need the symbol inside a text label, TEXT does it: =TEXT(ROUND(D3/$H$3,2),"£#,##0.00") gives £2,760.76.

Live rates: the Currencies data type (Microsoft 365)

Microsoft 365 can fetch exchange rates for you. Type a currency pair with ISO codes in a cell, such as USD/GBP, then convert it to a Currencies data type. The cell gets a small icon and you can add fields such as Price (the rate) next to it, or refer to it in a formula with dot notation, like =L2.Price.

The rates update when you refresh, not continuously, and Microsoft supplies them for information, so they can be delayed. For invoices, payroll or anything audited, record the rate you actually used (from your bank or payment provider) in the rate cell, with its date, rather than relying on a value that changes on the next refresh.

1. Type the pairs you need in a column, for example USD/GBP, EUR/GBP, SEK/GBP.
2. Select them and click Data > Currencies in the Data Types group.
3. Click the Insert Data button that appears next to the selection and choose Price.
4. Point your rate table at those Price cells, and use Data > Refresh All to update them.