Excel Tips and Tricks #461 – Things Google Sheets (still) does better than Excel

Way back in Tip #240, we explored 6 features of Google Sheets that were either missing in Excel, or simply inferior. So ahead of our upcoming webinar Excel vs Google Sheets: the battle of the spreadsheets, I thought I’d land some early blows in favour of the search giant’s spreadsheet tool by looking at some choice features where, in my view, Google still comes up trumps (and yes, there is a certain irony that Tip #240 was written by David Benaim, who will be championing Excel in the webinar!).

When the Chips are Down

Excel has come a long way in recent years with improvements to collaborative working and sharing of files. With SharePoint and OneDrive, it is much easier now to share and work simultaneously in an Excel file. Recent changes to Teams to allow attendees to work ‘live’ on an Excel file in a meeting also represent a huge step forward. But Google Sheets has an edge in two key ways.

One is the addition of Smart Chips – simple, quick tags that you can add to any Google Workspace file (Docs, Sheets or Slides) that link directly to people, events, or other files. In Google Docs you can also use this to insert links to places, dates or even drop-downs (which means we can probably expect this functionality in Sheets soon). To insert them in Sheets, you just start typing ‘@’ in a cell and away you go…

The other edge that Google Sheets has on the collaboration front is simple – it works! For inexplicable reasons, every time you share an Excel file with different groups, it generates a new sharing link – it’s very easy to get into a muddle with this and share the wrong link to the wrong person. With Google Sheets, it’s one link regardless of how you share the file or who you share it with – the URL is king. And because everything is governed by the URL, you can also very easily link to specific sheets and specific cells, to take your collaborators to exactly what you want them to look at. Plus, you can give comment access in Google Sheets, which you still cannot do in Excel.

Functions that are Sheets Ahead

In Tip #240 David mentions the TEXTJOIN function – well, finally, Excel has this one in the batch of new functions released earlier this year. And while Excel introduced TEXTSPLIT, Google Sheets has had SPLIT (which does the same thing) for a little while. Some of the other new Excel functions are more digestible versions of what you can do in Google Sheets, for example TEXTBEFORE and TEXTAFTER are more user friendly than Google’s REGEXEXTRACT – which requires a good working knowledge of regular expression to apply successfully, though ultimately is more powerful. Bizarrely, COUNTUNIQUE remains a ‘uniquely’ Google Sheets function, though it can now be replicated in Excel by using COUNTA and UNIQUE in combination.

Two functions that truly are unique to Sheets are IMPORTRANGE and QUERY.

IMPORTRANGE solves a very specific challenge in Google Sheets where you want to reference cells in another workbook – as there is no ‘file’ to point to – but in doing so, delivers a solution far more stable than in Excel. Once again, the URL is king, which means once the formula is set up, it is almost impossible to break unless the source sheet no longer exists. As an added bonus, only the person who sets up the function needs access to both sheets, it refreshes automatically, and can return an array. These features are lacking in Excel, despite various attempts to improve the functionality.

QUERY is a fantastic function that opens up a whole world of opportunity for the more advanced user, as it quite literally allows users to apply SQL-style queries to a selected range. There’s absolutely nothing comparable in Excel.

This function is covered in more detail in Tip #308

Check Yourself

The good news is, many of the features that were covered in Tip #240 have now been delivered in Excel, or are coming very soon. Vastly improved commenting has finally arrived, and the Google Sheets “Explore” function is mirrored by the “Analyse Data” option on the Home tab of Excel. And yes, finally, Excel will soon have the ability to insert images into cells. Big tick. But wait, that’s something else Google Sheets can do which Excel cannot – add tick boxes to cells.

This makes it much easier to create checklists, but behind the scenes, these are just binary true/false values, so you can also interact with them in formulas.

Summary

Since the demise of Lotus 1-2-3, for a while Excel lacked any genuine competition in the spreadsheet world. But with Sheets, we now have a market where Google and Microsoft are actively playing a fantastic game of one-upmanship. And for once it’s us, the users, who benefit, as they each race to bring out new functionality that not only keeps pace with but usurps the other. The Battle of the Spreadsheets is well and truly on!