The best part of Google sheets formulas definitely means to learn and try out new things that make life easy and better. Google sheet is a suitable platform with plenty of functions in it. Google sheet is an online spreadsheet that leads to creating and formatting of the spreadsheet and simultaneously work with other people. Nowadays business-related decisions are taken using the data created in Google spreadsheets.
Summary
Here, I have introduced you with the top 15 Google sheets Formulas that are used on a regular basis. These Google Sheets formulas can prove to be a backbone while preparing your data set. Whether you are a beginner or advanced users these formulas are a must to make your life easy!
Google sheets formulas are also used to get a variety of things. With Google sheet formula you can manage inventory, keep a track or list of contacts and much more. RANDBETWEEN Function – Google Sheets Formulas
There are two Google Sheets formulas that will help you to generate a random number. Those two Google sheets formulas are RAND() and RANDBETWEEN(). Here, we will see how to use the Google sheet Randbetween formula.
Google sheets Randbetween formula takes arguments in it. It means you can define high and low values in the formula. Randbetween returns a uniform random integer between the two values. The purpose of this function is to help you out in getting the random data in your Google sheet. The Syntax is=RANDBETWEEN(LOW, HIGH)
Here the parameters of RANDBETWEEN are:
- LOW or BOTTOM: indicates the start range
- HIGH or TOP: indicates the end range.
Example of Randbetween Formula:
Example 1: If I want to get a random number between one(1) and Ten(10) I would use the formula as RANDBETWEEN (1, 10)
The result will be a random number between these two numbers. Here in the image, you can see how the RANDBETWEEN formula is used. And also the result is 2.
Example 2: If I want to get a random number between minus one(-1) and One(1) I would use the formulas as RANDBETWEEN(-1, 1)
The result will be a random number between these two numbers. Here in the image, the result is -1
How to use the Google Sheets RANDBETWEEN Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Randbetween formula with the parameters
Step 3: Press Enter.
Note: The Randbetween number returned will keep on changing.
GOOGLE TRANSLATE Function – Google Sheets Formulas
Google Translate in Google sheets is very helpful when you’re learning a new language and you want to make some basic translation. From the various available function in Google sheet, Google Translate function is one of them. There are two Google sheets formulas to detect the language of the text. One is DETECTLANGUAGE() formula and the other is GOOGLETRANSLATE() formula.
The purpose of Google Translate in Google sheets is to help you convert individual cells from one language to another language. The text in the cell can be a single word or even a phrase. With Google Translate function, you no longer need to switch the tabs. This function is best when we work with multi-national companies all over the world. The Syntax is=GOOGLETRANSLATE(TEXT, [SOURCE_LANGUAGE, TARGET_LANGUAGE]) Here the parameters of Google Translate are:
- TEXT: The text or the cell range we want to translate
- SOURCE_LANGUAGE: It is Auto by default. This two-letter is the language code of the text you want to translate.
- TARGET_LANGUAGE: Here you need to enter the two-letter language code. This two-letter language code will be the code in which you want to translate.
Example of Google Translate Formula:
Example 1: In the example below, there is a phrase written in English in Column A. I wish to convert the phrase into the Spanish language.
Here “en” and “es” are the language code (List of all the language codes) Hence the result is written in front of you
Example 2: In the above example, we saw how a phrase is converted from English to Spanish. In this example, we will convert a few words in Chinese to English.
The Chinese words are converted into English
How to use the GoogleTranslate Formula in Google Sheet
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Google Translate formula and fill in the parameters
Step 3: Press Enter.
Tip of the day:
Google Sheets are one of the alternative software for Excel. Check out for some more Excel Alternatives here.
GOOGLE FINANCE Function – Google Sheets Formulas
Did you know that with Google Sheets Google Finance formula you will be able to track real-time stock information? Google Finance will let you create your portfolio, you can get BSE, NSE real-time stock prices and many more. Google Sheets Google Finance function will be easy for those who have already used some other trading platforms in the past.
Looking for some Google Sheets formulas to help you with the technical analysis? Google sheets Google Finance formula is one such to help you out. Google Finance fetches the current data or the historical data into the Google sheets. The main purpose of the Google sheets formulas Google Finance is to make a comparative stock chart in Google Sheets.The Syntax is=GOOGLEFINANCE(TICKER, [ATTRIBUTE], [START_DATE], [END_DATE/NUM_DAYS], [INTERVAL]) Here the parameters of Google Finance are:
- TICKER: This is the most essential parameter in Google sheets Google Finance. It is used to track security-related info.
- ATTRIBUTE: This parameter is optional. It is the price by default.
- START_DATE: The start date while fetching the historical data. This parameter is optional.
- END_DATE: The end date while fetching the historical data. It is an optional parameter.
- INTERVAL: The Interval parameter is optional. The interval is the frequency at which you want your data. I.e. weekly, daily, etc.
Example of Google Finance Formula:
Example 1: To know the active or the current price of the share you need to follow these steps. Type the name of the share in the Ticker parameter. Here we wanted to know the current share price of Apple.
So we enter the GOOGLE FINANCE formulas as seen in the formula bar. On pressing the enter key you will see the current price of the Apple share. Here the answer is 207.74
Example 2: Let’s take another example of Apple. Here we want to know the close price of Apple shares for the month of July.
Enter the GOOGLE FINANCE formulas as stated in the formula bar. Where APPL is the Ticker Parameter, Price is the Attribute. Complete the formula with the start date and the end date. On pressing Enter, We will get a list of Close price of Apple Share of July Month.
How to use the GoogleFinance Formula in Google Sheet
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Google Finance formula and fill in the parameters
Step 3: Press Enter.
Note: This function doesn’t update on its own. You need to run a script to ensure your data is kept up to date.
CONCATENATE Function – Google Sheets Formulas
Do you need to combine the content of two or more cells? Well, the Google Sheets CONCATENATE Function is to the rescue. The CONCATENATE Google sheets formulas allow you to combine data in a single cell. There are four Google sheets formulas through which you can combine text in a single cell. The four formulas are JOIN(), TEXTJOIN(), AMPERSAND(), and CONCATENATE() Function. The Syntax is=CONCATENATE(STRING1, [STRING2, ..]) Here the parameters of the Concatenate function are:
- STRING1: The initial string. It is a compulsory parameter.
- STRING2: Add strings in sequence. This parameter can be optional.
Example of Concatenate Formula:
Example 1: In order to Enter the first name and the Surname in a single cell, use this Google sheets formulas CONCATENATE. As seen in the example below.
Enter the CONCATENATE formula as seen in the formula bar. Since we need to add space between the name and the surname we need to enter the space in double quotation mark. Press Enter. You will see the Full Names in a single cell.
How to use the Google Sheets Concatenate Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Concatenate formula and fill in the parameters
Step 3: Press Enter.
Note: To enter space or dot or anything in between the words, we must enter in the double quotation marks.
Tip of the day:
On having seen some of the Google sheets formulas, let’s take a look at the Google Sheets Shortcuts.
TEXT Function – Google Sheets Formulas
Text function in Google sheets takes any value and reformats it. This function is used to avoid the stress of manually formatting the numbers or dates. The Google Sheets formulas that can change a number into currency, change a date string into an MM/DD format, and many more can be done through the TEXT formula. The Syntax is=TEXT(NUMBER, FORMAT) Here the parameters of Text Function are:
- NUMBER: The number, date, time, or the cell to format.
- FORMAT: The pattern in which you want to format the number, date or time or the specified cell.
Example of Text Formula:
Example 1: Here I have taken an example to convert a Date into the Day, Month and Year using the google sheets formula TEXT.
With the help of the TEXT formula, we will find the date of the month from the specified date. On pressing the enter key the results are in front of you. The Result will be 29.
How to use the Google Sheets Text Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Text formula and fill in the parameters
Step 3: Press Enter.
Note:
The format argument in the Google Sheets Text function does not contain an asterisk. (*)
COUNTIF Function – Google Sheets Formulas
The COUNTIF in Google Sheets tells you how many cells in the given range have met your specified or required criteria. COUNTIF in Google sheet is a Google sheet formula that counts the number of cells for you. You do not have to manually count the cell. The Syntax is=COUNTIF(RANGE, CRITERION) Here the parameters of COUNTIF Function are:
- RANGE: The range in which you want to search.
- CRITERION: The criterion by which you want to search.
Example of CountIF Formula:
Example 1: Below is the list of the winner of the FIFA World Cup from 1930-2018. With the help of the COUNTIF in Google Sheets, we will count how many times Brazil, Germany, Italy, and England have won the cup.
We will use the COUNTIF formula as =COUNTIF($D$1:$D$22, A3). Select the range in which we are looking and select what we want to count. These are the two parameters of the COUNTIF function. Press Enter and you will get the answer.
You do not have to manually count the time Brazil, Germany, Italy, and England have won the cup. This might lead to error as well as time-consuming.
This is how the COUNTIF function in Google Sheets can help you in making your working with Google Sheets easier.
How to use the Google Sheets CountIF Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the CountIF formula and fill in the parameters
Step 3: Press Enter.
Tip of the day:
You will not only learn the Google sheets formulas here but also Learn Everything about Google sheet.
ARRAY FORMULA Function – Google Sheets Formulas
ARRAY FORMULA in Google Sheets is used to form an array from your original formula. While working with an array formula, you need to apply the formula once and then you can apply it in the entire row. ARRAY FORMULA is used when we want to run the same formula across multiple cells.
There is no doubt that ARRAY FORMULA is difficult to understand but incredibly useful in tricky situations. The Syntax is=ARRAYFORMULA(ARRAY_FORMULA) Here the parameters of ArrayFormula are:
- ARRAY_FORMULA: This parameter can either be a range, a mathematical expression using one cell or multiple ranges of cells, and the function whose result is greater than one.
Example of ArrayFormula:
Example 1: Given below is the list of the inventory. Here I want to know the total amount for each product. Using the Array formula as shown in the formula bar we will be able to know the total amount.
After entering the formula for the Cell G3, we can just copy the same formula for the other cells and return the answer.
How to use the Google Sheets ArrayFormula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Arrayformula and fill in the parameters
Step 3: Press Enter
Note:
To automatically enter the ARRAY FORMULA, Press CTRL + SHIFT + ENTER while editing Google Sheets formulas.
IFERROR Formula – Google Sheets Formulas
IFERROR in Google Sheets is another formula that allows you to set a default value that results in an error. If we do not use the IFERROR formula and our sheet does contain error, then the spreadsheet will look like a mess. IFERROR, in other words, is a Cousin of the IF Function with more advanced cleaning sheets. With the Google Sheets formulas, IFERROR you can replace the error values with a new value that you specify in the formula.
When we encounter some uncertain errors in the Google sheet that is when Google Sheets formulas IFERROR pops up. The IFERROR formula will return the first argument if it is not an error and will return the second argument if it contains errors. The Syntax is=IFERROR(VALUE, [VALUE_IF_ERROR]) Here the parameters of the IFERROR Formula are:
- VALUE: The value to be returned if the value if not an error.
- VALUE_IF_ERROR: The value to be returned if the value is an error.
Example of IFERROR Formula:
Example 1: Below is the list to find the Average price per quantity of the Products. Hence we divide the price by the quantity as seen in the left image. In the image, you will see that the cell D3, returns an error.
To remove this error or to make your data look neat, we will use the IFERROR function. Enter the IFERROR formula as seen in the right image and Press Enter. The error will be removed and will b replaced by N/A.
How to use the Google Sheets IFERROR Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the IFERROR formula and fill in the parameters
Step 3: Press Enter.
Tip of the day:
There is a list of Google Sheets Shortcuts also explained to help you use these formulas at a faster pace.
IF Function – Google Sheets Formulas
To check the condition whether it is true or false Google Sheets Formulas IF() is used. IF Function helps us to keep the dataset clean. How does the IF function work? If the condition is true the function will perform a specific task assigned and if the condition is false if will carry out some other operation.
The main purpose of the IF Function is that it returns a value if the logical expression is TRUE and another if the logical expression is FALSE. Google Sheets Formulas IF is the simplest and most helpful formula of all the other formulas. The Syntax is=IF(TEST, THEN_TRUE, OTHERWISE_VALUE) Here the parameters of IF Formula are:
- TEST: The expression which we want to test.
- THEN_TRUE: Operation to be carried out if the test is true.
- OTHERWISE_VALUE: Operation to be carried out if the test is not true or if it is false.
Example of IF Formula:
Example 1: A very simple example to explain the Google sheets formula IF. Below are the marks scored by each student in a class. If the students score less than 45 marks then you must mark it as Fail. And if the students score more than 45 than Pass.
So the IF formula is entered as IF(B2>=45, “PASS”, “FAIL”)
Press Enter and the result is right in front of you in Column C.
How to use the Google Sheets IF Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the IF formula and fill in the parameters
Step 3: Press Enter.
Note:
IF Formula can also be combined with IFS, NESTED IF, VLOOKUP, TODAY and many more Google sheets formulas.
QUERY Formula – Google Sheets Formulas
Google Sheets QUERY formula is used for the retrieval of any data in the Google Sheet. The Query is the most powerful formula in Google Sheets. Query formula combines all the capabilities of arithmetic functions like SUM, AVERAGE, COUNT, etc. Want a Google sheet formula that lets you play with it? Then Yes, QUERY is the formula that will allow you to do so.
The QUERY formula allows you to work with the code that is used to communicate with the database. It is the most powerful as well as a versatile formula in Google sheet. The QUERY functions sums all logical, lookup, summation, counting, averaging, filtering and sorting requirements. The Syntax is=QUERY(DATA, QUERY, [HEADERS]) Here the parameters of Query Formula are:
- DATA: It refers to the range of cells we want to query upon.
- QUERY: In this argument, you need to enter the query to perform. The query must be enclosed in quotation marks.
- HEADERS: This argument is optional. It indicates the number of header rows at the top of the data.
Example of Query Formula:
Example 1: A very simple example to explain the Google sheet’s QUERY Formula. QUERY Formula is used to extract some part of the data from the large data set. In the example below, we will extract the south region data set which will include customer name, region, product, and the total amount.
In our example, Sheet 9 consists of the entire data set. In sheet 10, we want to extract the data, so we will use the Query formula to extract it.
The first parameter is the data range and it is selected as Sheet 9!A1:123.
The second parameter is the Query that we are looking for. So the Second Query states. Select B, C, F, I where C=South. It should be entered between the double quotation marks.
The third parameter is 1 as there is only one header in the range.
Press Enter and the result is displayed in front of you.
How to use the Google Sheets Query Formula
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Query formula and fill in the parameters
Step 3: Press Enter.
Tip of the day:
Google Sheet is an alternative for Microsoft Excel. Check out the Microsoft Excel Shortcuts here.
IMPORTRANGE Function – Google Sheets Formulas
The import of any data from other Google Sheets is allowed by Google Sheets IMPORT RANGE function. Before I came to know about this function, I used to spend tons of hours copying the data from one spreadsheet to another. IMPORTANCE is a powerful Google Sheet formula. The Syntax is=IMPORTRANGE(SPREADSHEET_URL, RANGE_STRING) Here the parameters of IMPORT RANGE function are:
- SPREADSHEET_URL: This URL is found in the search bar of the browser. It is the URL of the spreadsheet from where you want to import the data.
- RANGE_STRING: The data you want to bring in from another spreadsheet.
Example of IMPORT RANGE function:
Example 1: A very simple example to explain the Google sheet’s IMPORT RANGE Function. With the help of URL and specifying the range string you can import data in the spreadsheet. As we see in the image below, we first copy the URL and paste it in the first parameter of the Importrange function. The second parameter is the data range we want to copy in the other spreadsheet.
Press Enter and the data will be copied to another sheet using the Importrange Function.
How to use the Google Sheets IMPORTRANGE function
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the IMPORTRANGE function and fill in the parameters
Step 3: Press Enter.
Tip of the day:
Google Sheet is an alternative for Microsoft Excel. Check out the Microsoft Excel Shortcuts here.
SEARCH Function – Google Sheets Formulas
To find the existence of any value in a string can be made with the utilization of Google Sheets Search Function. With the Google Sheet formula Search, you can check whether a value exists in a string or not. This data is particularly useful while working with large sets of data. The Syntax is=SEARCH(SEARCH_FOR, TEXT_TO_SEARCH, [STARTING_AT]) Here the parameters of Search function are:
- SEARCH_FOR: It is the substring that we are looking for within text to search.
- TEXT_TO_SEARCH: It is the main string within which we will look for search for string.
- STARTING_AT: This argument is optional.
Example of Search function:
Example 1: A very simple example to explain the Google sheet’s SEARCH Function. Let us use the SEARCH function to find where 2 stands in the cell. So we Enter the Search formula as =SEARCH (“2”, A1). Here 2 is the parameter that we are searching for in the cell. And A1 is the text string in which we are looking at.
On pressing the Enter key we get the result as 5. This is because 2 is placed at the fifth character in the cell A1.
How to use the Google Sheets Search function
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Search function and fill in the parameters
Step 3: Press Enter.
Tip of the day:
Google Sheet is an alternative for Microsoft Excel. Check out the Microsoft Excel Shortcuts here.
IMPORTXML Function – Google Sheets Formulas
With Google Sheets IMPORTXML Function you import data from various structured data. Did you ever wish to import data from a particular webpage directly into your Google Spreadsheet? In other words, do you wish to scrape website data into the Google sheets? Then you must think of the Google Sheet formula IMPORTXML. This will make your task easy and save your precious time.
Google sheets formulas IMPORTXML imports data from any of the structured data types including XML, HTML, CSV, TSV, etc. Remember that a large data set cannot be scraped using the IMPORTXML function. The Syntax is=IMPORTXML(URL, XPATH_QUERY) Here the parameters of IMPORTXML function are:
- URL: The URL of the webpage from which you want to extract data.
- XPATH_QUERY: The path to the element you want to extract.
Example of IMPORTXML function:
Example 1: In the example below, I want to extract data relating to the Cricket World Cup. So I enter the first parameter which is URL. URL is the web browser page from which you want to extract the data. We enter the first parameter in two-inverted commas.
How to use the Google Sheets IMPORTXML function
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the IMPORTXML function and fill in the parameters
Step 3: Press Enter.
Note: IMPORTXML Function is much more powerful function than compared to IMPORTHTML Function.
Tip of the day:
IMPORTXML function does not load JavaScript. How to install JavaScript? Check out now.
VLOOKUP Function – Google Sheets Formulas
Google Sheets VLOOKUP function is used to merge multiple data sets. VLOOKUP by far is the most used formula while working with a large amount of data. VLOOKUP function looks for the data in one sheet and returns the data in other sheets. It helps to look up specific information located in the table or database. The ability of the worksheet to look at another worksheet is a great tool. The Syntax is=VLOOKUP(SEARCH_CRITERIA, ARRAY, INDEX, SORT_ORDER) Here the parameters of VLookup function are:
- SEARCH_CRITERIA: It refers to the value we want to search.
- ARRAY: The array or the range in which we are looking for.
- INDEX: The column number from which the matching value is to be returned
- SORT_ORDER: This parameter indicates the column to be searched is sorted. Set this to False so that an exact match will be returned.
Example of VLookup function:
Example 1: In the Vlookup example below, we are looking for the name of the employee using it employee Id. So we enter the Vlookup formula as
SEARCH_CRITERIA: In the search criteria parameter we center the cell A3 since we are looking for Employee ID.
ARRAY: It is the parameter in which we are looking for. So we enter the Array parameter as $A$5: $C$10
INDEX: This parameter is used to get the answer. In other words, we need to enter the column number from which we want the answer. Since we want the name of the Employee we will enter 2
SORT_ORDER: Enter 0 or False so that it will return the exact match answer.
On completing the formula we press Enter and the result is Daniel Rivera. You can see in the list that Employee ID 290000 is for Daniel Rivera.
This is how the VLOOKUP function will help you to find the value in the same sheet as well from the other sheet in the same workbook.
How to use the Google Sheets Vlookup function
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the Vlookup function and fill in the parameters
Step 3: Press Enter.
SPLIT Function – Google Sheets Formulas
Use the Google Sheets SPLIT Function for dividing strings into multiple data points. The strings can be separated by characters into separate cells. The SPLIT formula allows you to split single-cell data into multiple cells. The Syntax is=SPLIT(TEXT, DELIMITER, [SPLIT_BY_EACH], [REMOVE_EMPTY_TEXT]) Here the parameters of the SPLIT function are:
- TEXT: This is the text that you want to divide or split.
- DELIMITER: The character you want to split the text with. It can be any character, space, comma, phrase anything.
- SPLIT_BY_EACH: This parameter is OPTIONAL
- REMOVE_EMPTY_TEXT: This parameter is OPTIONAL. It states whether or not the formula should remove empty text messages from the result.
Example of SPLIT function:
Example 1: To explain the Split Function, below is the list of employee’s full names. Now, I want to split the First Name and the Surname into two different Columns. There is a delimiter in Column A that is a comma. Therefore, we will split the name and the surname by using the delimiter comma. Only two parameters will be used in the cell B3:
Text: Select the Cell A3, since this is the text we want to split.
Delimiter: (,) Comma sign is inserted between the two inverted comma since this is the delimiter to be used while separating the data.
Press Enter. 
On pressing the Enter key you will see that the name and the surname of the employees are separated into two different columns using a comma as the delimiter.
How to use the Google Sheets SPLIT function
Step 1: Press “=” equal to sign in start writing the formula
Step 2: Enter the SPLIT function and fill in the parameters
Step 3: Press Enter.
Read more
List of Google Spreadsheets Formulas
21 BricsCAD Shortcuts you Should know for Windows & Mac
Video Editing Software: Camtasia Shortcuts
Windows Run Commands you Should know
Handbrake Alternative absolutely FREE
Conclusion
These were some of the Google sheets formulas that will help you to master the Google Sheet. To learn in your leisure time, we might help you out with a downloadable Google sheets formulas in a few days. I hope these Google sheets formulas will be very much helpful to you. With these, your head might be swinging a bit less with the thousands of combinations of Google sheets formulas.
So, these were some of the best, most simple as well as most used Google sheets formulas. You must know these before you start working on your spreadsheet. And start doing the coolest stuff with the data.
Hope you have liked our blog on Google Sheets Formulas. Suggestions are always welcomed for the blog you looking forward to hearing from us. Connect with us and Take your Office Productivity to Next Level.
