Written by Allen Wyatt (last updated June 24, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Eric asked if there was a way to easily format cells so what would normally appear as $10,000.00 would appear as $10.000,00. This format being described is the difference between the US method of displaying figures (using commas as thousand separators and a period as a decimal sign) and the European method of displaying figures (using periods as thousand separators and a comma as a decimal sign).
There are three ways you can accomplish a switch. The easiest method is to simply change the Regional Settings in Windows. The exact way you do this depends on the version of Windows you are using, but in general there is a choice in the Windows Control Panel that allows you to specify regional settings. All you need to do is modify those settings to match the numeric display format desired. The change will affect not only the display of numbers in Excel, but in other Windows-compliant programs, as well.
The second method is to use a formula to handle the numeric display. This has the drawback of converting the numeric value to text, but it could be easily done. For instance, let's assume that you have the formatted numeric value $10,000.00 in cell A1. The following formula, in a different cell, would display the text $10.000,00:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1, "$#,##0.00"),".","^"),",","."),"^",",")
This formula first converts the number to an initial currency format in text. Then the SUBSTITUTE function is used to first change "." to "^" ("^" is used as a temporary placeholder), and then change "," to ".", and finally "^" to ",".
The final method has the advantage of leaving your numbers as numbers, instead relying on a custom format. All you need to do is to multiply your values by 100 and then use the following custom format:
#"."###"."###","##
The format allows any number up to 9.999.999,00 to be used. If you deal with numbers that have more than two decimal places, you will need to adjust your custom format accordingly, or adjust the value being displayed so that it has nothing to the right of the decimal point after it is multiplied by 100.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11266) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Formatting Currency.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
When you display a time in a cell, Excel normally displays just the hours, minutes, and seconds. If you want to display ...
Discover MoreWhen adjusting column width, Excel can add an extra line to some cells. This behavior seems to be related to the text ...
Discover MoreYou can use the Paste Special feature in Excel to multiply the values in a range of cells. If you don't want Excel to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments