Excel Tip of the Week #240 - 6 things Google Sheets does better than Excel

This week, guest author David Benaim takes us through a Basic User-level comparison of just what Excel could stand to learn from its great rival, Google Sheets.

David runs Xlconsulting an IT consulting firm in Cambodia where he helps clients get better use out of Excel for data and teaches Excel/presentation skills to professionals & at a university.

We normally hear that Google Sheets’ main advantages involve co-authoring. The newest Excel versions (online & desktop) share this capability but there are areas where Google Sheets really surpasses Excel.

9 months ago, I joined a volunteer group (Nerd Night) which relies on Google Sheets. Petrified at the thought of being unfaithful to my beloved Excel I dived in and was pleasantly surprised...

#1 -  Locking cells/protection: Select cells to lock & allocate who can edit

Whoever created Excel’s protection feature should take a long hard look at themselves. Not only does Excel’s method take multiple steps but it also blocks several features. In Google Sheets you select the cells you want locked, right click→ Protect range, then you can choose who gets edit rights, (e.g. only you, your dad & Brad Pitt). This may come as a shock, but just like Facebook & Cambridge Analytica, Google knows who you are!

#2 - Data validation: Autocomplete, more validation types etc.

88% of spreadsheets have errors and so many of these would be avoided if people used data validation more. Quite simply it means locking what data can be in cells to parameters (e.g. dates in 2016 or a positive number or a dropdown list of text). In Google Sheets, right click on cells you want validated and choose “Data validation” or watch this instructions video. My favourite Excel-beating features are:

  1. If your source dropdown list contains duplicates or blanks, the output dropdown ignores them showing unique non blank unique values (great for a dynamically expanding list)
  2. When a user types in a cell with a dropdown, it uses autocomplete i.e. it restricts the dropdown list to whatever characters you’ve entered.
  3. You have more allowed types, like date “is valid date” or text “is valid url”

#3 - Extra functions: COUNTUNIQUE & TEXTJOIN

Consider this scenario & try the questions below…

Product Customer name Email
Wine Spider-Man spidey@avengers.com
Beer Iron Man rich@avengers.com
Juice Hulk greenthing@avengers.com
Juice Captain America shielded@avengers.com
Wine Thor DoYouReallyThinkICanUseEmail@avengers.com
  1. How many unique products did we sell? Wine, Beer and Juice = 3 products. There is no built-in function in Excel that can answer this... Seriously!
  2. Combine all email addresses or names into a cell so I can e.g. paste it into an email “to” line or as a summary. (e.g. a cell that says “Spider-Man, Iron Man, Hulk, Captain America, Thor”).  Again, no formula in most versions of Excel can do this.

Google Sheets has a COUNTUNIQUE function. It’s like COUNTA (counts how many cells have data) but excludes duplicates.

Google Sheets also has TEXTJOIN function with 3 inputs =TEXTJOIN(Delimiter in speech marks, Option to include/exclude blanks, range of cells). I love TEXTJOIN, there end up being so many uses for it that you only realise once you start using it. You can do fancier things with it to, e.g. enter a line break between each entry or give a list of people based on criteria (e.g. Juice clients: “Hulk, Captain America” or Wine clients: “Spiderman, Thor”), these could be considered like a CONCATENATEIF or VLOOKUP with multiple entries.

Google Sheets also has functions that return a range of cells with changes made, e.g. FILTER returns a range where a certain column (or columns) meet criteria, UNIQUE returns a range where each row is unique, SORT does similarly with rows sorted as requested and SORTN is similar but only shows the top 5 (or whatever is specified).

I say no Excel formulas can do these things, but solving these is technically possible with PowerPivot, Array formulas or UDFs. Never heard of any of those? Neither have around 99.99% of Excel’s users. They involve coding & are conceptually complicated to grasp. (Note that Excel’s newest version has the same TEXTJOIN function)


#4 - Comments: Reply, @mention, mark as done, see all in sidebar

In today’s WhatsSnapYouInstaLinkedTweetFace world we spend commenting is a way of life, but Excel’s comments suck. In Google (select cells then right click→ Insert comment). Functionality includes a sidebar showing all comments, reply, mark as resolved and even @mention people (who get an email notification).

# 5 - Explore: Use AI to get quick answers on your data

When you are inside a table in Google Sheets, click the “Explore” button on the bottom right. This gives you a sidebar with many goodies using AI including summary stats (SUM, AVG, etc.) premade Pivot Charts/Pivot tables on your data (e.g. Bar chart/table with all sales summed by product) text sentences such as “most popular in one category” or highest/lowest data point, a Q&A segment where you can ask questions on your data and it’ll answer in form of text, numbers or charts. Read more here. Excel is shortly launching an “Insights” feature which for now only does a fraction of what “Explore” does.

#6 - An image in a cell from a formula

=IMAGE(“url”) allows sheets to convert an image url into the actual image, you can even specify the image should take up the whole cell or edit parameters for dimensions. And anticipating your question… Yes this works as follow up things like VLOOKUPs or Pivot tables!

Summary

Excel you are my one true love. You have an elegant and classic nature that will never be replaced, but there is a modern alternative, sleeker & versatile player in the mix that I will no longer ignore.

Google Sheets is determined to usurp Excel from the spreadsheet throne. They’ve swooped in, updating some of Excel’s outdated features for the age of the internet & faster computing. Sheets takes advantages of sharing, @mentions, AI and even (something Excel should have done 15 years ago)... automatically updating Pivot Tables. Today’s Excel is probably twice as powerful as it was 15 years ago with features like Map/Waterfall charts, Slicers, Power Query or Flash Fill. Google however could win the battle if Microsoft doesn’t improve the way it communicates all its simple yet unknown game changing new features to its users.

About the Author

David Benaim is the ICAEW Cambodia representative who runs Xlconsulting, David specialises in Excel, PowerBI & presentation skills consulting & runs corporate training courses, plus he has over 100 public videos on Microsoft Office, Google docs & PowerBI.

Thanks to David for his contribution!

Previous post - How to standardise data
TOTW index 
Next post - Cell borders

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous