I love New Zealand – the natural beauty, professionalism, work ethic and fun. Heavenly!
Recently, while I was there, I learnt a great lesson about Excel. My Excel formulas stopped working. The reason was so simple but I was stupid enough not to discover it till I did some deep dive. Here is my learning …
Photo credit: Foter / CC BY-SA
I conducted 24 sessions in 5 days for various customers in New Zealand. While I was there, I changed the regional settings to English (New Zealand). Then I forgot to change it back to English (India).
And that led to a disaster. When I was back in India, I was demonstrating a simple function to a customer …
This is a simple function which shows the Month in English based upon the date in B3 cell.
Unfortunately, it did not work. I got an error message:
There is no way this function cannot work. It has been working ever since Excel existed – which is 26 years.
I tried many other functions which accept multiple parameters – none of them worked.
I was confused. To add to my confusion, I had done what Microsoft has said should NOT be done! I had installed Office 2016 on top of Office 2013 (Click to Run version). This is not supposed to be done. But I did it anyway. All other applications were running fine.
So I though it is a clash between Office versions. I repaired both versions, reinstalled them… but no use. The problem persisted.
Finally, I realized how stupid I have been. I just noticed the tooltip of the TEXT function…
We never notice the tooltip if you know the function already. But I did.
Did you notice it? Parameters are usually separated by a comma. Here it is a semicolon. That is the reason I was getting the error message.
So I changed the country to English (India) and problem was solved.
What did I learn?
Excel respects the list separator setting in Regional Settings in Control Panel in Windows. For English (New Zealand), this separator is a semicolon (rather than a comma).
Bottom-line? Notice the regional settings. There are a lot of options there and these options are pervasive. These can (and should) affect many things.
It is also worth appreciating the fact that the tooltip changes based upon regional setting List Separator value. Amazing. Is it not?
Wow! I live and work in New Zealand and have always used a comma as my list delimiter. I just checked my regional settings (Windows 10) and my delimiter for NZ English is listed as a comma. I checked several other Englishes (India, Zimbabwe, UK, South Africa) and all had commas. But French Canadian used a semicolon so I can testify that regional settings can change the list delimiter. Thanks for pointing this out!
I learnt the importance of noticing the List Separator the hard way… so I thought it would be useful to others as well. Of course, instead of changing the locale, the list separator can be changed directly as well.
But this is something which should be standardized across the organization by the IT team.