Five tips for using Googlesheets if you’re used to Excel

Here are five tips for using Googlesheets if you normally use Excel.

1. Check your document is actually a Google Sheet

If you’ve imported it from Excel, it might still be an Excel file that’s being read by Google in compatibility mode. You can tell because name and file type of the workbook will end in XLSX. 

This is the worst of both worlds, as you are getting neither Excel nor Google functionality. Save the file as a Google sheet (in the file menu) 

2. Get more shortcuts 

There are lots of shortcuts in Google. You can see the full list of shortcuts in the help menu, listed by ribbon area.

This is a really helpful way to lay out shortcuts.

Even better: at the bottom of this dialogue window is an option to turn on “compatible shortcuts”. This adds a bunch more shortcuts that you might be familiar with from Excel.

For example F5 to Goto a cell or range. (if you don’t switch this on, F5 just refreshes the workbook) 

3. Check / fix the date format at workbook level before doing anything else

Probably the most annoying feature of Google sheets is that it defaults to US dates. And if you don’t have an organisational account, you have to change the date for every new sheet you start or open.

Deal with this early and deal with it at the workbook level (File – Settings then change to your location). This is much easier and robust than formatting as you go. 

4. If you know you need 100s of columns, start it in Excel first

Google starts you out with 26 columns and 1000 rows. Expanding rows in bulk is pretty easy – there’s an option at the bottom to add x more rows. But no equivalent for columns. There is code you can add, but I find it’s easier to create an Excel document with the right number of columns and import that. 

5. ARRAYFORMULA is your friend 

If you’ve embraced array formulas in Excel then you might be entering formulas like this:

A1:A10*B1:B10 – this will create a spilled column of ten multiplication results. 

To do this in Sheets, you need to wrap ARRAYFORMULA around it:

=ARRAYFORMULA(A1:A10*B1:B10)

CTRL+SHIFT+ENTER is a shortcut to add this.

Dynamic array functionality actually came from Google in the first place, but it’s fair to say Microsoft have more than caught up. That said, lots of specific dynamic array formulas you might be using like UNIQUE or SEQUENCE or VSTACK work just fine in both without modification.

Don’t panic, get stuck in.

As well as the specific tips for using Googlesheets above, I’d also just say – don’t panic. It might feel a bit weird if you’re used to Excel, but I would say 95% of what I do in Excel is possible for a normal user in Googlesheets. (And more if you’re prepared to get into scripting).

There are also some bits of functionality that I prefer in Googlesheets – for example, I think that pivot tables are easier to explain and show people in Googlesheets initially.

There are definitely some quirks though, and it isn’t as well documented as Excel. Most search engine results either lead back to Google’s help (which refuses to admit any flaws) or to AI generated fluff. Here is a source that I have found helpful for Googlesheets – Ben L Collins.

I offer tailored Googlesheets training – see here for more information.