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.
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.
In this workbook, there is an order form, with a drop down list of countries at the top.
NOTE: The cell with the drop down list is a named range - Country
The country information is stored on a sheet named Countries.
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:
Cell G2 is named CurrSel, and has this formula:
On the sheet named Prices, there is a price list with product names and the USA cost.
In column D, the adjusted cost is calculated, based on the selected country's exchange rate.
This formula is in cell D2:
The adjusted costs will be shown on the Orders sheet
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 cell D4:
In cell E4, that cost is multiplied by the quantity (cell C4), to get the total amount.
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 -- for the Pound currency:
2) Repeat those steps for the Euro currency, with this formula:
And select the Euro symbol
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.
In this workbook, we sell products in the USA and in other countries.
So we have a list of countries, and we can see that
On the price list, we show the US cost and then the adjusted cost, which is multiplied by the exchange rate.
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.
So to change that we're going to use conditional formatting in Excel 2010.
The formula will be checking the currency that we've selected.
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 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.
The other currency that we might need to show is a Euro.
So now if I select
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.
Last updated: March 31, 2021 10:13 AM