This is an overview of features where Google Sheets outperforms (in my opinion) Excel of Google Sheets.
The features are highlighted but not discussed in step-by-step detail. Future posts will dive into the low-level mechanics of some of these features.
#1 – Searchable Drop-Down Lists
Data Validation drop-down lists can be searched for content that begins or contains specific text.
Using an existing list of data, we can refer to the list for use in a Data Validation drop-down.
Although Excel can similarly create Data Validation lists, what makes Google Sheets superior are the following behaviors:
- The drop-down can be searched for items that begin or contain specific text.
- If blank cells are included in the Criteria range (assuming for future expansion of the list), the blank cells are not displayed in the drop-down.
- If duplicate entries exist in the source list, the drop-down only displays a unique list of entries.
This makes creating, using, and maintaining a drop-down list much easier.
#2 – The SPLIT Function
The SPLIT function allows you to dynamically split your text into multiple columns.
This may not sound like much at first, but it has some important advantages over traditional text-parsing techniques.
The SPLIT function requires two arguments, the cell containing the text you want to parse, and the delimiter to search for to split against.
If we were to split someone’s full name into first and last name, we could write the following formula that examines cell B3 for a space character.
=SPLIT(B3, " ")
Still not impressed. Try this on for size.
Suppose you need to split text that has a mixture of delimiters.
You can write the SPLIT function so that multiple delimiters are contained within the Delimiter argument.
=SPLIT(F3,"/ , -")Each delimiter is listed within the double quotation marks.
#3 – Automatic Refresh for Pivot Tables
In Google Sheets, Pivot Tables automatically refresh when the source data changes.
#4 – ImportXML to Import Data from Web Pages
Google Sheets can import XML data from a web page even if the data is not in a table format.
In the below example, we have two tables in the middle of the web page that are in a proper table format. Excel would have no problem acquiring these tables.
However, the table in the upper-right is not in a table format and cannot be accessed by Excel.
Using the IMPORTXML function, we can dig deep into the HTML code to locate the node name or attribute name from the source code.
This gives us the ability to import pretty much any component of a web page into Sheets.
The IMPORTXML function requires the website address and the node name or attribute name of the desired component.
=IMPORTXML("https://www.x-rates.com/table/?from=EUR&amount=1","//*[@id='ratesTrends']/li")
If we wanted to bring the other tables into Sheets, because these are proper tables, we can use the IMPORTHTML function.
#5 – Lookup Images
In Excel, images are placed on a “floating” layer called the Drawing Layer. It isn’t possible to embed an image directly in a cell like traditional data.
Google Sheets allows you to place images either in a cell or over a cell, like Excel.
Existing images that were originally placed over cells can be easily converted to in-cell images with a simple right-click step.
Because the images can be placed in cells just like traditional data, we can exercise a lookup operation on the images to return pictures instead of text.
#6 – Search & Replace with REGEX
REGEX stands for REGular EXpresssion.
With REGEX, we can search the text for patterns and replace them with something new.
Take for example the need to remove the middle names form any name that has more than a first and last name.
We can run a Find/Replace operation, but instead of typing specific text to locate, we will enter a pattern.
We then provide an expression that represents what we wish to keep.
The result is full names with the middle names removed if they existed.
BONUS: There are additional functions that use REGEX: REGEXREPLACE, REGEXEXTRACT, and REGEXMATCH.
#7 – Insert QR Codes with Image Function
The IMAGE function allows you to retrieve an image from the Web by entering a URL.
The images can then be referenced (as with a VLOOKUP) just as traditional data.
Another advantage of the IMAGE function is the ability to automatically insert QR codes.
The requirements are the link to the Google API along with the desired size and cell address that holds the Web link. You can also define the output encoding and error correction if needed.
#8 – Translate with a Function
You can create spreadsheets that support multiple languages using Google Translate.
The requirements are the cell that holds the reference text, the language the source text was originally entered, then the desired output language.
There are over 100 supported languages; sorry, no Klingon as of yet.
#9 – Chat Inside Your Spreadsheet
If you are sharing your worksheet with colleagues, you can initiate a chat session during editing.
When a person with whom a sheet has been shared opens the sheet, an icon appears in the upper-right of the window indicating they have opened the document.
This makes collaboration within the company more efficient than sending emails back and forth to one another.
#10 – Query Function to Supercharge Your Analysis
The QUERY function allows you to create complex reports with a single function. The output of the QUERY is like a Pivot Table report; the output can be a cross-tabular report with totals.
But where a traditional Pivot Table requires the construction to take place in many drag-and-drop steps within a construction interface, the QUERY function performs the same thing in a single cell’s formula.
The QUERY function uses a query language that is similar to SQL language.
To generate automatic total rows that move with the data, it takes a bit more code in the QUERY function.
The above example even contains embedded calculated columns to derive information not originally included in the source data.
It looks intimidating, but once you understand the requirements, it’s not too bad.
BONUS FEATURES
There are many features that both Excel and Sheets offer, but Sheets offers them a different way.
Extracting Stock Information
Sheets offers a function called GOOGLEFINANCE that allows you to query a ticker (ex: NASDAQ:GOOG) and retrieve prices between a date range for a defined interval.
Excel has a similar function that is still in development called STOCKHISTORY. This is like GOOGLEFINANCE with fewer attribute extraction options.
Sparklines
Where Excel utilized a Sparkline tool to construct Sparklines, Sheets uses a SPARKLINE function.
Because you are using a function to generate the Sparkline, you can embed other functions within it to create extremely dynamic results.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
