Google Sheets may not have been around as long as Excel, but its capabilities should not be underestimated.

Whether you’re an individual or business, you are bound to be hunting for faster and more effective ways of doing things.

In the past, using Excel as your preferred spreadsheet application was a no-brainer. However, Sheets has caught up in many areas, and in some cases even trumps it.

There are numerous tasks that can be accomplished more easily, thanks to Sheets possessing a surprising number of functions its rival doesn’t have.

It’s worth being aware of them, as it may make you reconsider which platform you use in the future.

In this post, I’ll show you 20 amazing Google sheet functions you won’t find in Excel.

Each function has been summarised with an example demonstrated. Download the example workbook to follow along.

Here are the 20 Sheets functions.

ARRAYFORMULA Function

ARRAYFORMULA – Docs Editors Help (google.com)

Any formula that outputs a range of cells instead of a single value is the definition of an array formula.

These can be nested in the ARRAYFORMULA function. Alternatively, you can wrap them inside a pair of curly braces ({ }), although this method will not work for every function.

Syntax 

ARRAYFORMULA ( array_formula )

• array_formula: a single range, multiple ranges, or function that returns a result of more than one cell.

Example

= ARRAYFORMULA ( A3:H8 )

The top table has been replicated starting at cell A10.

If you reference the range without putting it inside the ARRAYFORMULA function or curly braces, a #VALUE! error will occur.

FLATTEN Function

FLATTEN – Docs Editors Help (google.com)

If you want to stack multiple ranges on top of each other, use the FLATTEN function.

In Excel, appending one set of values to another is a rather convoluted process that requires Power Query or unwieldy formulas, but Sheets makes it very easy.

You simply select a single-column range for the range1 argument, and then select another for [range2] and beyond, if necessary.

The only downside is that it’s not possible to combine whole tables with a single formula.

If you attempt this, you’ll end up with one mega-long column with all the values from each, so you must have a separate formula for each column.

Syntax

FLATTEN ( range1, [range2,...] )

• range1: first range to stack.
• [range2]: same as range1 but optional.

Example 

= FLATTEN ( A17:A21, A25:A29 )

Ranges A17:A21 and A25:A29 are included to combine Table 1 and Table 2 into a single table.

SPLIT Function

SPLIT function – Docs Editors Help (google.com)

Excel has a Text to Columns feature where you can split a range of cells by a delimiter, and the result will spread across two or more columns.

Sheets makes the process even easier, as there is a function for it.

Check out this post if you want to discover a few other ways to split text into multiple columns.

Syntax

SPLIT ( text, delimiter, [split_by_each], [remove_empty_text] )

• text: the text to be split.
• delimiter: the character(s) used to determine where the text should be split.
• [split_by_each] (optional — default is TRUE): TRUE treats each character separately in the delimiter, whereas FALSE sees them as one block.
• [remove_empty_text] (optional — default is TRUE): TRUE treats consecutive instances of a delimiter as one, whereas FALSE will add empty cell(s) instead.

Example

= SPLIT ( E4, " " )

In Example 1, the Product column has been split with an empty string delimiter, so each space found invokes a new column.

= SPLIT ( E4, "at", FALSE )

Example 2 looks for instances of the characters at, but as [split_by_each] is set to FALSE, the only entries that get split are Chocolate Chip and Potato Chips because the letters appear consecutively.

= SPLIT ( E4, " ",, FALSE )

The FALSE [remove_empty_text] parameter in Example 3 picks up the additional spaces that exist in two of the values.

ISEMAIL Function

ISEMAIL – Docs Editors Help (google.com)

If you have a list of values and want to validate which ones are email addresses, use the ISEMAIL function. It will display TRUE for valid emails, and FALSE for invalid ones.

This will only validate the text has the correct syntax to be an email address and does not check if the email address exists.

Syntax

ISEMAIL ( value )

• value: the value to check for being a valid email address.

Example

= ISEMAIL ( A4 )

The emails listed in column A each have a TRUE or FALSE value beside them depending on their validity.

ISURL Function

ISURL – Docs Editors Help (google.com)

This function uses the same concept as ISEMAIL.

Say you have a list of URLs and want to find which ones are legitimate — TRUE is returned for the valid URLs and FALSE for the invalid ones.

Syntax

ISURL ( value )

• value: the value to verify as a web address.

Example

= ISURL ( A4 )

The web addresses listed in column A each have a TRUE or FALSE value beside them depending on their validity.

COUNTUNIQUE Function

COUNTUNIQUE – Docs Editors Help (google.com)

To count the number of unique values in a range you can use the COUNTUNIQUE function.

Syntax

COUNTUNIQUE ( value1, [value2, ...] )

• value1: the first cell or range to count unique values for.
• [value2] (optional): same as value1.

Example

= COUNTUNIQUE ( B4:B8 )

The unique values in each column of the table have been counted and displayed in cells B9 to I9.

GOOGLETRANSLATE Function

GOOGLETRANSLATE – Docs Editors Help

If you have some text you want to translate, GOOGLETRANSLATE can convert it from one language to another.

Syntax

GOOGLETRANSLATE ( text, [source_language, target_language] )

• text: cell or text string to translate.
• [source_language] (default is auto): two-letter code of the source language, e.g. en for English or es for Spanish. To auto-detect the language, use auto.
• [target_language] (default is system language): two-letter code of the target language, e.g. en for English or es for Spanish.

Example

= GOOGLETRANSLATE ( B4, "en", "es" )

Values from the Product column have been translated from English to Spanish.

DETECTLANGUAGE Function

DETECTLANGUAGE – Docs Editors Help (google.com)

Perhaps you have some text written in another language, yet you cannot work out what language it is.

In this case, the DETECTLANGUAGE function comes in handy as it will detect and display the language of a cell or text string.

Syntax 

DETECTLANGUAGE ( text_or_range )

• text_or_range: string or range of cells that contains text to evaluate.

Example

= DETECTLANGUAGE ( B4 )

An assortment of products in different languages has been created to demonstrate this function. The language of each value is displayed by referencing each cell.

GOOGLEFINANCE Function

GOOGLEFINANCE – Docs Editors Help

The GOOGLEFINANCE function provides a way of fetching financial data from the stock market via Google Finance.

Syntax 

GOOGLEFINANCE ( ticker, [attribute], [start_date], [end_date|num_days], [interval] )

• ticker: code name of the company on the stock market, such as AMZN for Amazon. Specify an exchange before the name to ensure the data is fetched from that source. For example, NASDAQ: AMZN.
• [attribute] (optional — default is “price”): the attribute to pull in from Google Finance. Examples include price for the real-time price quote, high for the highest price of the day, and low for the lowest.
• [start_date] (optional): the beginning date of the data.
• [end_date] | [num_days] (optional): the final date or number of days elapsed from the start date.
• [interval] (optional): how frequently updated the data is. Accepted values are DAILY or WEEKLY.

Example

= GOOGLEFINANCE ( "NASDAQ:AMZN", A4 )

In column A, a list of attribute names is present. Each metric has been fetched for Amazon, and the value displayed in column B.

= GOOGLEFINANCE ( "NASDAQ:AMZN",  "all", "01/03/2021", "25/03/2021", "DAILY" )

In column D, historical data has been retrieved between the dates defined in the [start_date] and [end_date] parameters.

Sometimes an #N/A error will display when you navigate to a worksheet that uses the GOOGLEFINANCE function.

For some reason the data doesn’t always load, so the best remedy is to reset the cell. Do this by copying the formula, clearing the contents of the cell, and then pasting the formula back in.

IMAGE Function

IMAGE – Docs Editors Help (google.com)

Not only does Google Sheets let you insert images inside of the cell, but it conveniently provides a function to do so as well.

This is useful if you have a table of employees, an inventory list, or any scenario where images can help identify rows of data.

Syntax

IMAGE ( url, [mode], [height], [width] )

• url: full web address of the image.
• [mode] (optional — default is 1): sizing option for how the image should display in the cell.
  • 1 ensures the image fits in the cell, whilst maintaining aspect ratio
  • 2 fills the whole cell with the image, ignoring aspect ratio
  • 3 displays the image at its original size
  • 4 allows the image to be displayed at a custom size according to the [height] and [width] parameters
• [height] (optional): height of the image in pixels. The [mode] parameter must be 4 to set a custom height.
• [width] (optional): width of the image in pixels. The [mode] parameter must be 4 to set a custom width.

Example

H4: = IMAGE ( "https://i2.wp.com/www.twosisterscrafting.com/wp-content/uploads/2018/03/carrot-cake-bars-main.jpg" )
H5: = IMAGE ( "https://i.ndtvimg.com/i/2017-11/crackles_620x330_81510898394.jpg", 1 )
H6: = IMAGE ( "https://handletheheat.com/wp-content/uploads/2018/02/BAKERY-STYLE-CHOCOLATE-CHIP-COOKIES-9-768x768.jpg", 2 )
H7: = IMAGE ( "https://handletheheat.com/wp-content/uploads/2018/02/BAKERY-STYLE-CHOCOLATE-CHIP-COOKIES-9-768x768.jpg", 3 )
H8: = IMAGE ( "http://www.nicecupofteaandasitdown.com/biscuits/media/arrowroot.jpg", 4, 162, 163 )

Every product in the table has an image and each mode has been demonstrated.

QUERY Function

QUERY function – Docs Editors Help (google.com)

The QUERY function allows you to use the Google Visualisation API Query Language, which is very similar to Structured Query Language (SQL), to query worksheet data just like you would with a database.

This is a very useful and powerful function to help you manipulate data. Check out this definitive guide to the QUERY function to find out everything you can do with it.

Syntax

QUERY ( data, query, [headers] )

• data: the range of values to query.
• query: the query code to run, written in the Google visualisation API query language. This is input as a text string value.
• [headers] (optional): the number of header rows at the top of the range.

Example

= QUERY ( A3:H13,
 "SELECT * WHERE B='West'"
 )

The first query selects all the data in the table and extracts the rows where the Region column is equal to West.

Remember: you must reference the whole column in the query.

= QUERY ( A4:H13, "SELECT C, SUM(H), MAX(H), MIN(H), AVG(H)
 GROUP BY C
 ORDER BY SUM(H) DESC
 LABEL C 'City', SUM(H) 'Total', MAX(H) 'MAX Total', MIN(H) 'MIN Total', AVG(H) 'AVG Total'
 FORMAT SUM(H) '$#', MAX(H) '$#', MIN(H) '$#', AVG(H) '$#'" )

The second query groups each city and displays the sum, maximum, minimum and average of the TotalPrice column. It also orders the rows in descending order based on the totals.

To make the heading names more clear, a LABEL statement has been used containing the new names for each column.

The output has also been altered using FORMAT, so the figures appear with a preceding dollar sign ($) and are rounded to whole amounts.

The one annoyance with both is having to repeat what was defined in the SELECT statement for each column you want to change.

=QUERY(A4:H13,
 "SELECT SUM(H)
 PIVOT C
 FORMAT SUM(H) '$#.##'"
 )

The last query generates a pivot table containing the total sales by city. This capability is one of the most impressive things about the QUERY function.

However, it doesn’t replace regular pivot tables as QUERY function results are devoid of the extra UI features, such as the expand and collapse buttons, and layout options.

SPARKLINE Function

SPARKLINE – Docs Editors Help (google.com)

The SPARKLINE function allows you to generate a sparkline, which is a miniature chart that displays in a single cell. It’s ideal for obtaining a quick overview of the data it represents.

Syntax

SPARKLINE ( data, [options] )

• data: the data range to be used.
• [options] (optional): a range of options are available to determine how the sparkline should display. For example, use “line” for a line graph or “bar” for a stacked bar chart.

Example

= SPARKLINE ( B4:B25 )

The first example shows a table containing closing market data pertaining to the company selected in the dropdown. In cell B26, there’s a sparkline that represents the column’s data.

This is the most basic way of including a sparkline.

= SPARKLINE ( GOOGLEFINANCE ( E4, "price", TODAY() - 30, TODAY(), "DAILY" ), { "charttype", "line"; "linewidth", 1; "color", "blue" } )

The second example shows data for Big Tech companies based on a 30-day period.

Cells H4 to H8 contain customised sparklines that have been set by defining the chart type, line width and line colour.

= SPARKLINE ( QUERY ( GOOGLEFINANCE ( E4, "price", TODAY() - 30, TODAY(), "DAILY" ), "SELECT Col2", -1 ), { "charttype", "column"; "highcolor", "green"; "lowcolor", "red" } )

Alternatively, the adjacent examples use column charts to illustrate the same data. The addition of the coloured bars makes it easy to see the highest and lowest values.

REGEXEXTRACT Function

REGEXEXTRACT – Docs Editors Help (google.com)

The REGEXEXTRACT function allows you to extract characters from a value using a regular expression.

This consists of a search pattern comprising a sequence of characters.

Syntax

REGEXEXTRACT ( text, regular_expression )

• text: the text string to use.
• regular_expression: the regular expression to perform on text.

Example

In the side table, each column features an example of a regular expression performed on the Product column of the main table.

J4: = REGEXEXTRACT (E4, "Chocolate" )
K4: = REGEXEXTRACT (E4, "(.+)" )
L4: = REGEXEXTRACT (E4, "Chocolate|Wheat" )
M4: = REGEXEXTRACT (E4, "......" )
N4: = REGEXEXTRACT (E4, "C\w+" )

• J4 extracts the exact text string.
• K4 extracts whole text.
• L4 extracts one string or another.
• M4 extracts the same number of characters as periods (6).
• N4 extracts a string based on a partial match (text beginning with C).

Valid regular expressions return a value, whereas errors produce #N/A because they fail to meet the conditions of the expressions.

REGEXMATCH Function

REGEXMATCH – Docs Editors Help (google.com)

Using the same expressions featured in the REGEXEXTRACT example, the successful ones return TRUE, whereas the errors equal FALSE.

Syntax

REGEXMATCH( text, regular_expression )

• text: the text string to use.
• regular_expression: the regular expression to perform on text.

Example

J4: = REGEXMATCH ( E4, "Chocolate" )
K4: = REGEXMATCH ( E4, "(.+)" )
L4: = REGEXMATCH ( E4, "Chocolate|Wheat" )
M4: = REGEXMATCH ( E4, "......" )
N4: = REGEXMATCH ( E4, "C\w+" )

This example uses the same regular expressions as what were shown for REGEXEXTRACT. The difference being is the table displays TRUE and FALSE values.

REGEXREPLACE Function

REGEXREPLACE – Docs Editors Help (google.com)

The REGEXREPLACE function replaces all successful regular expressions with a different text string.

Syntax

REGEXREPLACE ( text, regular_expression, replacement )

• text: the text string to use.
• regular_expression: the regular expression to perform on text. The result of this will be replaced by what’s included in the replacement parameter.
• replacement: the text to replace the result of regular_expression.

Example

J4: = REGEXREPLACE ( E4, "Chocolate", "Choccy" )
K4: = REGEXREPLACE ( E4, "(.+)", "Product" )
L4: = REGEXREPLACE ( E4, "Chocolate|Wheat", "C/W" )
M4: = REGEXREPLACE ( E4, "......", "6" )
N4: = REGEXREPLACE ( E4, "C\w+", "c" )

 Each valid regular expression is replaced with a text string, and the ones that don’t return an #N/A error.

IMPORTDATA Function

IMPORTDATA – Docs Editors Help (google.com)

There’s no need to go through some clunky menu system to import a CSV file.

With this function, you only have to specify the URL of the file and it will load itself in the worksheet. The file must be housed remotely to work, however.

Syntax

IMPORTDATA ( url )

• url: the web addresses of the .csv or .tsv file.

Example

= IMPORTDATA( "https://people.sc.fsu.edu/~jburkardt/data/csv/biostats.csv" )

This imported sample CSV file contains biometric statistics for a group of office workers and was found at CSV Files (sc.edu).

IMPORTFEED Function

IMPORTFEED – Docs Editors Help (google.com)

An RSS or Atom feed can be imported into a worksheet.

These are both XML based formats that allow for web content from different sources to be syndicated.

Syntax

IMPORTFEED ( url , [query], [headers], [num_items] )

• url: the web address of the RSS or ATOM feed.
• [query] (optional — default is “items”): specifies the data to grabbed from the web address.
• [headers] (optional — default is FALSE): the number of header rows at the top of the range.
• [num_items]: the number of items displayed, starting with the newest.

Example

= IMPORTFEED ( "https://www.nasa.gov/rss/dyn/breaking_news.rss",, TRUE )

The RSS feed found at NASA’s website has been fetched and displayed. To ensure column headings are visible, the [headers] parameter must be set to TRUE.

IMPORTHTML Function

IMPORTHTML – Docs Editors Help (google.com)

The IMPORTHTML function can import data from a website, simply by stating the URL and the index number of the list or table desired.

This allows you to circumvent the rigmarole of menus and windows, as is the case in Excel for this process.

When finding the index number, you can either use a bit of trial and error until you arrive on the right table or list, or you can look at the page’s HTML structure and count each table or list element.

Syntax

IMPORTHTML(url, query, index)

• url: the address of the webpage.
• query: the two options are list and table. The one needed depends on the structure containing the data according to the webpage’s markup.
• index: the index number of the table or list to return. This is according to its position in the HTML structure.

Example

= IMPORTHTML ( "https://en.wikipedia.org/wiki/2020_United_States_presidential_election", "table", 18 )

US Election results have been imported from Wikipedia. The 18th table has been selected.

IMPORTRANGE Function

IMPORTRANGE – Docs Editors Help (google.com)

If you have data in another Sheets workbook you want to pull in, use IMPORTRANGE. It is very flexible as you can import a single cell or a whole worksheet of values.

Syntax

IMPORTRANGE ( spreadsheet_url, range_string )

• spreadsheet_url: the web address of the spreadsheet.
• range_string: the sheet name and range specified as a string.

Example

= IMPORTRANGE ( "https://docs.google.com/spreadsheets/d/1LHZcJHO5c8ni5_mOBI60pQSV9yTq1vcwyAXnWn9CkME/edit?usp=sharing", "18. IMPORTHTML!A3:H11" )

Although sharing a range from another workbook would be a better example to show, to avoid any access issues, the same workbook URL has been used. The IMPORTHTML worksheet has been pulled in, containing the same table you saw in the previous section.

IMPORTXML Function

IMPORTXML – Docs Editors Help (google.com)

A more accurate and precise alternative to IMPORTHTML is the IMPORTXML function. You can specify an XPath query to extract a specific part of a webpage.

Syntax

IMPORTXML ( url, xpath_query )

• url: the web address to be inspected.
• xpath_query: the XPath query to run on the webpage.

Example

= IMPORTXML ( "https://en.wikipedia.org/wiki/2020_United_States_presidential_election", "//table[@class='wikitable'][7]/tbody/tr" )

The same table has been fetched again, and here’s a breakdown of the XPath code:

• //table: jumps straight to the table element
• [@class=’wikitable’]: selects the ‘wikitable’ class name of the table element
• [7]: there are multiple tables with the class name ‘wikitable’, so this represents the seventh one
• /tbody/tr: all the tr elements that appear within the tbody tags are selected

Conclusion

Hopefully, these 20 functions have given you an insight into just how powerful Google Sheets can be.

Its capabilities as a cloud platform are impressive, and although it’s farfetched to say it can replace Excel in every instance, it can certainly complement it.

Some of the functions are so powerful, they challenge long-standing Excel features. For many tasks you would be better off going straight to Sheets.

Take the IMPORT batch of functions and GOOGLEFINANCE, for example.

They are capable replacements for Excel’s Get and Transform Data features. There is no requirement to go through windy GUI windows to import data from a website or CSV file.

Likewise for other tasks…

Dividing a range of values with SPLIT eradicates the need for Text to Columns.

ISURL and ISEMAIL make data validation menus redundant for URLs and emails.

Sparklines don’t need anything else other than a formula.

Even pivot tables can be produced with the QUERY function alone, albeit with limitations. What is the next step though? Perhaps fully loaded formula-based pivot tables. Watch this space.

It begs the question: will formulas replace features? They already are, but this trend is likely to continue. Sheets may have been quicker out of the blocks on this, but Excel is likely to follow suit in the coming time.

One thing is for sure: Excel is being chased hard, and to maintain its leading position, it must keep pace in areas where its competition is superior.

Exciting times lie ahead as we continue to see Excel and Sheets battle it out against each other.