Useful Google Sheets functions that don't exist in Excel

Natalia Sharashova by , updated on

This blog post covers those Google Sheets functions that Excel doesn't have. They are conveniently classified by Google based on their primary task. So just pick the group from the table of contents below and you'll find their descriptions with the simplest examples.

Did you know Google Sheets has some features that you won't find in Excel? I'm speaking about some very useful spreadsheet functions that will certainly lighten your work. Some of them help import and filter your data, others manage your text. But no matter their task, all of them are worth to be mentioned.

Special Google Sheets functions

The first group embraces those Google Sheets functions, that you're unlikely to meet in Excel even as tools.

Google Sheets ARRAYFORMULA

Typically, Google Sheets formulas work with one cell at a time. But having the entire range of cells scanned and calculated would save your time drastically. This is when Google Sheets array formulas come to play.

Array formulas are like more powerful upgraded formulas. They process not just one cell but entire ranges of cells – as many rows or columns as your formula contains. Besides, they make non-array formulas work with arrays, too!

In Excel, you have to keep in mind that you're entering an array formula because you are to finish it not with just Enter but Ctrl+Shift+Enter. The curly brackets at both ends of the formula will let you know you've succeeded.

In Google Sheets, this was solved with a special function:

=ARRAYFORMULA(array_formula)

You put your entire Google Sheets formula with ranges inside those standard round brackets and finish as usual – by hitting Enter.

The simplest example would be with the IF function for Google Sheets.

Suppose you have a table with the results of a short survey on Sheet1. The table is linked to a form, so it is being updated constantly. Column A contains respondents' names and column B contains their answers – yes or no.
The list of names and answers.

You need to display the names of those who said yes on Sheet2.

While IF usually refers to one cell, Google Sheets ARRAYFORMULA makes your IF process all names and responses at once. Here's the formula to use on Sheet2:

=ARRAYFORMULA( IF(Sheet1!$B$2:$B$100="yes", Sheet1!$A$2:$A$100, ""))
The IF function wrapped in the ArrayFormula.

See also:

GOOGLEFINANCE function

Have you ever wondered if it's possible to track currency exchange rates in Sheets? Or how much does some item from the imported table cost in your country's currency? And how much did it cost a week ago? A month or a year ago?

Google Sheets answers all these and some more questions with the GOOGLEFINANCE function. It connects to Google Finance servers and fetches the current or historical financial information right to your spreadsheet.

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
  • Though the function lists quite many arguments, only the first one – ticker – is truly required. It is the ticker symbol that this Google Sheets function will consider when fetching the info. For example, GOOG will stand for Google, MSFT for Microsoft.

Other arguments are optional and their usage depends on what you're trying to get:

  • attribute is whatever you're looking for. If omitted, it is "price" by default. But if you need the info on a certain day, you need to enter this part of the formula anyway. You can also fetch the current day's high price ("high"), the previous day's closing price ("closeyest"), or the price as of when the market opens ("priceopen").

    Tip. You can find the full list of attributes on this page devoted to GOOGLEFINANCE.

  • start_date and end date|num_days are used to specify the period over which you want to check the data. You enter the first day of the period and either the last day or the number of days after the start date.
  • interval denotes the frequency at which to return the data. It can be only either "daily" (1) or "weekly" (7).

Example 1. Current stock price

Let's fetch the current stock price of Google from the American stock exchange called Nasdaq:

=GOOGLEFINANCE("NASDAQ:GOOG", "price")
The current Google stock price in the Nasdaq.

Example 2. Historical stock price

In a similar fashion, you can retrieve the info on stock prices for the last 7 days:

=GOOGLEFINANCE("NASDAQ:GOOG", "price", "9/13/2019", 7, 1)
Google stock price for the last week.

Example 3. Current exchange rate

GOOGLEFINANCE also helps to fetch currency exchange rates:

  • =GOOGLEFINANCE("CURRENCY:EURGBP")
    to get rates for turning euros into pound sterlings
  • =GOOGLEFINANCE("CURRENCY:GBPUSD")
    to fetch the info on converting pound sterlings to US dollars
  • =GOOGLEFINANCE("CURRENCY:USDCAD")
    how much does it cost to switch from US dollars to Canadian dollars

Get today's currency exchange rates.

Example 4. Historical exchange rate

Or I can check the exchange rates from the same day a year ago:

=GOOGLEFINANCE("CURRENCY:USDCAD", "price", "9/20/2018")
Fetch the exchange rates dated one year ago.

See also:

Google Sheets IMAGE function

Having pictures in your spreadsheets can be useful, especially for educational purposes. You can incorporate images to drop-down lists to promote the work with your data to the next level.

To supply your data with some artwork, the arsenal of Google Sheets functions includes IMAGE:

