Special Offers

Troubleshooting Microsoft® Excel Spreadsheets
Author Laurie Ann Ulrich
Pages 368
Disk N/A
Level Beg/Int
Published 12/06/2000
ISBN 9780735611610
ISBN-10 0-7356-1161-0
Price(USD) $19.99
To see this book's discounted price, select a reseller below.

More Information

About the Book
Table of Contents
Sample Chapter
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books


Currency (continued)

I’m not sure how to format my numbers as foreign currency

Source of the problem

When you click the Currency Style button or apply the Currency formatting, the currency formatting that is applied by default is based on the regional settings for your installation of Windows. If you live in the United States, your currency format is likely dollars and cents. If you often work with a different currency, this default won’t be appropriate some or all of the time.

For example, currency in the United Kingdom uses periods where commas are used in U.S. currency (£1.000 is one thousand British pounds). With Excel, you can change the symbol in front of your currency amounts, but that’s about it. So you can change a dollar sign to a pound sign, but that won’t make Excel give up the commas for periods. To be able to apply foreign currency formats with the appropriate monetary symbols and use of commas and decimals for various national denominations, you need to change the Windows Regional Settings.

How to fix it

To change the currency symbol in Excel, follow these steps:

  1. Select the cells that you want to format with a different national currency symbol.
  2. Right-click the selection, and then click Format Cells on the shortcut menu.
  3. In the Format Cells dialog box, click the Number tab if necessary.
  4. In the Category list, click Currency if you don’t need the currency symbols and decimal points to align, or click Accounting if you do.
  5. Click the Symbol drop-down list.
  6. Scroll through the list, and select a country name. (The country’s currency symbol appears next to the country name.)
  7. Click to view graphic
    Click to view graphic

  8. In the Decimal Places box, type or scroll to the number of decimal places you want to display.
  9. If appropriate, click a format in the Negative Numbers list, and then click OK.

To change the Windows Regional Settings so that your currency is in a different format by default, follow these steps (Your command and dialog box names will differ slightly if you’re working in Windows 2000 or Windows Me):

  1. Click the Start button, point to Settings, click Control Panel, and then double-click Regional Settings.
  2. On the Regional Settings tab of the Regional Settings Properties dialog box, select a country from the drop-down list above the world map. (Keep in mind that the country you choose will affect the way the time and date appear on the Windows taskbar; how dates, times, and currency symbols appear in other Microsoft Office applications; and how the default dictionary checks spelling.)
  3. Click to view graphic
    Click to view graphic

  4. Click the Currency tab, and view the options in the Currency Symbol, Decimal Symbol, and Digit Grouping Symbol drop-down lists. If more than one is available for the selected country, the alternative(s) will appear when you click the drop-down arrows.
  5. Click to view graphic
    Click to view graphic

  6. When your default settings are the way you want them to be, click OK. You’ll be alerted that the changes won’t take place until you restart your computer.

You can change your regional settings temporarily so that your worksheet formatting is appropriate for a particular currency. You might change them before creating a worksheet, save or print the worksheet so that you can distribute it to interested parties, and then restore the default regional settings for your version of Windows.

Be careful when changing your regional settings. Your outgoing e-mail will appear in the time format of the country you select, and this might be confusing for some of your e-mail recipients. If it’s possible to get by with just changing the currency symbol and not adjusting your regional settings, you should consider taking this "path of least resistance."


Top of Page

Last Updated: Friday, July 6, 2001