Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Decimal commas instead of points in Excel

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?

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 (.).

Decimal commas instead of points in Excel
Decimal commas instead of points in Excel

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.

Decimal commas instead of points in Excel

Some words of caution with regards playing with the decimal and thousands separators:

  1. 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.
  2. 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.
  3. 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).

Correcting numbers with incorrect comma or points as decimals

CSV now semicolon not comma