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

by
Spread the love

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.

Content

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)

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)

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)

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")

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)

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")

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")

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.

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")

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.

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")

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

=GOOGLEFINANCE("NASDAQ:GOOGL","volume")

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")

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)

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")

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)

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")

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)

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)

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.

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)

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.

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")

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)

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)

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)

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

=TOROW(A1:C2,,TRUE)

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)

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

=TOCOL(A1:C2,,TRUE)

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.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Sandy Writtenhouse

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.

Comments are closed