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!)
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.
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.
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).
- Click Format, and in the Format Cells dialog box, select the Currency format, and the applicable symbol.
- 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.
Download the Sample File
To see the formulas and conditional formatting, you can download the Conditional Formatting for Currency Symbol sample file. 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.
_____________
