Conditional Formatting for Currency Symbol

Conditional Formatting Currency Symbols

If you sell products in several countries, you might want to show the prices in different currencies. In Excel 2010 and later, you can use conditional formatting for currency symbol changes. See how to use those settings, you can change the number format based on a cell’s value, to show a specific currency for the country that’s selected.

Excel 2010 and Later

The technique show here will only work in Excel 2010 and later versions. Conditional Formatting for numbers was available in Excel 2007, but didn’t work correctly. (Thanks Oli!)

CondFormatNum05

Set up the Country List

On a separate worksheet, create a list of countries, with each country’s exchange rate and currency. Use a VLOOKUP formula to return the exchange rate and currency for the selected country.

Cell G1 is named ExchRate and G2 is named CurrSel.

CondFormatNum03

Adjust the Prices

In the price list, multiply the base price by the selected country’s exchange rate, to get the adjusted price for each product. The adjusted price will show up in the order sheet.

CondFormatNum04

Apply the Conditional Formatting

Next, you’ll format the cells on the order form, so the currency symbol changes, depending on the country selected.

  • On the Order sheet, select the currency cells — D4:E10 in this example.
  • On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
  • In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’
  • In the ‘Format values where this formula is true’ box, type a formula that checks the selected currency (CurrSel).

CondFormatNum02

  • Click Format, and in the Format Cells dialog box, select the Currency format, and the applicable symbol.

CondFormatNum01

  • Click OK to close the dialog boxes.

Test the Conditional Formatting

To test the conditional formatting, select a different country from the data validation drop down list.

The pricing and currency symbol for the selected country will be displayed.

CondFormatNum06

Download the Sample File

To see the formulas and conditional formatting, you can download the sample file from the Contextures website.

In the Conditional Formatting section, look for: CF0003 – Conditional Formatting for Currency Symbol

NOTE: Although you can open the file in some earlier versions of Excel, the conditional formatting will only work correctly in Excel 2010.

Watch the Video

To see the steps for applying conditional formatting for currency, watch this shortvideo.

_____________