18 Google Spreadsheet Formulas you must know!

With more and more businesses focusing on making decisions by understanding their data, the usage of Google Spreadsheets has increased enormously.

Did you get stuck formatting heavy data to make decisions? Wondering what exactly are the formulas you need to implement on this data to make it crisp and understandable?

For becoming a pro in the world of google sheets, you really need to know the backbone and the basic formulae before you jump onto advance hacks. If you’re a beginner and struggling with lumps of data, this is the right place to understand the basic functionalities of a Google Spreadsheet.

We’ve written down simple and 18 most important Google Spreadsheet formulae to make your life easy! After reading this blog, you’ll be ready to implement these formulae right away!

Most useful and must know Google spreadsheets formulas

1. COUNTIF() Function in Google Sheets for Conditional Counting

If you’ve been manually counting cells all this time, you’re doing it wrong.

There’s actually a Google Sheets formula that will count the number of cells (that meet certain criteria) for you!

Formula: =COUNTIF(range, criteria)

For example, say I’m tracking the engagement of my company’s Facebook posts on this Google Sheets, and I want to find out how many posts have engaged users more than 1.

Assuming the data is housed in cells E2 to E12, here’s what my formula would look like:

=COUNTIF(E2:E12,“>1”)

2. Update Current Date using TODAY Formula

If you can make the dates in your Google Sheets update automatically, think of how much time you’d save.

Luckily, there’s a simple Google Sheets Formula to help you out.

Formula =TODAY()

You can also use this same formula to build date ranges. Say you want to build a report consisting of data from the last 3 days.

Your end date would be =TODAY()

And your start date would be =TODAY() — 3

Pretty nifty, huh?

3. Use TEXT() to Convert Numbers into Currency Format

Using the TEXT formula, you can take any value and reformat it.

Now, there are plenty of ways in which you can use this formula. Here are some examples:

• Changing a number into a currency
• Changing a number to have more decimal places
• Changing a date string into a MM/DD format

Say you’ve got a row of numbers, and you want to convert all of them into currency.

