by 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:
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.
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 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")
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 (appeared in Excel by the end of 2022)
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:
- 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.
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.
- 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 *")
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")
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):
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")
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.
Filter and Extract Data 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 filters multiple columns by multiple criteria at once.
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:
- you just choose a condition from the drop-down list (contains, more than, is between, etc.)
- and enter your text, date, time, or a number as is.
And all of these in just one quick step:
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 filter 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.
Anyway, you can install Filter and Extract Data 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:
- 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.
Note. Each command should be wrapped in double-quotes while the entire pair put to curly brackets.
=SPARKLINE(B2:B13, {"charttype","column"})
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"})
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 (appeared in the latest Excel versions)
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:
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)
See also:
Google Sheets UNIQUE function (appeared in the latest Excel versions)
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)
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:
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.
See also:
Google Sheets SORT (appeared in the latest Excel versions)
Yet another simple Google Sheets 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)
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)
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:
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).
Google Sheets functions to convert numbers and do some math
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 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! ;)
14 comments
How do you reference a cell in the same spreadsheet and write to it. For example I am in C2 and would want to write something to D2 based on the data in C2.
Hello Pav,
If I understand you correctly, entering =C2 into D2 will do the trick. Feel free to read this article to learn other basic operations: Create and edit Google Sheets formulas
Hi, I need to combine 4 sheets that contains around 100 tabs each. It would be hard to name all tabs in an IMPORTRANGE Function.
Is there a way to do it with a QUERY function? I have programming background but I don’t know where to start.
Can you please advise? Thank you very much
Hi Alicia,
To combine data from different files, you will still need to use the IMPORTRANGE function. It's the only way to transfer data between different spreadsheets in Google Sheets.
However, there's a Combine Sheets add-on that will help you do that quickly. It is described in this blog post along with other possible ways, feel free to take a look.
Does SUMIFS work from IMPORTRANGE? I tried to use SUMIFS from a range imported from a different google sheet, and I received "0". There was no other syntax error.
Hello Nik,
I'm afraid you will have to import the range with a standalone function first and then apply SUMIFS.
Alternatively, you can use the QUERY+IMPORTRANGE combo since QUERY knows how to sum by your criteria as well.
Thank you so very much! I love reading your articles and I learn alot.?☺?☺?
Appreciate your kind words, Aekaterini! It's good to know our articles help :)
Interesting. Although there is now a filter and sort option in excel 365. Used with unique, it's quite powerful
Hi Andrew,
That's the beauty of Google Sheets where these functions are available to everyone :)
Hi!
I want to use IMPORTRANGE to merge several google sheets (those sheets are provided with format and with the same template), however what I want to see or import only are certain rows with data on it, not the whole template per sheet.
Say I have 5000 rows on each sheets, File 1 has 500 rows with data, File 2, has 200, & File 3 has 100 rows have data only, how can I apply IMPORTRANGE on this scenario since as per example there are no added condition after the string_range
Hoping for your positive response.
Hi Jess,
You can limit the number of rows to return manually in the second argument of the IMPORTRANGE.
But if you want the formula to do that automatically, try wrapping IMPORTRANGE in the QUERY function. You'll be able to make QUERY check and pull all non-empty rows using IMPORTRANGE. You'll find more details in the following blog post: Google Sheets QUERY to import ranges
Hi there!
I need to get the stock price at the specific time.
I use this formula : =GOOGLEFINANCE("NASDAQ:GOOG"; "price";time(15;55;0)) but the result still error
At this example I want to get the stock price at 5 minutes before closing.
is it possible?
give me an advice on how to solve this matter. Thanks
Hello!
The GOOGLEFINANCE function can show data for a certain date only at the beginning or at the end of a trade. Data cannot be shown at any other point in time. You can see more detailed information here.