With the world divided on what the standard is to indicate a decimal number, Excel can sometimes seem confused. This can cause problems for the user especially when they get a new computer or work on someone else’s computer. So how do you get decimal commas instead of points in Excel or visa versa?
Table of contents
YouTube Decimal instead of full stops in Excel
Not Excel but Windows
Firstly you must realize this is not an Excel thing, it is a Windows thing. When your computer was originally set up, the regional settings were set to match a certain country and those standards are used throughout your machine.
So depending on the version of Windows you use, in the control panels you have regional settings and as shown below mine assumes English (South Africa) and if you click on Advanced settings you will see that my Decimal symbol is a period (.).
By default these settings are what Excel will use. If you change the regional settings, it will automatically change all the decimal symbols to match the new standard. So you could change the decimal separator from comma to point or point to comma at this level. Just note that it will affect all your programs (Excel, Access etc).
You can customise the decimal symbol directly in Excel though
If you are wary of changing your regional settings, then you can change it directly in Excel.
If you go into Excel Options (FILE, Excel Options) and choose the Advanced button you will see that there is a setting that specifies whether Excel should use the system separators (what we set up in regional settings) or your own version (see below). If you untick it, you can specify what you want the decimal separator and thousands separator to be.
Some words of caution with regards playing with the decimal and thousands separators:
- In South Africa we use a period and it seems the States uses commas. However, most of the software used here is American, so by default it uses commas. Your IT guys may have set up the regional settings for a reason.
- CSV files cause the most problems as they just show what they received. If the CSV was created from software that uses commas as the decimal indicator, then a number might read 1 123 256,26. However, your machine may use commas as the thousands separator and expect a full stop or point for the decimal. This can cause chaos when importing files.
- Changing the regional setting may make certain imports and templates stop working. We still think it is better to use numbers in the format you best understand but keep in mind when you make the change you may need to rework some other files (not just in Excel but other number based systems).
Related
Correcting numbers with incorrect comma or points as decimals