Must-know Google Sheets functions not available in Excel

Google Sheets ARRAYFORMULA

Typically, Google Sheets formulas work with one cell at a time. However, scanning and calculating entire ranges of cells will significantly save you time. This is where Google Sheets array formulas come to play.

Array formulas are like more powerful versions of regular formulas. They process 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 remember to finish a formula with Ctrl+Shift+Enter to turn it into an array formula. The curly brackets at both ends of the formula will indicate success.

In Google Sheets, this is simplified with a special function:

You put your entire Google Sheets formula with ranges inside the 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 survey results on Sheet1. The table is linked to a form so it is 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 lets 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, ""))

GOOGLEFINANCE function

Have you ever wondered if it's possible to track currency exchange rates in Google Sheets? Or find out the cost of an item in your country's currency? And maybe even 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 current or historical financial information right into your spreadsheet.

Let me briefly explain each GOOGLEFINANCE argument:

• Though the function contains quite many arguments, only the first one — ticker — is truly required. is truly required. Google Sheets function will consider the ticker symbol here to fetch the info. For example, GOOG stands 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 will find the full list of attributes in this Google Docs Editors Help for 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 first date.
• interval tells the frequency at which to return the data. It can either be "daily" (1) or "weekly" (7).

Here are several simple examples of how you use the GoogleFinance function.

Example 1. Current stock price

Fetch the current stock price of Google, Microsoft, Nvidia and Netflix from the American stock exchange called Nasdaq:

=GOOGLEFINANCE("NASDAQ:GOOG", "price")

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:USDEUR")
  how much does switching from US dollars to euros cost

Google Sheets function to detect language

Have you ever received data with text in multiple languages and needed to identify the language of each piece of text? Google Sheets has a unique function for that — DETECTLANGUAGE.

It scans the provided text and returns the detected language code. This function can be incredibly useful when dealing with multilingual datasets (for localization, etc.)

Suppose you have text in column A. Use the DETECTLANGUAGE function in column B to identify the language of each text string:

=DETECTLANGUAGE(A2)

It will return the language codes (e.g., "en" for English, "no" for Norwegian) for each cell in column A.

Google Sheets function to translate language

Need to translate text directly in your spreadsheet? Google Sheets offers the GOOGLETRANSLATE function, which uses Google Translate to convert text from one language to another.

• specify the text (cell with the text) you want to translate
• provide the code of the source_language (use "auto" for Google Sheets to detect the language automatically)
• provide the code of the target_language (omit to translate to your system language)

For example, make Google Sheets formula to detect the language in column A and translate each phrase to English:

=GOOGLETRANSLATE(A2,"auto","en")

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 can't list them all.

It can fully substitute the Google Sheets FILTER function, it can sort your data, count, sum, and apply the AVERAGE function.

All in all, formulas built with Google Sheets QUERY let you handle large datasets easily. It uses a special Query Language — a set of commands that regulate what the function does.

I won't explain every detail here because I did it in this separate article. But to stir up your interest, I'll show you a few examples.

How do you pull data by date and/or by text (by condition)?

A special clause where lets you specify the condition that should be met to return specific rows only. This endows Google Sheets QUERY with filtering powers.

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

  =QUERY(Movies!A1:C10, "select A,C where C > 1960")

• Or pick all dramas that aired after 1950 only (those movies where Drama appears in the Genre column):

  =QUERY(Movies!A1:C20,"SELECT * WHERE (lower(B) contains 'drama') AND (C > 1950)")

Google Sheets SPARKLINE function

Creating quick, in-cell charts has never been easier than with the SPARKLINE function in Google Sheets. Unlike traditional chart tools, SPARKLINE is a lightweight function that generates simple, compact charts within a single cell.

• select the range with data for the chart
• set up extra options for the chart such as type, axis length, colors. (If you don't mention anything, the function will return a black line chart by default.)

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

See also:

• How to highlight uniques or duplicates in Google Sheets
• How to find and remove uniques or duplicates in Google Sheets
• Count cells by color in Google Sheets
• COUNT by multiple criteria in Google Sheets
• COUNT and COUNTA functions in Google Sheets

How to use IMPORTRANGE in Google Sheets

The IMPORTRANGE function lets you pull data from other documents in Google Sheets:

You just specify a spreadsheet_url of the file with your source sheet, and enter the range_string that you want to import.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1V8IjzfD9Eiwfk9SF6OV2wBx8KgJ9g3l3_P3Go/edit","Sheet1!A1:C11")

IMPORTHTML and IMPORTDATA

These two Google Sheets functions are designed to import data from web pages and online sources into spreadsheets without manual copy-pasting.

• IMPORTHTML(url, query, index)

  Use this Google Sheets function to import data from tables or lists on web pages.

  • provide the URL of the web page
  • decide if you want to get a "list" or a "table"
  • and if there are several tables or lists on the page, specify which one you need by supplying its number

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

• =IMPORTDATA(url)

  Just provide the URL of your source page or a reference to a cell with such a link.