Hello and welcome back to Excel Tips and Tricks! This week, we’ve got a General User post that looks at some of the functionality you can find in Google Sheets that Excel is yet to fully master.
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
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.
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!
- Excel Tips & Tricks #468 – Refreshing dealing with Objects
- Excel Tips & Tricks #467 - Excel functions that do not return arrays or ranges
- Excel Tips & Tricks #466 - Setting range permissions in Excel Online
- Excel Tips & Tricks #465 - Using #N/A in charts redux
- Excel Tips & Tricks #464 - Refreshing Keyboard shortcuts
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.