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 Google Sheets 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 20 most important and Google Spreadsheet functions to make your life easy! After reading this blog, you’ll be ready to implement these formulae right away!
Here are the most useful and must know Google spreadsheets formulas
Table of Contents
- 1. COUNTIF() for Conditional Counting
- 2. TODAY() to update current date
- 3. TEXT() to convert numbers into currency
- 4. SPLIT() to divide data into multiple cells
- 5. SEARCH() Function to Check Value in a String
- 6. CONCATENATE() to Append Multiple Cells
- 7. VLOOKUP() for Vertical Lookup
- 8. IF() to Check Condition of a Logical Expression
- 9. IFERROR() to Return Cell Error Value
- 10. IMPORTRANGE() to Import a Range of Cells
- 11. IMPORTFEED() to Import an RSS or ATOM Feed
- 12. GROWTH() to predict the growing trend
- 13. SUBSTITUTE() to replace existing text
- 14. IMPORTXML() to import website data
- 15. REGEXEXTRACT() to extract substrings
- 16. TRANSPOSE() to transpose rows and columns
- 17. QUERY() to Run a Google Visualization API Query
- 18. ARRAYFORMULA() to Display of Values Returned
- 19. SUMIF() to add numbers in a range cell
- 20. SORT() to sort a range of cells
- Wrapping Up
1. COUNTIF() 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 COUNTIF Google Sheets formula would look like:
2. TODAY() to update current date
If you can make the dates in your Google Sheets update automatically, think of how much time you’d save. Auto-updating of dates on your Google sheets can benefit you in multiple ways – for instance, when you integrate your Google sheets with Google Calendar, any update made inside your sheets is automatically added to your calendar. Isn’t that amazing? All you need to do is write a simple google spreadsheet function and updates happen simultaneously in both sheets and the calendar.
Here’s the Google Sheets Formula to help you out.
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. TEXT() to convert numbers into currency
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. SPLIT() to divide data into multiple cells
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. 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. CONCATENATE() to Append Multiple Cells
Need to combine the contents of two or more cells into a third separate cell? The CONCATENATE google sheet 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. VLOOKUP() for Vertical Lookup
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.
Things to know:
- VLOOKUP only looks RIGHT. When used, this formula will work for a table that has lookup values in the left-most column. The data you are looking to retrieve using this formula can appear on any column on the right.
- VLOOKUP has two matching modes – exact and approximate. These two are controlled by the “range_lookup” argument. To get exact matching, you need to set the range_lookup to FALSE, and for an approximate matching set it to TRUE.
Note: VLOOKUP by default is TRUE. So when you are using this formula, the result is always approximate by default unless altered manually.
Apart from this, VLOOKUP() formula on Google Sheets can be used on different sheets and on wildcard characters.
Using VLOOKUP from another sheet:
Let’s say you have two Google sheets and you want to lookup matching value of one sheet in another. You can do this by using the VLOOKUP() Google sheets function based on one key column. Your formula will look like this:
=IFERROR(VLOOKUP(A2, IMPORTRANGE (“google_doc_link”,”sheet_name!cell_range”),column_number, FALSE),)
- A2 is the cell for which you want VLOOKUP formula to search matching values. I have used A2 to explain. It can be any other cell-based on your requirement.
- Sheet_name will have the name of the sheet and the following cell_range will be the cell ranges that you want to search from.
- column_number is the column number that will return your matched value.
Place this formula on a blank cell and hit Enter to get the result. This is one amazing way to use VLOOKUP between sheets.
Using VLOOKUP with * wildcard character
Imagine you have a table of team members’ information and you need to find out information based on a partial match.
You can do this using the standard VLOOKUP Google Sheets formula with the * character – also known as the wildcard asterisk character.
Let’s say you are searching for first names starting with AV in your table from the first name column. Your VLOOKUP formula will look like:
=VLOOKUP(“AVI*”,cell_range, 2, FALSE)
The * is used to pull all names starting with AVI combination.
8. IF() to Check 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. IFERROR() to Return Cell Error Value
IMPORTRANGE Google Sheets function 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 an 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. GROWTH() to predict the growing trend
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. SUBSTITUTE() to replace existing text
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. IMPORTXML() to import website data
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. REGEXEXTRACT() to extract substrings
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() to transpose rows and columns
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)
17. QUERY() to Run a Google Visualization API Query
This is a very POWERFUL formula where you can write a pseudo-SQL query and play with your data!
Here’s the formula: Query(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.
18. ARRAYFORMULA() to Display of Values Returned
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.
19. SUMIF() to add numbers in a range cell
The SUMIF() Google Sheets Formula comes handy in calculating sum with multiple parameters i.e. conditional addition.
Let’s say I have a list of items and their prices along with categories to which each item belongs to. I can have categories like stationery, miscellaneous, and instruments in column B and prices in Column C. To find the total price of all items belonging to any one category, I can use the SUMIF() function as follows:
Here B1:B17 range of cells contain all the categories and C1:C17 range of cells contain prices of all the items.
20. SORT() to sort a range of cells
If you want to sort your values, SORT() Google Sheets Formula comes handy. The syntax for this formula is:
Let’s say I have values like 10,34,55,87,90,17 in column A ranging from A:3 to A:8 cell range. To sort this range, all I need to do is use the SORT() function.
It will automatically sort the values in column A with the lowest on the top.
Note: When I add the function in A:11 cell, the sorted values are placed from A:11 to A:20 cell range. The original value order remains as it is. So I use the SORT() function in the adjacent column. This gives more clarity.
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! You can make your Google Sheets more powerful by using add-ons that not just simplifies your work but are also pretty useful. Luckily we already have a list of tried and tested google sheets add-ons that can give your spreadsheets a power-boost. Start trying these along with the Google spreadsheet functions.
If you come across any use cases wherein you used the above formulas, let us know in the comments section below! 🙂