Tracy van der Schyff

#Office365Challenge – Today’s post is not so much an awesome thing about Excel / SharePoint / Office 365, but definitely something I think you should know. This post is dedicated to Anne Williams for reminding me to chat about this. After quite a bit of research I must admit I don’t have all the answers and I welcome comments and feedback to put me on the right track.

Note: If any of you ever have anything you would like me to blog about – which will solve a problem or pain for you – please let me know. Add a comment on one of my posts. The purpose of the #Office365Challenge is exactly that! Helping people with stuff they struggle with everyday. And know this – I don’t have all the answers – but you will force me to investigate it and hopefully figure it out.

So as mentioned, after quite a bit of research – I still couldn’t figure out when this “change” happened. Upgrading to Windows 8 or Office 2013? Suddenly I noticed my way of compiling formulas was not “that popular anymore”. I was accustomed to using commas to separate fields and compile formulas, now it changed to semicolons. I do know this is caused by the Regional Settings on your pc.

Below is what the formula builder (help screen) now looks like. It actually indicates semicolons. If I wasn’t paying attention and just typed my formula as before (with commas) – it would return an error.

(Yes, YES, Apart from not getting the formula to work, I couldn’t spell the word “right” RIGHT either. haha.

So to fix this you’ll have to make changes to your regional settings of your pc. KEEP IN MIND THAT THIS AFFECTS EVERYTHING – NOT JUST EXCEL. I’ve pressed my Windows Button on my keyboard and searched for Control Panel (as I actually don’t know how to find it any other way anymore). Click on Control Panel in the search results to open.

In the Control Panel (1), you’ll find the Region (2) (or Regional settings – might differ between different Operating Systems). Under Formats Tab (3) click on Additional Settings (4). Under the Numbers Tab (5), you’ll see the option to change the Decimal symbol (6) and the List separator (7). Apply the changes. These will look different on different machines – so find the List separator and Decimal Symbol under your Region settings – you might have to scratch around a bit. Change the Decimal Symbol to a period “.” and the List separator to a comma “,”

If Excel was open while you were applying this change, you will have to close and open again. Now you’ll notice that the formula help indicates a comma and not a semicolon.

This also affects formulas used in SharePoint Calculated Columns, so careful when using blogs to help you build these as these separators might differ. Personally I’ve chosen to use the semicolons going forward, suck it up and just get used to it.

Hope this has solved a pain for you today?

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.