=IMAGE(url, [mode], [height], [width])
  • url – the address of the picture on the Web. Required.

    Note. Don't confuse the address of the picture with the page where the image resides. The URL of the picture can be retrieved by right-clicking the image itself and choosing Copy image address from its context menu.

  • mode – decide how to add an image to Google Sheets: fit it to cell size and keep (1) or ignore (2) image aspect ratio; keep the original picture size (3); or set your own image proportions (4). Optional, but uses mode #1 by default if omitted.
  • height and width are used to specify the size if you chose the corresponding mode (#4) beforehand. Optional.

Example 1. Fit image to cell size yet keep aspect ratio

To add an image to Google Sheets so it matches the size of the cell, it is enough to mention only the URL of the picture in the formula. So, I enlarge the row a bit and use the following:

=IMAGE("https://cdn.ablebits.com/_img-blog/google-sheets-functions-not-xl/Strawberry.png")
Add image to Google Sheets and fit its size to cell size.

Example 2. Fit image to cell and ignore aspect ratio

If you want to insert image and stretch it so it fills the cell entirely, it is the mode #2 for the formula:

=IMAGE("https://cdn.ablebits.com/_img-blog/google-sheets-functions-not-xl/Blueberry.png", 2)
Insert image in Google Sheets and fill the entire cell ignoring the picture proportions.

As you can see, this mode doesn't look too appealing. Let's try the next one.

Example 3. Keep the original picture size

There's an option to keep the original size of the image. Mode #3 will assist:

=IMAGE("https://cdn.ablebits.com/_img-blog/google-sheets-functions-not-xl/Blackberry.png", 3)
Add image to Google Sheets in its original size.

Obviously, the cell doesn't expand automatically. So I believe this way is only useful if you have small pictures or adjust cells by hand.

Example 4. Specify image proportions

The last mode (#4) allows you to set the custom width and height of the image in pixels directly in the formula:

=IMAGE("https://ableb_images.s3.amazonaws.com/_img-blog/google-sheets-functions-not-xl/Raspberry.png", 4, 100, 100)
Insert image in Google Sheets and customize its height and width.

Since my images are square, I set 100 pixels by 100. It's clear that the picture still doesn't fit in the cell. But I kept it that way just to show that you should be ready to adjust your cells for all 4 modes.

See also:

Google Sheets QUERY function

I believe QUERY in Google Sheets is the most comprehensive and powerful function you can find. It is used in so many different ways that I'm not sure I can list, let alone count them all.

It can fully substitute Google Sheets FILTER function, and, in addition, it has the capabilities of COUNT, SUM, and AVERAGE function. Well... too bad for them!

Formulas built with Google Sheets QUERY let you handle large datasets right in your spreadsheets. For that, a special Query Language is used – a set of commands that regulate what the function does.

Tip. If you're familiar with databases, these commands may remind you of SQL.

Tip. Don't want to figure out any commands? I hear you. ;) Hop to this part of the post to try the tool that will build Google Sheets QUERY formulas for you.

=QUERY(data, query, [headers])
  • data is where you indicate the table to manage, for example, a named range or a range of cells. This argument is required.
  • query is where your commands begin. Required.

    Tip. You can find a full list of available clauses and the order of their appearances in the formula on this page created by Google for you.

    Note. All clauses should be entered in double-quotes.

  • headers lets you specify the number of header rows. It is optional and, if omitted, takes -1 by default. In this case, Google Sheets QUERY will try and guess the number of headers based on the contents of your cells.

There's so much this function can do and so many use cases it can cover! But I'm going to demonstrate only a few of the simplest examples.

Example 1. Select data using Google Sheets QUERY function

To return your entire table from Sheet1, you need to use the select command and an asterisk (*) that represents all data:

=QUERY(Sheet1!A1:C10, "select *")
Return all data with the QUERY function in Google Sheets.

Tip. If you don't need the entire table and you'd rather pull certain columns, just list them instead of the asterisk:

=QUERY(Sheet1!A1:C10, "select A,C")
Specify columns that you want to return with the QUERY function.

Example 2. Return data by condition ("Where" command)

The clause where lets you specify the condition that should be met in order to return the values. This endows Google Sheets QUERY with filtering powers.

  • Get the list of those movies only that aired after the '50s:

    =QUERY(Sheet1!A1:C10, "select A,C where C > 1950")
    Set the condition in QUERY for records to meet.

  • Or pick dramas only (those movies where Drama appears in the Genre column):
    Pick only those values that fall under the condition.

Tip. You are free to specify as many conditions for as many columns within one formula as you need.

Example 3. Sort data using the "Order by" clause

Surprisingly enough, Google Sheets QUERY can also play the role of the sorting tool. A special command called order by is used for this purpose.

You just type in the column to sort by and then specify the order: ASC for ascending and DESC for descending.

Let's fetch the whole table and sort movies A to Z:

=QUERY(Sheet1!A1:C10, "select A,B,C order by A DESC")
Pull the table and sort at the same time using QUERY for Google Sheets.

Make Google Sheets create QUERY formulas for you

Formulas are great and all, but if you have neither the time nor desire to dig into them, this add-on will help you immensely.

Multiple VLOOKUP Matches does a v-lookup from another sheet. Despite its name, the tool uses Google Sheets QUERY function to return selected multiple columns from another sheet.

Why QUERY? Because it's language allows more than just a vertical lookup. It searches columns in all directions and gets you all matches based on multiple criteria.

To work with the add-on, you don't need to know any of the QUERY clauses at all. And setting up those v-lookup multiple criteria has never been easy:

  1. you just choose a condition from the drop-down list (contains, more than, is between, etc.)
  2. and enter your text, date, time, or a number as is.

And all of these in just one quick step:
Search multiple columns for multiple criteria at once with Multiple VLOOKUP Matches.

The bottom part of the add-on is the Preview area where the QUERY formula is being built. The formula changes right while you set up conditions, so you always see it up-do-date.

It also shows you the vlookup searches returned. To get them in your sheet along with the formula, simply select the cell where to put them and press Insert formula. If you don't need the formula at all, get only matches pasted to your sheet by hitting Paste result.
Use add-on to build Google Sheets QUERY functions for you.

Anyway, you can install Multiple VLOOKUP Matches to your spreadsheets from the Google Workspace Marketplace to prove me right ;) Also, make sure to visit the add-on home page to get to know it better.

See also:

Google Sheets SPARKLINE function

Some time ago we explained how to build charts in spreadsheets. But Google Sheets SPARKLINE is your way to quickly create the simplest charts right in cells.

While Excel has this feature as a tool, in spreadsheets, it is a small function:

=SPARKLINE(data, [options])
  • select the range that should contain the chart – it's your data
  • set the options for the chart like its type, the length of the axes, and colours. As it was with the QUERY function, special clauses are used for this. If you don't indicate anything, the function returns a black line chart by default.

The function is a really great substitute for the big old chart, especially if you're short on time or a place for the chart.

I have a list of incomes over the year. Let's try and build small charts based on that data.

Example 1. Line chart

I merge 4 cells for the chart to look good and enter the following formula there:

=SPARKLINE(B2:B13)
Use Google Sheets SPARKLINE to build a chart right in cells.

I've got a line chart because it is set by default for when you don't specify anything but the range of cells.

Example 2. Column chart

To change the type of the chart, I will need to use the first clause – charttype – followed by the type of the chart itself – column.

Note. Each command should be wrapped in double-quotes while the entire pair put to curly brackets.

=SPARKLINE(B2:B13, {"charttype","column"})
Create a column chart using SPARKLINE in Google Sheets.

Example 3. Fine-tune the chart

The next thing I'm going to do is specify the colour.

Note. Each new pair of clauses should be separated from the previous one by a semicolon.

=SPARKLINE(B2:B13, {"charttype", "column";"color", "orange"})
Fill your cells with a chart and change its colour at the same time.

Google Sheets SPARKLINE lets you set different hues for the lowest and highest records, specify how to treat blanks, etc.

Tip. A full list of commands can be found on this help page.

Sort and filter with Google Sheets functions

Another group of functions helps to filter and sort data in spreadsheets.

Google Sheets FILTER function

I know, I know, filter exists in Excel. But only as a tool that is applied to your master table. And yes, Google spreadsheets have the same tool as well.

But the FILTER function in Google Sheets keeps your original data intact and returns the desired rows and columns somewhere nearby.

Though it's not as mighty as QUERY, it is easier to learn and will do to get some quick excerpts.

This Google Sheets function is super straightforward:

=FILTER(range, condition1, [condition2])

Only two parts are required: range for the data to filter and condition1 for the rule the filter relies on. The number of criteria depends on your task, so other conditions are completely optional.

If you remember, I had a shortlist of fruits and their prices. Here's how Google Sheets FILTER gets me those fruits that cost more than $5:

=FILTER(A2:B10, B2:B10>5)
Filter data in Google Sheets using the function.

See also:

Google Sheets UNIQUE function

In case the table contains duplicate values, you can retrieve those rows that are mentioned only once. The UNIQUE function for Google Sheets will help. With it, it is a question of the range only:

=UNIQUE(range)

Here's how it may look on your data:

=UNIQUE(A1:B10)
Pull unique rows with Google Sheets UNIQUE function.

Tip. Since UNIQUE is case-sensitive, bring your values to the same text case beforehand using the ways from this tutorial.

See also:

COUNTUNIQUE for Google Sheets

Ever wondered how to count unique records in Google Sheets instead of pulling them to a separate list? Well, there's a function that does that:

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

You can enter as many values as you need right into the formula, refer cells from there, or use real data ranges.

Note. Unlike UNIQUE, the function cannot count entire rows. It deals with individual cells only. Thus, each new cell in another column will be treated as unique.

Formulas to count unique cells in Google Sheets.

See also:

Google Sheets SORT

Yet another simple Google Sheets function that doesn't exist in Excel and can belittle the standard tool. ;)

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
  • you enter the range for your table
  • specify sort_column – a number of the column to sort by
  • choose the way to sort rows in is_ascending: TRUE for ascending, FALSE for descending
  • if there are more columns to sort by, continue filling the formula with pairs of sort_column and is_ascending

For this example, I am sorting fruits by price:

=SORT(A2:B10, 2, TRUE)
How to sort in Google Sheets with formulas.

Tip. A couple more extra arguments – and Google Sheets SORT function turns into SORTN. It returns only the specified number of rows rather than the whole table:

  • enter the number of lines you want to get as the second argument
  • the third one is used to indicate the number of ties (similar or duplicate rows), but I don't need it.
  • the rest are the same as for Google Sheets SORT function:

    =SORTN(A2:B10, 5, , 2, TRUE)
    Sort rows in Google Sheets and return only some number of them.

    Tip. You can read more about Google Sheets SORTN on its Docs Editor Help page.

Google Sheets functions to join and split cells

The functions for these tasks are called the same: SPLIT and JOIN.

  • To split cells in Google Sheets with a function, I enter the range with values I want to pull apart and specify the delimiter in double-quotes – space in my case.

    Tip. ARRAYFORMULA enables me to enter and process the entire column, not just one cell. Cool, huh? :)

    =ARRAYFORMULA( SPLIT(A2:A24, " "))

    How to split cells in Google Sheets using formulas.

  • To merge cells back, Google Sheets JOIN function takes over. The function will do if you need to merge records within one-dimensional arrays: one column or one row.

    =JOIN(" ", A2:D2)

    Join cells in Google Sheet with the function.

