8 Best Google Sheets Formulas to Boost Productivity

Google Sheets has roughly 500+ formulas, but which ones should you learn to boost productivity for your everyday tasks? The whole collection of formulas range from Engineering, Database, Math, Statistical, and many more important categories.

We’ve gathered a list of the top 8 formulas you need to master if you want to speed up things at work using Google Sheets. These are formulas which you’ll use in almost every field and therefore it’s important to know how they work. You can take this knowledge into any job and benefit from it.

If you’re unsure about how to create a formula, you should first check our guide on creating formulas in Google Sheets.

VLOOKUP

Vlookup is short for vertical lookup and it does exactly that. This function helps you look through rows vertically with a specific value and return a value from another column in the same row.

This formula automates the process of you having to manually find the correct row and extracting the value in the right cell.

Its formula is =VLOOKUP(search_key, range, index, [is_sorted]).

• The search_key is the value you want to search for.
• Range is the rows and columns you want to search the key in.
• Index refers to the specific column you want to extract from that row.
• The is_sorted field is optional and we’ll discuss that in our detailed VLOOKUP guide. It’s recommended to keep it FALSE in most cases.

Let’s look at a practical example of VLOOKUP now.

In cell E1, we’ve written our VLOOKUP formula to search for cell A4 and return the second column. Can you guess what the answer will be?

It’s Vasquez. Feel free to try different rows and change the index to see how the result changes.

IF

The Google Sheets IF statement is used to perform actions based on a specific condition. That condition can be anything you want.

We can specify the output of the statement depending on if the condition is true or false.

Here’s the formula: IF(logical_expression, value_if_true, value_if_false)

• Logical_expression is the condition we want to test the cell on
• Value_if_true is the answer we’ll get if the condition is true.
• Value_if_false is what we’ll see if the condition is false.

Let’s look at an example.

In cell D1, we’ve written our condition. It says to check the contents of cell A2 and match them with the string ‘Aroush’. If they match, ‘First name is Aroush’ should be printed. If they don’t, print ‘First name is not Aroush’ instead.

And here’s the result:

We encourage you to try different combinations and see for yourself how the results change.

COUNTIF

This Google Sheets formula is pretty self-explanatory. It counts through cells based on a specific condition. The condition checks whether the contents of the cell are equal to it. If they are, the count increases. Otherwise, the next cell is checked.

The conditions can be to check for a specific number (50), string of characters (“Fruit”), a cell reference (B10), or an expression (“<=45”).

The formula for COUNTIF is =COUNTIF(range, criterion)

• Range specifies which rows we want to look in.
• Criterion is the condition on which we want to count them.

Here’s an example of it in action. We’ve added an extra column of ‘City’ for this example and we want to count how many people are living in which city.

Under ‘Los Angeles count’, we type in the formula =COUNTIF(C2:C11, “Los Angeles”) and here’s the result:

Why don’t you try and write formulas for the other two columns now?

ARRAYFORMULA

When working with spreadsheets, it’s common to perform one task on a number of rows. That’s where ARRAYFORMULA comes into play. It lets you apply one formula to a number of rows while typing it only once.

One might argue that the same result can be achieved by typing the formula in one cell and copying it to others. The problem with this approach is that it will unnecessarily add more formulas for the spreadsheet to process. This can slow down the performance of your spreadsheets.

Another problem with copying the formula is if a change is made. To make the changes reflect across every row, you’ll have to manually make changes everywhere. When you use ARRAYFORMULA, you only need to make the change once.

ARRAYFORMULA takes the formula you want to apply to other rows as an argument.

The syntax: =ARRAYFORMULA(formula)

We’ll make it more clear with the help of an example.

We want to know which people live in the city of Chicago and which don’t. In a separate column, we type =ARRAYFORMULA(IF(C2:C11=”Chicago”,”Yes”,”No”))

Upon pressing enter, the column is filled with either Yes or No.

There’s a lot more to ARRAYFORMULA and we’re going to cover it in detail very soon!

LEN

Len is yet another simple but extremely powerful formula. It’s used to count the number of characters in a cell or string. LEN counts all the characters in a string including spaces and special characters.

Syntax: =LEN(“Sample Text”)

Above, we’ve given the formula a standard string. We can also reference a cell like this: =LEN(A1)

In the second example, the contents of cell A1 will be read and the LEN function will count the characters in the cell and return the count.

Let’s look at a practical example:

We want to find the length of the first name of the people in our spreadsheet. Here’s how:

And here’s the answer:

As an exercise, try using ARRAYFORMULA to extend this formula to the rest of the rows!

IMPORTRANGE

When you’re working with spreadsheets, you’ll probably have to go back and forth between a bunch of them. This can be time consuming and difficult. For this, IMPORTRANGE exists to make our lives easier.

With IMPORTRANGE, we can easily extract information from a completely different spreadsheet and add it a different spreadsheet.

As usual, here’s the syntax: =IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url is a placeholder for the URL of the sheet we want to extract data from.
• range_string represents the range of cells we want to extract.

Create a new spreadsheet and make a column of 10 entries of anything. We chose to add random fax numbers.

In the original spreadsheet, type the IMPORTRANGE formula in a separate column and copy the full URL of the new spreadsheet as the first argument in double-quotes. Then, select the sheet and range from which you want to extract information. In short, it will look like the text below.

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1VfIzxRtWfglsgIbUTQvU0uuqW1xfPH-yu0tlJIkbB7Y/edit#gid=0”, “sheet1!A1:A11”)

Note: the URL for your sheet will obviously be different, so copy it directly from your browser.

At first, you will receive an error telling you to allow access to the sheet. Press the ‘Allow access’ button and voila!

SPLIT

The SPLIT function is used to extract characters from a string based on specific characters or in this case, delimiters.

This is very useful in a number of scenarios where we want to divide the contents of a cell into different columns. For example, separating the first and last name into different columns. We can also divide phone numbers by region using this method.

As usual, we’ll look at the syntax first: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])

• text is the string we want to split
• delimiter is the character or set of characters we want to split the string on

The optional arguments ([split_by_each] & [remove_empty_text]) are a bit complex and we’ll cover them in a separate article. For now, you can simply ignore them.

In this example, we’re going to split the numbers based on the hyphen. Here’s how:

And here’s our answer:

Try making the split across every row using the ARRAYFORMULA function now.

SUMIF

This function quickly gives you a sum of a range based on a condition you choose. It works just like the IF and COUNTIF functions but provides a sum instead.

SUMIF is useful when you want to sum values but test them on some condition first.

Here’s how it works: =SUMIF(range, criterion, [sum_range])

• range is again, the cells whose values we want to sum
• criterion is the condition we want to test the cells on

sum_range is an optional argument that we’ll cover in a dedicated article for SUMIF.

We’ll now add a monthly income column to the mix and get the sum of salaries that are greater than $5000.

Here’s the answer:

Closing words

We hope you followed our examples and learned something new today. While these examples were very basic, they should be enough to encourage you to learn more.

If you liked these, you should also check out our other guides on creating checkboxes and dropdown menus in Google Sheets!

Do you have any recommendations on what topic we should cover next?