Advertisement

How To

10 essential formulas for Google Sheets

10 essential formulas for Google Sheets
Jeff Weishaupt

Jeff Weishaupt

  • Updated:

Google Sheets works pretty similar to Microsoft Excel, except it’s entirely cloud-based. This living document aspect adds convenience. However, you can make Google Sheets even better with formulas that automate otherwise time-consuming tasks.

Google Sheets DOWNLOAD

The Google Sheets app formulas may be slightly different from that of Excel. Knowing these formulas and shortcuts can make your data entry much more efficient. Plus, some of these formulas can also help you analyze your data. So whether you’re making spreadsheets for business or a class project, the following 10 Google Sheet formulas will come in handy.

COUNTIF() to count cells

This formula counts the number of cells if they meet certain criteria. With this shortcut, there’s no more need to count the cells manually: =COUNTIF(range, criteria)

For example, you’re looking at the sales data for the sales team for a month, and you want to know which salespeople exceeded the minimum threshold of sales, which is $500. You can add the range of the cells in the first part of the formula where the sales data is. In the criteria section, you can define an amount greater than 500. Here’s what it will look like:
=COUNTIF(F1:F10,”>500″)

COUNTIF formula

Text() to change numbers

You can use this formula to reformat numbers into a currency or decimal format: =TEXT(cell,” Format”)

For example, let’s say you have a column with dollar prices of products. You can apply the formula to the first cell and then drag it down to apply to all the other cells in the row. If the first cell is C2, the formula will look like this:
=TEXT(C2,” $0.00″)

TEXT formula

SPLIT() to divide data from cells

This formula can help divide data from a single cell in Google Sheets into multiple cells in subsequent rows. The formula is: =SPLIT(Text, Delimiter)

Let’s say you have customers’ full names in one column. And you want to divide that into first and last names. Suppose the first cell is A2. You’ll go to the next cell B2, and enter the formula =SPLIT(A2,””). This will enter the first and last names in cells B2 and C2. You can simply drag the B2 cell down to the last name.

SPLIT formula

TODAY() for current date

With this formula, you’ll automatically get today’s date without having to type anything extra. Simply type in the cell: =TODAY()

You can also use it to create date ranges. For example, =TODAY() – 2 for two days before the current date.

TODAY formula

CONCATENATE() to append cells

If you want to append data from multiple cells, this formula makes the process super easy: =CONCATENATE(string1, string2, …)

For example, you have customers’ first and last names in separate rows. So the first data row is 2, the first name is in B2, and the last name is in C2. Here’s how you can append them and get the full name in adjacent cell D2: =CONCATENATE(B2,” “,C2)Then simply drag the cell D2 to apply the formula to all the other first and last names in the sheet.

CONCATENATE formula

SEARCH() to check the value in a string

Use the search formula to help find a string in a text. This comes in handy when you’re working with spreadsheets that have a lot of text. The formula is:=SEARCH(substring, string, or text, starting at)

Here, the substring is the text you’re looking for, the string or text is the text you want to check for in the substring, and the starting at is the position where it will start, which is 1 by default.

SEARCH formula

SUM() to get total

SUM is probably the most frequently used formula in Google Sheets. Here’s the formula: =SUM(range)

Simply type in the range of cells you want the sum for and apply the formula in the cell where you want the total. Of course, this formula applies to numeric values only.

For example, let’s say you have sales numbers for the month from B2 to B11. Here’s what the formula would look like:
=SUM(B2:B11)

SUM formula

AVERAGE() to Get Average

This formula will give you the average value of a set of numeric data: =AVERAGE(range)

If you wanted the average sales number for the month, here’s what the formula would look like: =AVERAGE(B2:B11)

You can also add multiple ranges. For example: =AVERAGE(B2:B11, C2:C11)

AVERAGE formula

SUBSTITUTE() to replace text

You can use this formula to replace text with another text. Here’s the formula syntax: =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence number])

In the formula, the text_to_search is the text within which to search, search_for is the text to replace, replace_with is the replacement text, and [occurrence number] is optional if there’s a certain number of times the text should be replaced. Otherwise, all the occurrences will be replaced.

SUBSTITUTE formula

Sort() to sort the rows in one column

If you want to sort the rows of a column in ascending or descending order, use this formula: =SORT(range, sort_column, is_ascending)

Here the range represents the cell or row range, sort_column specifies the column, and is_ascending is either TRUE or FALSE, specifying whether the order is ascending or not.

SORT formula

Google Sheets makes entering, managing, and analyzing data incredibly easy. More importantly, you can access it from anywhere and backup all your spreadsheets in Google Drive. If you want to make the app even better, check out these 10 essential add-ons for Google Sheets.

Jeff Weishaupt

Jeff Weishaupt

Latest from Jeff Weishaupt

Editorial Guidelines