Dot notation as decimal separator in Excel and SPSS on Mac OS X

This issue might causing more people troubles than just me, so I thought to post it on my blog. For a scientific articles I needed to create some graphs, both from SPSS (a Kaplan-Meier curve) and Excel (the other graphs).

As a default setting, my computer uses the comma as the decimal separator. For these figures I needed to change it into  a dot as the decimal separator. The approach is different for Excel and SPSS.

Using dot as decimal separator in Excel (version 2011 for Mac)

Go to “System Preferences” > “International” (called “Taal en tekst” in Dutch).

Then go to the “Formats” tab (called “Notatie” in Dutch), and customize the number settings. Here you can change the decimal separator to “dot” (and the thousand separator to “comma”, as these two cannot have the same value).

Now restart Excel, and you’re done!

Using dot as decimal separator in SPSS (version 18 for Mac)

This works a little different, as SPSS (or “PASW”, as this first is called officially does not take its notation from the system preferences. But fortunately the solution is very easy.

Click “File” > “New” > “Syntax”. In the newly opened syntax editor, type

SET LOCALE = “en_US”

and click the green “Selection” arrow (or press Cmd + R on your keyboard).

This will change the decimal separator to a dot in SPSS.

Note: according to this website it should be possible to use the syntax “SET LOCALE = DOT” in SPSS version 20.0 (by IBM). At least in version 18.x this did not work.

Hope this saves you some time searching!

10 Responses to “Dot notation as decimal separator in Excel and SPSS on Mac OS X”

  1. Ascension says:

    Thanks a lot for your suggestion

  2. user says:

    Thanks a lot! what a stupid default setting for SPSS. On windows I had to omit the quotes for the “en_US” to make it work. I have SPSS 20 and the syntax “SET LOCALE = DOT” did not work

  3. camilla says:

    Thank you soo much!! I have been trying to figure this out for a long time!!

  4. Johan Lund says:

    Thank you very much for your good advice! I have a new MacBook Air from October 2012 with OS X Mountain Lion and the System Preference panel differs sligthly from the previous one. The “International” icon is now “Language & Text” and “Formats” is now “Region”. The number separator is edited in “Numbers”.

  5. sarian says:

    Hartelijk bedankt! Precies de info waar ik naar opzoek was!

  6. Mo says:

    I am running Mac OSX Snow Leopard. This worked for me, I would recommend quitting excel from the doc, before changing the system preferences. Thank you so much I have been sitting with this problem for 2 years!

  7. qwerty73 says:

    THANKS, just thanks.
    Googling “spss decimal separator”, this appears as the 3rd result, but it’s 10^99 more useful than the “official” help from IBM.

    THANKS

  8. DulceF says:

    Thanks a lot :)

  9. Eve says:

    How did you get the toolbar in the syntax window to display the “active dataset”? I can’t find that option under the customize toolbar settings!

  10. I did not have to select it in that SPSS version. I would try to enable different toolbars and see if the option is there, if you need it at all. Maybe it is automatically the active dataset if just one dataset is open.

Leave a Reply

Your email address will not be published. Required fields are marked *