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.

There are written steps and a video below

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.

Set up the Country List
Set up the Country List

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.

_____________

3 thoughts on “Conditional Formatting for Currency Symbol”

  1. 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?

  2. I wonder if you could help me. I sell on Amazon in the UK, Germany, France, Spain, Italy, USA, Canada, Australia. when I down load monthly sales it arrives in the form of notepad (into excel) the sheets are similar headings (just different languages) column are the same.
    the problem is this… the sales are just number? ( like 27.99) this could be Pounds sterling, Euros, USD, CD, AD,?
    what I like to know if there a formula that I can use that would format the currency to the right value. I have a common field I can use that in through out the sheets (UK amazon.co.uk. (pounds) amazon.de (euros) amazon.com (USD)

    I am thinking build a table with the www addresses import to power BI as a look up would that work?

    or is there a more simple way

  3. I was stuck on conditional formatting back and forth from dollar sign and straight numbers with no formatting, and your post helped a lot. Keep up the good work.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.