8 useful functions in Google Sheets you may not know

Google Sheets is an incredibly versatile and powerful tool that combines data organization and computation in a spreadsheet format. It operates based on cloud functionality, providing a wide range of interactive features, automated data collection, and even the ability to pull data from third-party APIs.

If you’re accustomed to working with spreadsheets like Excel, iWork Numbers, Zoho Sheet, or Open Office Calc, you’ll find it easy to use Google Sheets. However, if you’re new to Sheets or exploring the Spreadsheet program, here are 8 useful functions for your data.

1. COUNTIF and SUMIF in Google Sheets

SUMIF and COUNTIF are easier than search functions. If the logical criterion in COUNTIF or SUMIF is true, Google Sheets can count the number of occurrences or sum the corresponding values.

Take a look at the example below, where you can count the number of apples sold using the following formula:

=COUNTIF(B2:B10, "Apple")

This formula states: Count the number of cells with the word “Apple” in the range from B2 to B10.

You can calculate the total weight of apples sold using the SumIF function.

=SUMIF(B2:B10,"Apple",C2:C10)

It searches for the word “Apple” entered in column B and then calculates the sum of the corresponding values in column C.

2. VLOOKUP and HLOOKUP

The examples below will demonstrate the effectiveness of Google Sheets’ lookup functions. They allow you to search for a string of text and then retrieve the corresponding value from a column or row. This is particularly useful when you have different datasets with the same entities in your spreadsheet, such as product listings, personnel data, or projects.

In the following example, let’s imagine you want to track the changes in the quantity of apples, oranges, and pears for the months of January and February. The order of the fruits available changes each month, so simple subtraction formulas cannot be used.

Instead, the VLOOKUP function searches the data table vertically until it finds a match for the search term, and then retrieves the corresponding value horizontally in the adjacent column. VLOOKUP stands for Vertical Lookup because it searches vertically and retrieves values horizontally. On the other hand, HLOOKUP stands for Horizontal Lookup because it searches horizontally and retrieves values vertically.

First, let’s use VLOOKUP with the table and the following formula:

=VLOOKUP(F2,$A$2:$B$6,2,false)

Within this, “F2” is the value we’re looking for, which is “Apple” in cell F2. Since we’re using VLOOKUP, “$A$2:$B$6” in the formula instructs Google Sheets to search vertically within the data table for January. “2” indicates that we’re looking for “Apple” in the second column. “False” assumes that if an exact match is not found, it will return an approximate match. Ultimately, the formula searches for “Apple” in the January data table and retrieves the value 1003 in the second column.

To calculate the change, we need to subtract the lookup function for February from the lookup function for January. Therefore, the formula would be:

=VLOOKUP(F2,$C$2:$D$6,2,false)-VLOOKUP(F2,$A$2:$B$6,2,false)

The second lookup operation is similar to the one above, but now we input the February data and subtract it from the January data. So, the formula for the lookup function in February is similar to that of January, but we replace “$A$2:$B$6” with “$C$2:$D$6”. Now it will subtract the quantity of apples in February (785) from the quantity in January (785) and retrieve the value -218.

Similarly, HLOOKUP represents an equivalent function, but it reads horizontally and searches vertically.

3. IMPORTRANGE

IMPORTRANGE is a useful function when you need to retrieve data from different sheets within Google Sheets. Instead of copying data from one sheet and pasting it into another, you can use this formula to save time.

If someone other than you owns the spreadsheet, you need access to the spreadsheet before you can start using the IMPORTRANGE function.

During the process of pulling data from different sheets, any changes made to the source sheet will automatically be reflected in the destination sheet.

Therefore, here is the necessary formula if you are creating reports or summaries by sourcing data from different members within your project team.

In the video above, you can see how easily data from the U.S. Census Bureau can be imported from one sheet to another. You simply need to reference the URL of the spreadsheet, the sheet name, and the range of data you want to import.

This formula does not import any visual formatting from the source. If you want to try it out, you can use the following formula syntax:

=IMPORTRANGE("URL","Sheet1!B3:R11")

4. IFERROR

Your spreadsheet can become messy if there are too many errors. Errors often occur when you apply multiple formulas across columns and sheets but don’t have enough data to return any values.