Assuming your first cell is B3, you’d use the formula =TEXT(B3,”$0.00")

Once you get your output, drag the cell downwards to automatically apply the formula to the rest of the data.

4. Divide Data into Multiple Cells Using SPLIT()

The SPLIT formula is pretty self-explanatory; it allows you to split data from a single cell into multiple cells.

Here’s the formula: =SPLIT(Text, Delimiter)

Say you offer a lead magnet on your site, and in order to download this lead magnet, your prospect has to enter their name into your form.

Now, you need a way of splitting up your prospects’ first name and last name, before you add them to your mailing list.

First, line up all your prospects’ names in a column (let’s say your data is in cells B3 to B9).

We’ll have the two subsequent columns (C and D) house the first names and last names that we’ll get after applying the Google Sheets formula.

All you have to do is type the following formula: =SPLIT(B3,“ ”) into Cell C3, and you’ll see your prospect’s first and last names appear in cells C3 and D3.

Then drag Cell C3 downwards to populate the rest of the cells.

That’s all there is to it!

5. Google Sheets SEARCH() Function to Check Value in a String

This Google Sheets formula allows you to check whether a value exists in a string.

Say you’re doing an audit on your content strategy…

And you want to figure out if the founder interviews that you’ve been posting on your company’s blog have been performing well.

You already have a Google Sheets consisting of the 100 most popular blog articles on your company’s blog, so you want to search through this list and identify how many of your posts fall into the category of “founder interviews”.

If the vast majority of them do fall in that category, then that’s a sign that you’re on the right track. If not, then it’s time to try a different approach!

Now, here’s the formula that you’d use:

Formula: =IF(SEARCH(“/founder-interviews/”,B2),”YES”,” “)

Again, line up all the URLs of your top 100 blog articles from cell B2 onwards.

Next, go to cell C2, type the above formula in, and drag it downwards to populate the rest of the cells in the same column.

If a particular URL does contain the term “founder-interview”, then you’ll see a “YES” on the cell adjacent to it.

Now you can assess how well-performing your founder interviews are, and fine-tune your content strategy!

6. Append Multiple Cells in Google Sheet using CONCATENATE()

Need to combine the contents of two or more cells into a third separate cell? The CONCATENATE function helps you do just that.

Here’s the formula: =CONCATENATE (string1, string2, string3, …)

You can also use a variation of the same formula to combine the data in cells, AND incorporate a spacing in between the different data.

To do this, add a “ “ in between your strings.

For example, an initial =CONCATENATE (B3,C3) formula would now look like:

=CONCATENATE (B3,“ ”,C3)

7. Vertical Lookup using VLOOKUP() Function

The VLOOKUP function is pretty self-explanatory — it helps you to look up specific information located in a table or database.

Here’s the formula: =VLOOKUP(search_key, range, index, is_sorted)

First, the search_key refers to the value you want to search for.

Next, the range refers to the number of columns and rows that should be included in the search.

The index refers to the column index of the value to be returned, with the first column in the range being numbered 1. Take note that if you enter an index that isn’t between 1 and the number of columns in the range, “#VALUE!” will be returned.

Finally, is_sorted indicates whether the column to be searched (the first column of the specified range) is sorted. Google recommends that you set this to FALSE, so that an exact match (NOT the nearest match) will be returned.

8. Use IF() to Check the Condition of a Logical Expression

If you want to test whether a certain condition is true or false, then the IF function will be right up your alley.

How does the function work?

If the condition is true, the function will carry out a specific operation. If the condition is false, the function will carry out a different operation.

The formula of the function is =IF( test, then_true, otherwise_value)

First, the test refers to the expression which you want to test (to find out whether it’s true or false).

Next, then_true refers to the operation that is carried out if the test is true.

Finally, otherwise_value deals with the operation that is carried out in the other scenario — if the test is not true.

9. Return the Cell Error Value using IFERROR()

IFERROR is a handy way of dealing with cell errors.

Say you have an error in a cell such as #DIV/0! (this happens when you try and divide a number by zero). Assuming you have other cells whose formulas involve the data in this cell, this error then prevents your subsequent calculations from working.

To get around this, use the IFERROR function to replace error values with a new value that you specify.

Here’s the formula: =IFERROR(value, [value_if_error])

The first parameter, value, refers to the value to return if the value is not an error.

The second parameter, value_if_error, refers to the value the function returns if the value is an error. This is left blank by default.

10. IMPORTRANGE() to Import a Range of Cells

IMPORTRANGE is a simple yet powerful function that helps you import a range of cells from a specified spreadsheet.

Here’s the formula: =IMPORTRANGE(spreadsheet_url, range_string)

Simply key in the spreadsheet’s URL, and the string (eg A1:D7) that you’d like to import.

11. IMPORTFEED() to Import an RSS or ATOM Feed

IMPORTFEED is similar to IMPORTRANGE, but instead of importing a range of cells, it imports a RSS or ATOM feed.

Here’s the formula: =IMPORTFEED(url, [query], [headers], [num_items])

Only the first parameter (the URL of the RSS or ATOM feed, including protocol) is mandatory; the rest are optional.

12. Predict the Growth Trend using GROWTH()

Trying to predict the demand for your product?

The GROWTH function will help you fit an ideal exponential growth trend, and give you data about said growth trend.

Here’s the formula: =GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

known_data_y is the array or range containing y values that are already known. These values are used to curve fit an ideal exponential growth curve.

known_data_x are the values of the independent variables that correspond with known_data_y.

new_data_x are the data points to return the y values for on the ideal curve fit.

Finally, b is set to be TRUE or FALSE; if true, the function will calculate b given a general exponential form of y = b*m^x, and if false, the function will force b to be 1 and calculate the m values.

13. Replace the Existing Text with New Text using SUBSTITUTE()

This is the best formula that’ll come handy if you wish to change an existing text with new text in a particular string.

Here’s the formula: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Let’s take an example: If you wish to change all the “http” to “https” for all the URLs mentioned in your Google Spreadsheet, you need to use the — SUBSTITUTE function.

14. Import Data from a Website Using IMPORTXML()

Wish to import data from a particular webpage data onto your Google Spreadsheet?

A copy-paste job can take you forever to do this! Instead, use the formula ImportXML to pull information from any of the XML fields of the webpage.

Here’s the formula: IMPORTXML(url, xpath_query)

Example: If you wish to pull out all the links on this URL — https://automate.io/, from it’s structured data, use this formula like — =IMPORTXML(“https://automate.io/", “//a/@href”).
Below image depicts the result of this formula.

15. Extract Substrings using REGEXEXTRACT()

Ever get stuck extracting specific terms from a set of data on your google sheet? Regexextract is the formula to be used! If you have many words to look up from a huge set of data, you definitely can’t go around applying filters everywhere! None of us will have the time to do the whole boring stuff. That’s where these formulas come to our rescue.

Here’s the formula: REGEXEXTRACT(text, regular_expression)

In this formula, the ‘text’ is the input text and ‘regular_expression’ is the first part of the text that matched this expression (the value which will be returned).

16. Transpose the Rows and Columns Using TRANSPOSE()

You might receive raw data from vendors, customers, etc. and you wish to format it the way you want to see it. If there is a huge set of data arranged row-wise and you’d want to see it column-wise, it’s a hassle copying and pasting.

This formula, TRANSPOSE, let’s you convert row-wise data into column-wise and vice versa.

Here’s the formula: TRANSPOSE(array)

Now let’s apply this formula to a sample data as shown in the image below:

So, your formula would look like: TRANSPOSE(B2: D7)

Apply this on the cell where you want to paste your transposed data.

Looks easy and simple right?

17. Use QUERY() to Run a Google Visualization API Query

This is a very POWERFUL formula where you can write pseudo-SQL query and play with your data!

Here’s the formula:Qu ery(data, query)

‘data’ : It is the reference to the range of cells you want to query upon
‘query’ : It is the text using which the QUERY formula churns out the information we are looking for from the data set

Let’s see how you can use the Search Query. Below, we’ll consider a set of data taken from an AdWords Account and our aim is to fetch those campaigns whose cost is > $5.

Let’s now run the formula and see results like shown below:

18. Use ARRAYFORMULA() to Enable the Display of Values Returned from an Array Formula

Using a lot of formulas on your huge set of data and bloating up your spreadsheet? Use ARRAYFORMULA to perform multiple actions on one or more datasets. The ARRAYFORMULA doesn’t have to replicate across multiple data sets. It’s dynamic and you can change it just at one place in-case you want to edit the formula across all the data sets.

Here’s the formula: ARRAYFORMULA(array_formula)

array_formula: A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell

Let’s understand this with an example.

Suppose you have a data set for an Adwords account wherein data for each keyword across two months (separately) is being displayed.

If you wish to calculate the spend for each keyword for two months, you needn’t calculate it separately in columns. Just use ARRAYFORMULA as shown below:

Wrapping Up

So, those were the best and the most simple Google Spreadsheet formulae you’re supposed to know if you’ve just started working on a spreadsheet!

If you come across any use cases wherein you used the above formulas, let us know in the comments section below! :)