Conditional Formatting for Currency Symbol

If you sell products in several countries, you might want to show the prices in different currencies. With the conditional formatting options in Excel 2010, you can change the number format, to show a specific currency for the country that’s selected.

Note: 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

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, you can watch this Excel tutorial video.

_____________

You may also like...

1 Response

  1. Manuel Mogollon says:

    I was doing exactly the same procedure as you did in the example, but in my file the currency symbol is stuck in one currency symbol. When I found your example, I downloaded the file to my computer and when I tried, it did the same. The currency symbol is stuck either to Euros or GBP. I wonder if it is something related to the way I have Excel configured or if it is also happening to you. Could you change currencies several times and see if the symbol is stuck on a particular currency symbol?

Leave a Reply to Manuel Mogollon Cancel reply

Your email address will not be published. Required fields are marked *