See also:

Import data from the Web

Were it not for some certain Google Sheets functions, importing data from other spreadsheets and the Web would be a pain in the neck.

How to use IMPORTRANGE in Google Sheets

The IMPORTRANGE function lets you pull data from another document in Google Sheets:

=IMPORTRANGE(spreadsheet_url, range_string)

You just specify a spreadsheet by providing its spreadsheet_url and enter the range – range_string – that you want to retrieve.

Note. The first time you reference another file, the formula will return the error. No need to panic. The thing is, before IMPORTRANGE for Google Sheets can fetch the data, you will have to grant it the permissions to access another spreadsheet. Just hover your mouse over that error and you'll see a button that will help you do that:
Allow the function to access another file.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1V8IjzfD9EiwfkV2wBx8KgJ9g3GQGQOyl3_P3Go/edit","Sheet1!A1:B10")
Import range from another Google spreadsheet.

Tip. I discussed IMPORTRANGE in details in one of the previous blog posts, come take a look. :)

IMPORTHTML and IMPORTDATA

These two functions are designed to import data from various internet pages.

  • If the data of interest is presented as .csv (comma-separated value) or .tsv (tab-separated value) on the webpage, use IMPORTDATA:

    =IMPORTDATA(url)

    Replace that url with a link to your source page or with a reference to a cell with such a link.

  • To fetch only the table from some webpage, use IMPORTHTML instead:

    =IMPORTHTML(url, query, index)

    Specify the url to the page with a table; decide if you want to get a list or a table for query; and if there are several tables or lists on the page, point the function to the correct one by supplying its number:

    =IMPORTHTML( "https://travel.gc.ca/travelling/advisories", "table", 1)

    Import table from the Web.

Tip. There's also IMPORTFEED that imports RSS or ATOM feed, and IMPORTXML that pulls data from data structured in different ways (including XML, HTML, and CSV).

Google Sheets functions to convert numbers and do some math

There's a small group of simple functions – parsers – that convert your number to:

And a small group of operators that can be used in formulas to compare or calculate. You'll find them in one group of operators on this page.

  • ADD, MINUS, DIVIDE, MULTIPLY
  • EQ (check if values are equal), NE (not equal)
  • GT (check if the first value is greater than), GTE (greater than or equal to), LT (less than), LTE (less than or equal to)
  • UMINUS (reverses the sign of the number)

…Phew! What a crowd of Google Sheets functions! :)

Can you believe they don't exist in Excel? Who would have thought? I bet lots of them take Google Sheets a step further in processing your data.

If there are any other functions that you've discovered in spreadsheets that don't fit in Excel, hurry up and share them with us in the comments section below! ;)

Table of contents