Sometimes you don’t want the comma “,” as a thousands separator, but rather a full stop “.”. Or you don’t want the full stop as the decimal separator. Changing these two separators in Office is easy: You can either do it system-wide or override the system settings.
Change the separators under Windows
The two separators for decimals and thousand are connected in many language regions: If you change the thousands separator, e.g. from comma to full stop, you also need to change the decimal separator. Because these two things are connected, you usually change both in the same place.
In Excel, you have two choices: Either modify the thousands and decimal delimiter system-wide or just do it within Excel. Recommendation: Do it system-wide, if you want to change them permanently or also need to do it for PowerPoint. If you just temporarily, simply override them in the Excel options.
Please note: Don’t search within the language and proofing menus. You won’t find it there.
Change the separators in Excel, permanently and system-wide
By default, Excel uses the separators defined by your Region settings in Windows. If you permanently and for the whole computer want to change them, follow these steps:
- Press the Windows key. The Start menu should open.
- Type “Region”.
- If the small symbol with a clock and a globe comes up, click on it (see the animation on the right hand side).
- You can either change the whole region or just the separators. For only changing the separators, click on “Additional settings…”.
- Type your “Decimal symbol” and “Digit grouping symbol” in the corresponding text fields.
- Confirm with OK.
If the steps 1 and 2 above don’t work, you can also navigate through the control panel. Therefore, right click on the start button and click on “Control Panel”. If your control panel is organized by category, switch to “Small icon” on the top right corner. Click on “Region” and continue with step 3 above.
Please note 1: Usually the new settings adapt immediately. But sometimes, you have to press F9 in order to recalculate. If it’s still not working, try restarting Excel.
Please note 2: If the GIF is too fast, please refer to the steps shown on the screenshot on the right hand side.
Change the separators in Excel, temporarily and inside Excel
Besides the system settings, you can modify the thousands and decimal separators within Excel. Recommendation: Override them only if you temporarily need to change them.
- Go to “File” and click on “Options” on the left side.
- Within the Excel options, click on “Advanced” on the left side.
- Remove the tick mark from “Use system separators”. Now you can define your own for decimals and thousands. Confirm with OK.
Change the separators in PowerPoint
PowerPoint uses the separators defined in your language region like Excel. Changing them requires the same steps as described above in the chapter Change the separators in Excel, permanently and system-wide.
Please note: Usually you need to restart PowerPoint in order to let the changes take effect.
Change the them in Think-Cell
Think-Cell is a great add-in for PowerPoint. You can easily create charts with much more comfort than the built-in chart options. Unfortunately, some settings are a little bit hidden in Think-Cell.
If you want to change the thousands and decimal separators in Think-Cell, follow these steps:
- Select the labels you want to change. If you want to change it for all labels, click on the label in the left bottom corner first (here: 12,900), hold the Shift key and click on the label in the top right corner (here: 46,400).
- The small window opens for modifying the labels (e.g. font, separators). Click into the second item (a text box). Now you can change the separators, either from the list or change it to “Use Excel Format”.
- If you choose “Use Excel Format”, you can further define the label number format. Therefore, open the datasheet, either by double clicking onto your chart or by clicking on the small sheet icon in the right bottom corner.
- Once the datasheet is open, select the cells you want to format and press Ctrl + 1 on the keyboard.
- Now you can finetune the number format of your think-cell chart.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Change them in Mac OS
If you use a Mac, the approach is similar to Windows. Change the separator within the system settings. The only difference: Excel for Mac doesn’t allow you to override the system settings (temporarily).
- Open the “System Preferences” and click on “Language & Region”.
- Click on “Advanced” on the bottom of the Window.
- Define your desired separators under “Number separators”.
Please note: Usually you need to restart Excel in order to see the effect.