Home > Format > ConditionalFormatting > Currency

Conditional Format Currency Video

This video, and the written steps, show how to use Excel conditional formatting to automatically change the currency symbol in worksheet numbers. Download the free Excel workbook to follow along with the video.

Video: Conditional Format Currency

Watch this short video to see how to use Excel conditional formatting to automatically change the currency symbol in worksheet numbers, when you select a different country from a drop down list. The written steps are below the video.

The video transcript is available below, and you can download the Excel workbook below, to follow along with the video.

Video Timeline

• 00:00 Introduction
• 00:54 Lookup Formula
• 01:42 Add Conditional Formatting
• 02:09 Conditional Formatting Formula
• 02:43 Choose a Format
• 03:16 Add Another Currency
• 04:11 Test the Conditional Formatting

Conditional Format Currency Workbook

In this workbook, there is an order form, with a drop down list of countries at the top.

• If you select USA, the cost and the total cost are shown in dollars.
• If you select other countries, an exchange rate is applied to the price, and the currency might change.

NOTE: The cell with the drop down list is a named range - Country

Country Information

The country information is stored on a sheet named Countries.

• In a 3-column list, each country's name, exchange rate and currency are listed
• The cells with the details are in a named range - CountryLU

Exchange Rate and Currency

In column G on the Countries sheet, VLOOKUP formulas find the exchange rate and currency for the selected country.

Cell G1 is named ExchRate, and has this formula:

• =VLOOKUP(Country,CountryLU,2,0)

Cell G2 is named CurrSel, and has this formula:

• =VLOOKUP(Country,CountryLU,3,0)

Adjust the Prices

On the sheet named Prices, there is a price list with product names and the USA cost.

• The cells with the product details are in a named range - PriceLU

In column D, the adjusted cost is calculated, based on the selected country's exchange rate.

This formula is in cell D2:

• =C2*ExchRate

The adjusted costs will be shown on the Orders sheet

Order Form Formulas

In the order form, a VLOOKUP formula, in column D, finds the adjusted cost for the product name in column B.

This formula is in the active cell, D4:

• =VLOOKUP(B4,PriceLU,3,FALSE)

In cell E4, that cost is multiplied by the quantity (cell C4), to get the total amount.

• =C4*D4

Add Conditional Formatting

On the order form, conditional formatting will change the currency symbol, based on which country is selected.

1) Follow these steps to add the first conditional formatting rule -- number format for the Pound currency:

• 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 this formula, to check the selected currency (CurrSel).
• =CurrSel="Pound"
• Click Format, and in the Format Cells dialog box, select the Currency format, and the Pound symbol.
• Then click OK, twice, to close the dialog boxes, and apply the conditional formatting rule to the selected range

2) Repeat those steps for the Euro currency, with this formula:

• =CurrSel="Euro"

And select the Euro symbol

Test 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.

Video Transcript

In this workbook, we sell products in the USA and in other countries.

• When we sell in the USA, we show the cost and the total cost in dollars.
• And if we sell in other countries, we apply an exchange rate to the price.

So we have a list of countries, and we can see that

• the USA would just be a flat rate. There's no increase or decrease in the cost.
• If we sell in the UK, the exchange rate is 0.678108, and that would be in pounds.
• And we've got other countries in that list as well.

On the price list, we show the US cost and then the adjusted cost, which is multiplied by the exchange rate.

Lookup Formula

And the exchange rate comes from a lookup formula that checks the country we selected, and goes to the country lookup, and finds the value in the second column. So in our lookup table, the second column has the exchange rate.

We also can find that country's currency. So for the UK, it would be pound - that's in the third column.

And on our order sheet, this cell pulls the currency for the selected country.

So our costs are adjusting correctly. If we look at the UK, instead of 12.95, it's 8.78, but it shouldn't be dollars, that should be in pounds.

Add Conditional Formatting

So to change that we're going to use conditional formatting in Excel 2010.

• I'm going to select all these cells where currency would be displayed
• on the Ribbon, I'm going to the Home tab and click Conditional Formatting
• and go down and click New Rule.
• For the rule type, I'll click on the very last one, which is Use a formula.

Conditional Formatting Formula

The formula will be checking the currency that we've selected.

• So I'll type equal, and then click on this cell that has the currency, and then type equal.
• And we're doing the UK, so in quote marks, I'm going to type pound
• So is cell E1 equal to pound?

Choose a Format

Then we'll click the Format button, and in Format Cells, I'm going to select currency.

And for the symbol, I want a pound sign.

• So I'll go down to English, UK pound
• Click on that, and then click OK.

So we can see what it will look like if that cell says pound. When I click OK, it's already showing that result that I want.

Add Another Currency

The other currency that we might need to show is a Euro.

• So with those cells still selected, I'm going back to Conditional Formatting, New Rule, Use a formula.
• And this time we'll say equals, and click on that currency cell, equals Euro.
• So if that cell is equal to Euro, what format do we want?
• So I'll click Format, Currency, and for the symbol, I'm going to find the Euro.
• I could use any of these, but I'll use Italy and click OK.
• So here's how it will look. I'll click OK.

Test the Conditional Formatting

So now if I select

• Italy, it shows the Euro
• USA, dollar signs

Get the Workbook

Download the Conditional Format Currency sample workbook, to follow along with the Conditional Format Currency video. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Conditional Formatting Basics

Conditional Formatting Examples