Choose Currency Format from Dropdown

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,226
Office Version
  1. 365
Platform
  1. Windows
Someone asked me this question:

There is apparently a trick (noVBA) that allows the user to choose the currency from a drop down menu. This changes the entire Sheet/Workbook into the currency of choice. This would be very useful. any ideas?

I said:

1) In Excel 2007, create a cell at the top of the sheet that uses data validation and a list of currency names. Then use Conditional format and a true/false formula that looks at the cell with the data validation list and applies the correct number formatting. Or 2) Ctrl + A, Ctrl + 1, Number Tab, Currency of Choice.

Anyone have any other ideas of how to answer this question?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
(Excel 2003) If you have the Euro conversion addin...

Convert values or formulas to euros or another currency


On the Tools menu, click Euro Conversion. If the Euro Conversion command is not available on the Tools menu, you need to install the Euro Currency Tools add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.).
How?

On the Tools menu, click Add-Ins.
If the add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in.

In the Add-Ins available box, select the check box next to the add-in you want to load, and then click OK.
If necessary, follow the instructions in the setup program.
In the Euro Conversion dialog box, in the Source range box under Data to Euro convert, enter or select the cell or a single range that contains the values you want to convert. All values in the range must be in a single currency.
Note Excel may convert dates and times in the source range to values in the new currency in the destination range.

Under Data to Euro convert, click the Destination range box, and enter or select the upper left cell of the range where you want to paste the converted values. The destination range must not include any of the cells in the source range.
Under Currency conversion, in the From list, click the currency of the values you're converting, and in the To list, click the currency to which you want to convert the values.
If your source range contains formulas, click Advanced, and select one of the conversion options under Formula options in the Advanced Euro Options dialog box.

You can also use this dialog box to control rounding.

How?

Set either or both of the following options:

If you want the converted values that are stored by Excel to include all significant digits without rounding, select the Output full precision check box.
If you are converting between two euro member currencies and want to set the number of decimal places to use for rounding the intermediate values in euros, select the Set triangulation precision to check box, and then click the number of significant digits (3 or greater) you want in the digits box.

In the Output format list, click one of the following to indicate the number formatting you want to apply to the converted values:
Currency To apply a currency format for the currency selected in the To list and copy the cell formatting, including borders and font, from the source cells.

ISO To display the International Standards Organization code for the converted currency and copy the cell formatting from the source cells. For a list of the ISO codes, see the EUROCONVERT worksheet function.

None To keep the current number format and cell formatting of the destination cells.

Click OK. If the Euro Conversion dialog box appears, select an option to convert the current formula. Click OK to move to the next formula, or click Apply to all to use the same settings for all formulas.

Notes

Excel converts constant values in the source range to values in the new currency in the destination range.
Excel copies text in the source range without conversion.
 
Upvote 0

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,226
Office Version
  1. 365
Platform
  1. Windows
Makrini:

Totally amazing! This is a great add-in.

Thanks!!!!
 
Upvote 0

Forum statistics

Threads
1,191,197
Messages
5,985,231
Members
439,952
Latest member
djharter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top