11 Lesser-Known Google Sheets Functions You Can Use Every Day

Math and numbers

Google Sheets has some terrific features that you probably use all the time. But when it comes to functions, there may be several you didn’t know existed. These handy functions and their accompanying formulas help you compare values, get financial data, convert arrays to columns or rows, and more. This list includes 11 lesser-known Google Sheets functions that can help you be more productive.

Tip: these Google Sheets keyboard shortcuts can help you save much time.

1. Compare Values: GT, GTE, LT, LTE, EQ

When you want to compare two values or the values in two different cells, there are five operator functions that make the task easy. Whether you’re comparing sales amounts or expenses over time, these functions have you covered.

The following are the functions with the syntax for each formula, which is the same.

  • GT (Greater Than): GT(value1, value2)
  • GTE (Greater Than or Equal To): GTE(value1, value2)
  • LT (Less Than): LT(value1, value2)
  • LTE (Less Than or Equal To): LTE(value1, value2)
  • EQ (Equal To): EQ(value1, value2)

For the arguments, you can enter the exact values you want to compare, the cell references containing the values, or a mix of both.

Once you enter the formula, you’ll receive a result of “True” or “False.”

Let’s look at a few examples.

In this example, we are using GT to compare the values in cells A1 and B1 with this formula:

=GT(A1,B1)
GT function in Google Sheets

The result is False, as the value in cell A1 is not greater than the value in cell B1.

Using the less than function, we’re comparing the values 15 and 20 with this formula:

=LT(15,20)
LT function in Google Sheets

The result is True, as 15 is less than 20.

Note: if you’re working with multiple spreadsheets, you’ll want to link data between the Google Sheets spreadsheets.

2. Compare Text Strings: EXACT

Maybe the comparison you want is for two strings of text. Using the EXACT function, you can see whether the strings contain the same characters, including case sensitivity, spaces, and hidden characters. This is useful for finding typos that may not be apparent at first glance.

The syntax for the formula is EXACT(string1, string2), where both arguments are necessary and can be cell references, text, or both. The function returns “True” for an exact match or “False” for no match.

In this example, we are comparing the text strings in cells A1 and B1 with this formula:

=EXACT(A1,B1)
EXACT function using cell references

The result is False. Upon closer inspection, we see that the text in cell B1 contains a period after the B, whereas the text in cell A1 does not.

In another example, we are comparing the text in cell A1 with the text string “Location B Revenue and Expenses for Quarter 1” with this formula:

=EXACT(A1,"Location B Revenue and Expenses for Quarter 1")
EXACT function using a cell reference and text

Again, this result is False. When looking closely, we see that “Quarter” is spelled differently. It’s misspelled in cell A1 but correct in the formula text string.

3. Count Unique Values: COUNTUNIQUE

With the COUNTUNIQUE function in Google Sheets, you can get a total of distinct values in your spreadsheet. You may want to find unique customer names, email addresses, or cities.

The syntax for the formula is COUNTUNIQUE(value1, value2,…), where only the first argument is required. You can use cell references, values, and text for the arguments.

In this example, we are examining how many distinct customer names are in our sheet, as we know that some customers ordered more than once. The following formula is used to find unique records in the cell range A2 through A8:

=COUNTUNIQUE(A2:A8)
COUNTUNIQUE function using a cell range

We received a result of 5, as only five distinct names appear.

In another example, we are counting the unique items in a list of entered values. Using this formula, we are inserting our cell range and text:

=COUNTUNIQUE(A2:A3, "Bill Brown", "Sue Smith")
COUNTUNIQUE function using a cell range and text

We received a result of 3, there are only three unique names among the two in the cell range and two in the formula.

4. Count Unique Values With Criteria: COUNTUNIQUEIFS

If you find the COUNTUNIQUE function useful, you may also appreciate the COUNTUNIQUEIFS function. It allows you to enter criteria to only count unique values based on a condition.

The syntax for the formula is COUNTUNIQUEIFS(range, criteria_range1, criterion_1, criteria_range2, criterion_2,…). Use “range” for the cell range to count, “criteria_range1” for the cells to evaluate, and “criterion_1” for the condition. Enter multiple criteria ranges and criterion if needed.

In this example, we are counting the unique values in cells A2 through A8 for Order Totals in cells B2 through B8 that are greater than $75 with this formula:

=COUNTUNIQUEIFS(A2:A8,B2:B8,">75")
COUNTUNIQUEIFS function

The result is 4. There are five Order Totals that are greater than $75, but because Jim Jones appears twice, he is only counted once as a unique value.

COUNTUNIQUE function result

In an example using multiple conditions, we are again counting the number of orders over $75, but also count those with an Item Total less than 10.