If you share such files with team members or clients, they may not be satisfied. Moreover, you will also struggle to avoid making mistakes when completing tasks. The IFERROR function can come to your rescue.

Place your formula inside the IFERROR function and specify the text that will be displayed if there is an error. The video above demonstrates the use of IFERROR in situations where you manage a spreadsheet for product pricing or student grading.

Here are some formula syntaxes that can be helpful if you want to try them out yourself:

=iferror(D4/C4, 0)
=iferror(VLOOKUP(A23,$A$13:$G$18,7,false),"ID Mismatch")

5. ARRAYFORMULA

This function helps you save time when editing formulas in your spreadsheet. When you need to apply functions to thousands of rows and columns in a sheet, you should use ARRAYFORMULA instead of non-array functions.

Non-array functions are those that you create in one cell and then copy-paste them into other cells in the sheet. Google Sheets is smart enough to adjust the formula based on the cell address. However, doing so can lead to the following issues:

  • The spreadsheet becomes slow due to individual functions in thousands of cells.
  • Making any changes to the formula requires hours of editing.
  • Copying and pasting non-array formulas into specified cells is a tedious process.

Let’s assume that you are responsible for creating a spreadsheet document with student names, subject grades, and total scores. Now, if you not only have to calculate grades for a few students in a class but for all students across the entire city, a formula based on the “+” operator would be time-consuming. You can use ARRAYFORMULA as mentioned below.

=ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F)

In this formula, the range B2:B is specified as infinite. You can make it finite by adjusting the range to B2:B10, and so on.

6. FILTER

Although you can easily filter and sort data by going to Data > Create a Filter, it would be more convenient to use the FILTER function to obtain the desired results. The parameters are straightforward:

=FILTER(range, condition1, other conditions)

The “other conditions” section is optional. Essentially, these are true/false comparisons of other cells to further filter your results.

  1. In an empty cell, start your formula. Ideally, you would have created column headers that match the data you are filtering, and then start the formula in the first cell below the header of your first column. For example, the article is filtering employee IDs with salaries greater than $120,000.
  1. Enter your range and the first condition. You can close the formula here or enter additional conditions separated by commas. In the example, the condition is to select only values in the range F2:F14 that are over $120,000.
  1. Your results will appear below the new column header (if you have one).

The great thing about this formula is that if you make any changes to your data, the results will automatically update. Adding a new row within the original range will be included automatically. This is a much more dynamic option compared to filters in the Google Sheets menu.

7. JOIN

The JOIN function is used to concatenate a series of values into a text string, making it convenient for usage. It can be used to simply combine a few key values or some HTML.

Type “&” to connect the values of different cells and use double quotation marks with any text you want to insert.

For example, we use the following calculation:

=A1 & " " & B1 & " and " & A2 & " " & B2

and the result is “4 Apples and 5 Pears”.

Using the JOIN function is most suitable when combining multiple values. You simply specify the character you want to insert between the values and the cell values you want to join.

For example:

=JOIN(",",A1:A5)

We have the following:

1,2,3,4,5

8. SPLIT

Do you encounter the opposite problem and need to separate items within a cell? Just use the SPLIT function. It is the reverse of the JOIN function. For example, you may want to separate first names and last names to easily sort data alphabetically by last name.

For SPLIT, the parameters are:

SPLIT(text, separator, [split_by_each], [remove_empty_text])

In simpler terms, “text” refers to the cell you want to split, “delimiter” is the character used to specify the text separation position, and the last two parameters are optional. “split_by_each” refers to whether you want to split at every matching character. “remove_empty_text” removes empty text from your result, and it is set to TRUE by default.

You will need two or more empty cells, one cell for each part of the text to be divided. For example, if you are splitting a full name into first and last names, you only need two empty cells.

  1. In your first empty cell, start your formula with =SPLIT(
  2. Enter the cell you want to split.
  3. Enter the delimiter you want to use. In this example, it is a space, so ” ” will be used, but it can be any character, such as a letter or symbol.
  4. Close your formula and fill in multiple cells in the same row.

You can apply the mentioned Google Sheets functions in various different scenarios. Make sure to explore and find new situations to apply these formulas. They will save you time and help you interpret data in an understandable way.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top