7 Most Useful Formulas In Google Spreadsheets  

Google Spreadsheets goes beyond just making spreadsheets per se. There are many cool things that you can do with it, whether as an expert or as a newbie. It may be relatively new compared to its counterparts, but it doesn’t fall far behind. In fact, it performs just as well, too. The best part is that for you to get the hang of it, you don’t even have to be an expert. Using the right formulas, you can create insightful data for your business to help you make more meaningful decisions. These formulas can help make life easier for you by cutting down hours off the workload that you need to do.

Google Docs, including that of Spreadsheets, can now be used even on mobile phones. This advantage is one that you should highly take advantage of, by also learning to use the right formula. That way, even when you’re on the go, it becomes easier for you to manipulate data in your sheets and improve your productivity.

If you’re looking to learn more about using the right formulas on Google Spreadsheets, read through below. This Google sheets formula cheat sheet can help you improve your efficiency and productivity.

1. Vertical Look Up Formula

The vertical lookup formula searches for a value in the left-most column of the array, then returns another value in the same row based on the index.

The general formula is as follows:

=vlookup(search_criterion, array, index, sort_order)

For example, you’re looking to find the value of the population in cell D2, from all the values contained in the range A2:C2, copy-paste the formula into the cell E2. The formula will be as follows:

=vlookup(D2;A2:C2;2; false)

2. COUNTIF Conditional Counting Formula

This formula comes in handy when you have to count how many cell values there are, using a certain predetermined criteria. While counting manually can be a good option, this is impossible to do and time-consuming if there are hundreds of cell values that you have to compute. With manual counting also, it’s easy for you to make mistakes, such as skipping cells.

With the COUNTIF formula, you need to specify the range and the criteria that you want to search.

The COUNTIF formula is as follows:

=COUNTIF(range,criterion)

For example, you want to know how many people in the list of contacts in your sheet have supervisor as their job title. Assuming that in your spreadsheet, you’ve put the job titles in column C and  there are 600 rows under job titles. In this case, your formula is going to look something like this:

=COUNTIF(C2:C600, “supervisor”).

3. Filter Formula

As its name suggests, the filter formula is used when you need to filter information in a given array. This is useful because when you do your reports, the new sheet should already show the filtered version. The general formula for applying filters is:

=filter(range; condition1, condition2,…)

For another example, if you wish to filter certain values under Column B with rows 2 to 24 that are greater than 50, the formula will be:

=filter(B2:B24; B2:B24>50)

4. Array Formula

The array formula is used when you want to perform an operation on a whole array, rather than on single values.

This is an example of an array formula:

{=SUM(A1:A4-B1:B4)}

In using this formula, ignore the curly brackets and press ctrl + shift + enter after typing it. Without pressing the string ctrl + shift + enter, the value will result in an error. This is because the subtraction sign on the formula cannot be used on arrays (A1:A4 and B1:B4). For the subtraction sign to work on arrays, you need to turn it into an array formula using ctrl + shift+ enter.

This formula is generally used in Excel. But, the same principle applies with Google Sheets. Instead of the curly brackets, the right and more accurate formula is:

“=ArrayFormula(concat(J6:J,K6:K))”

Take note that you can also use ctrl+shift+enter to have your formula inside ArrayFormula().

For example, you wish to subtract cells in one column from other cells, and then add all of the total. Rather than using two different formulas, an array formula can be used. In this case, the formula is going to look something like this:

=ARRAYFORMULA(ADD(B2:B10-C2:C10)

5. Concatenate Formula

This formula concatenates multiple text strings, in different cells, all into one string. To concatenate means to link together in a chain or series.

For example, if you wish to concatenate all the information that are contained in cells B3, B4, and B5 into one string, your formula will look like this:

=concatenate(B3, B4, B5)

To make this formula work, copy and paste it in the cell that you wish to put the information on, such for instance, cell D2.

6. Import Range Formula

When you’re running a business, the tendency often is to have many different spreadsheets for different purposes. There may be instances when you wish to import information from one spreadsheet to another. In this case, the IMPORT RANGE function comes in handy. The general formula will be:

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

In this formula, the “spreadsheet_url” is where you’re going to copy and paste the spreadsheet’s key.

“range_string,” on the other hand, should first define the name of the sheet to be imported from, such as Sheet 2, for instance. Then, after naming the sheet, define the range of the data that needs to be imported.

With this formula, it’s easier to transport data from one sheet to another. You can quickly add the data that you want to be moved from the other spreadsheet, without the need to copy and paste.

7. FIND Formula

The FIND function is what you’re going to use when you’re looking for a string of text within another string, and then, later on, convert this data to an integer number. For this, the formula is as follows:

=find(find_text, text, position)

For example, in a particular problem, you need to find “space” within the string contained in cell D2, which contains, for instance, the text Thaddeus Ong. Then, copy and paste this formula in cell E2. In this problem, the formula will be:

=find(”     “,D2, 1)

In this case, the formula in E2 returns the value “9”. This means that this space is found within the source string after 9 characters.

Conclusion

With all these formulas, you can now elevate the use of Google Sheets to do a wide variety of things. A little bit of mastery with these examples, and you can go far beyond just simple arithmetic. You can perform even the most complicated equations with ease. Now that you’ve got this list, all that’s left for you to do is to start practicing and enjoy creating excellent sheets for reporting.