=COUNTUNIQUEIFS(A2:A8,B2:B8,">75",C2:C8,"<10")
COUNTUNIQUE function with multiple criteria

Our result is 1. Although Jim Jones had two orders over $75, and both contained less than 10 items, he can only be counted once as a unique value.

COUNTUNIQUE function with multiple criteria result

Note: while function doesn’t appear in the Google Sheets Function List, you do receive assistance on the arguments, just like other functions, when you enter it in your sheet.

5. Get Financial Details: GOOGLEFINANCE

If you use Google Sheets for tracking finances or managing your budget, you can take advantage of it for your investments as well. Using the GOOGLEFINANCE function, you can obtain many different details for stocks and mutual funds directly from Google Finance.

The syntax for the formula is GOOGLEFINANCE(symbol, attribute, start_date, end_date, interval), where only the first argument is required for the ticker symbol. The “attribute” argument is ideal for getting the exact data you want, such as price, high, low, or market cap. Because the list of attributes is extensive and varies by current, historical, and mutual fund data, you can search for the function on the Google Docs Editors Help page.

Use the last three arguments to obtain historical data with the start and end dates, and the frequency. Be sure to include all arguments in quotation marks.

In this example, we are taking a look at Google’s current price, which is the default if you leave the “attribute” argument blank:

=GOOGLEFINANCE("NASDAQ:GOOGL")
GOOGLEFINANCE function

Let’s see what today’s trading volume is for Google with this formula:

=GOOGLEFINANCE("NASDAQ:GOOGL","volume")
GOOGLEFINANCE function with an attribute

For example, using historical data, we are checking for Google’s closing price from January 1, 2023, to February 28, 2023, by week.

=GOOGLEFINANCE("NASDAQ:GOOGL","close","1/1/23","2/28/23","WEEKLY")
GOOGLEFINANCE function for historical data

Tip: the GOOGLE FINANCE function also lets you convert currencies in Google Sheets.

6. Identify a Language: DETECTLANGUAGE

If you import data from another source, or copy and paste from a website or email, and are unsure of the dialect, you can use the DETECTLANGUAGE function to determine what it is.

The syntax for the formula is DETECTLANGUAGE(cells_text), where the only argument allows you to enter the cell reference, cell range, or exact text to identify. If more than one language is found, the result only displays the first one. That result is a two-letter code identifying the region.

As an example, we are learning which language appears in cell A1 with this formula:

=DETECTLANGUAGE(A1)
DETECTLANGUAGE function using a cell reference

The result is “ko,” which is Korean.

7. Translate a Language: GOOGLETRANSLATE

You may need to translate a language in your sheet rather than simply identify it, using GOOGLETRANSLATE.

The syntax for the formula is GOOGLETRANSLATE(cells_text, source, target), where only the first argument is required, and you can enter the cell reference or actual text. Google automatically detects the source language if you leave the “source” argument blank. However, if you want to include the target language, you must also include the source, which can be the two-letter code or “auto.”

In this example, we are translating that same text to French and allowing Google to automatically detect the source language with this formula:

=GOOGLETRANSLATE(A1,"auto","fr")
GOOGLETRANSLATE function using Auto

In another example, we are translating the text in cell A1 with the “source” in cell B1 (“es” for English) and the “target” in cell C1 (“ko” for Korean) with this formula:

=GOOGLETRANSLATE(A1,B1,C1)
GOOGLETRANSLATE function using multiple cells

8. Insert and Customize an Image: IMAGE

While you can easily insert an image in your spreadsheet, you may want to add a picture from a web page and customize the size. Using the IMAGE function in Google Sheets, you can specify the dimensions too.

The syntax for the formula is IMAGE(url, mode, height, width), where only the first argument is required.

The arguments are as follows:

  • URL: the web link to the image, including “https://” and placed within quotation marks.
  • Mode: the sizing for the image, where 1 resizes it to fit in a cell and maintain the aspect ratio (default if omitted), 2 stretches or shrinks the image to fit in a cell and ignores the aspect ratio, 3 leaves the image at its original size, and 4 lets you choose custom dimensions.
  • Height and Width: the height and width you want to use in pixels. Remember to choose 4 for the mode argument.

In this example, we are inserting the image at the URL “https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800×430.jpg” with the default “mode” using this formula:

=IMAGE("https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800x430.jpg")
IMAGE function with default settings

In another example, we are entering our own “height” (230) and “width” (400) using “mode” 4:

=IMAGE("https://www.maketecheasier.com/assets/uploads/2023/03/Google-Finance-Amazon-Page-800x430.jpg",4,230,400)
IMAGE function with custom settings

9. Import a Table or List: IMPORTHTML

