How to change your Google spreadsheet's language & related formatting settings

I was having a problem with a Google Sheets spreadsheet and am writing this post in case anyone is having a similar one. It's that type of nitpicky problem that wasn't a huge deal but was affecting my workflow — my spreadsheet thought I was in the UK when I'm really in the US.

What difference does it make if a spreadsheet thinks I'm writing in UK English vs. US English? Well, for me, it came down to the dates column. It really, really wanted my dates to be in this format: DD/MM/YYYY. Whereas we weird Americans tend to like it this way: MM/DD/YYYY, with the month first. I automatically type them like so: 3/16 (for March 16), expecting the spreadsheet to autoformat my entry to 3/16/2016. But…it wasn't. It just sat there: 3/16, and left aligned, as if I'd typed in text instead of a numeric value. If I typed in the words March 16, then it autoformatted it to 16/03/2016, which, though accurate in its own way, was not what I wanted.

So! I tried a couple things first that didn't solve it entirely. First of all, under Format --> Number --> More Formats --> More date and time formats…, I was able to find a way to change that column specifically to be the date format I prefer. But, it didn't "stick." Anytime I typed in a new date, the sheet still didn't seem to know what to do with it. I worked around it by copying and pasting the correctly formatted dates, but that was inadequate.

I also went to my universal Google account settings and found that, somehow and sometime, I had indeed been set to UK English, so I switched that back to US. I refreshed my spreadsheet and hoped that would be the end of it. Nope, still UK. I restarted my browser. Nope.

Then, I found it: File --> Spreadsheet settings…. Hurrah!

You can set your locale, and sure enough, mine was set to United Kingdom. As the Google help notes, "This affects formatting details such as functions, dates, and currency." I'd indeed noticed instead of a dollar sign ($) in my toolbar, there was a pound symbol (£).

You can also change your time zone (useful to know if you're trying to keep track of specific times entries were added to your spreadsheet) and your display language.

Click to embiggen.

Don't get me wrong — I love the UK, but I need my spreadsheets to function the way I'm expecting them to on this side of the pond. Hope that helps someone else who was frustrated by the wrong date formatting, currency settings, or whatever else was bugging you!



Post a Comment

Related Posts Plugin for WordPress, Blogger...