Useful Google Sheets functions that don't exist in Excel
This blog post brings the majority of those Google Sheets functions that Excel doesn't have to your attention. 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 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, formulas in Google Sheets work with one cell at a time. But having an 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:
You put your formula with ranges inside those standard round brackets and finish as usual - by hitting Enter.
The simplest example would be with the IF function.
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.
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,""))
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.
- Though the function lists quite many arguments, only the first one - ticker - is truly required. It is the ticker symbol that the 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 the function.
- 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")
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)
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
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")
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 IMAGE function was created in Google Sheets:
- 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")
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)
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)
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)
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.
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 the 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.
- 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, 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 *")
=QUERY(Sheet1!A1:C10,"select A,C")
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")
- Or pick dramas only (those movies where Drama appears in the Genre column):
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")
See also:
Google Sheets SPARKLINE function
Some time ago we explained how to build charts in spreadsheets. But SPARKLINE in Google Sheets 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:
- 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)
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.
=SPARKLINE(B2:B13,{"charttype","column"})
Example 3. Fine-tune the chart
The next thing I'm going to do is specify the colour.
=SPARKLINE(B2:B13,{"charttype","column";"color","orange"})
Google Sheets SPARKLINE lets you set different hues for the lowest and highest records, specify how to treat blanks, etc.
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.
The function is super straightforward:
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 short list 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)
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:
Here's how it may look on your data:
=UNIQUE(A1:B10)
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:
You can enter as many values as you need right into the formula, refer cells from there, or use real data ranges.
See also:
Google Sheets SORT
Yet another simple function that doesn't exist in Excel and can belittle the standard tool. ;)
- 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)
- 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)
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," "))
- 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)
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:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1V8IjzfD9EiwfkV2wBx8KgJ9g3GQGQOyl3_P3Go/edit","Sheet1!A1:B10")
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)
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).Functions to convert numbers and do some math in Google Sheets
There's a small group of simple functions - parsers - that convert your number to:
- date - TO_DATE
=TO_DATE(43,882.00) - dollars - TO_DOLLARS
=TO_DOLLARS(43,882.00) - TO_PERCENT
- TO_PURE_NUMBER (a number without formatting)
- TO_TEXT
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 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! ;)
You may also be interested in:
- 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.