Along with grabbing an image from a web page, you can import data from the Web. Using IMPORTHTML, you can pull in a table or list from a page without the hassle of copy/paste or additional formatting.

The syntax for the formula is IMPORTHTML(url, query, index), where you’ll want to use all three arguments. Use them in the formula in the following ways:

  • URL: the web link to the image, including “https://” and placed within quotes.
  • Query: enter either “table” or “list” (including the quotation marks) to designate the structure for the data.
  • Index: a number to identify the table or list on the web page, starting with 1.

This example is for our James Bond fans. We are importing a table from Wikipedia that contains James Bond movies using the formula below:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_James_Bond_films","table",1)
IMPORTHTML function with a table

To break down the formula, we have the URL, the “query” as “table,” and the “index” of 1, since it’s the first table on the web page.

James Bond movie table from Wikipedia

In another example using the list query, we are using one of our own pages to capture the table of contents:

=IMPORTHTML("https://www.maketecheasier.com/track-stocks-google-sheets/","list",7)
IMPORTHTML function with a list

This formula has the URL, the “query” as “list”, and the number 7 for the “index,” as this is the seventh list on the page.

Table of contents for an article

Note: You can also sort by date in Google Sheets.

10. Count the Number of Workdays: NETWORKDAYS

Have you ever needed to count the number of workdays or business days between two dates? Using the NETWORKDAYS function, you can obtain the number of workdays, excluding weekends and holidays, between two dates.

The syntax is NETWORKDAYS(start, end, holidays), where only the first two arguments are required. To use the “holidays” argument, refer to a range in your sheet that contains those dates.

We are calculating the number of workdays between June 1, 2023, and December 31, 2023, with this formula:

=NETWORKDAYS("6/1/2023","12/31/2023")
NETWORKDAYS function using dates

Notice that when you include the start and end dates in the formula, they should be placed within quotation marks.

In this example, we are using the cell references containing our dates instead:

 =NETWORKDAYS(A1,B1)
NETWORKDAYS function using cells

In an example with holidays, we have our list in cells C2 through C7 and include this as the third argument with this formula:

=NETWORKDAYS(A1,B1,C2:C7)
NETWORKDAYS function with holidays

11. Convert an Array: TOROW and TOCOL

If you work with arrays in your sheet and want to transform one into a single row or column, you can use the TOROW and TOCOL functions. These were introduced in early 2023 and make arranging data in your sheet much easier.

The syntax for each function’s formula is the same as TOROW(array, ignore, scan) and TOCOL(array, ignore, scan), where only the first argument is required.

The optional arguments work as follows:

  • Ignore: by default, the functions don’t ignore any values (0). Use 1 to ignore blanks, 2 to ignore errors, or 3 to ignore both blanks and errors.
  • Scan: by default, the functions scan the data by row (False). Use True to scan by column instead.

In an example using TOROW, we are transforming our array in cells A1 through C2 into a single row with this formula:

=TOROW(A1:C2)
TOROW function

To scan by column instead of row, use the following formula instead:

=TOROW(A1:C2,,TRUE)
TOROW function with the scan argument

The “scan” argument set to True changes the order of the displayed result. The function scans from top to bottom (column) rather than left to right (row).

For the TOCOL function, we are converting our array in cells A1 through C2 into a single column.

=TOCOL(A1:C2)
TOCOL function

To scan by column instead of row with this function, use this formula:

=TOCOL(A1:C2,,TRUE)
TOCOL function with the scan argument

Again, our result orders the data from top to bottom rather than left to right.

Next: master VLOOKUP to find one value based on another value in the same row.

Frequently Asked Questions

How do I make formulas visible in Google Sheets?

When you enter a formula in Google Sheets, you can see it by selecting the cell and looking at the Formula Bar below the toolbar. But if you prefer to see the formulas within the cells instead, go to the “View” menu, move your cursor to “Show,” and select “Formulas” in the pop-out menu.

What’s the difference between a formula and a function in Google Sheets?

While many use the terms “formula” and “function” interchangeably, they are not the same. A formula is an expression that is created to instruct Sheets. A formula starts with an equal sign, then contains the instruction. In =GT(A1,A2), the entire string is a formula.

A function is a premade formula that Google Sheets creates. It contains instructions behind the scenes that tell Sheets what to do. Often times, you can place a function within a formula. In this formula: =GT(A1,A2), the function is GT.

Where can I get help with a function in Google Sheets?

When you enter a function into a formula, you’ll see a question mark in blue appear next to the cell. Select that question mark to turn on formula help, or press F1 on your keyboard.

You’ll see details such as an example, what each argument expects, and a link to “Learn more,” which takes you to the function’s Google Support page.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Is this post useful?
Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sandy Writtenhouse
Sandy Writtenhouse - Staff Writer

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.