The efficiency and simplicity of Google Sheets have made it an invaluable tool for various business functions. From sales to management—analysis to accounting—it’s a go-to powerhouse for handling data.
But are you getting the most out of Google Sheets? With so many options, it can seem overwhelming at first. But learning just a few basic Google Sheet formulas can help maximise the potential of this powerful tool and put you on the road to data paradise!
We’ve compiled this list of 20 Google Sheet formulas to help you along the way. Whether you’re a Sheets regular or a complete novice, these nifty little formulas can save you a whole lot of time, leaving you free to focus on the bigger picture.
So, let’s dive straight in!
Google Sheet formulas allow you to perform calculations with the data you enter into a spreadsheet. You can use them to efficiently arrange lists, add up specific numbers, extract information, or perform more complex tasks such as drawing graphs.
Every formula begins in an empty cell with an equal sign “=”, followed by the type of calculation you want to make and the cells you are referring to. For example, the formula = SUM(A1:A20) will add up all the numbers in cells A1 to A20 in the cell that you write it in.
As you’ll see, many formulas will require using information written in quotation marks. Note that it’s sometimes best to manually type these quotation marks, as copying and pasting from other sources can result in errors.
And, to reference the value of a specific cell in a formula, simply type the cell’s address, such as A1 or B2. You can also reference ranges of cells, such as A1:A10 or B1:C5.
When you’re handling large volumes of information, calculations can quickly get complicated. And, the smallest mistakes can have a big impact and slow down productivity. The speed and efficiency of Google Sheets formulas help streamline what might otherwise be fiddly, cumbersome, and click-intent data management tasks.
While you may be familiar with some of the more typical functions on a spreadsheet, such as adding cells together or arranging lists, did you know that, similarly to Google Scripts, you can use Google Sheet formulas to perform a diverse range of more complex tasks?
From importing XML data directly from a website to charting graphs and even using Google Translate right there in your spreadsheet, there’s a formula for every occasion!
SPLIT() allows you to separate information from one cell into multiple cells.
For example, let’s imagine you have a list of 10 full names and want to separate the first name and the surname into two separate columns.
Say that the full names are listed in column A. Typing the following formula in column B will automatically divide and place the first name and the surn`me into the next two columns, B and C:
- =SPLIT (A2, ” “)
In this formula, you must use a space in quotation marks to indicate the two names should be separated by the space between them. Or, for example, if in your list there are commas in between the names, such as SMITH, JOHN—then you would use a comma and a space instead: “,”. All you have to do next is drag the cells downwards to fill in the rest of the names and you’re all set!
Alternatively, if you want to combine various bits of separated data into one cell, the JOIN() formula is the one you need!
Let’s take the same list of names, this time separated into first name and surname in two columns. In a new cell, we type:
- =JOIN (” “, A2:B2)
Here, the delimiter you put in the quotation marks will be what separates the content of both cells in the new cell. In this case, it’s a space, but you could replace this with a comma or even omit it if you simply want to join the text from both cells or a cell range.
Next, simply pull down the blue square to complete the same formula for the rest of your list!
SUM() is one of the more commonly used yet simple Google formulas that adds together numerical values within a range of selected cells.
You can enter this manually by typing =SUM() and the cell range you want to add together specified in the brackets. If you type it at the bottom of a list of numerical values, Sheets will automatically detect the numbers you want to add together, but you can adjust it to suit your needs.
Alternatively, you can also select the cells you want to add together and select the SUM function from the drop-down menu.
The COUNTA() formula counts how many values are entered in a set, whether that’s a list of numbers or words. This can be very useful if you have a bunch of lists of various lengths and need to know how many entries are in each list. The formula syntax looks like this:
Again, by typing this in a new cell beneath a set of cells, Sheets will automatically assume you want to count the number of cells above it and helpfully select them for you. Just press enter, and it’s done!
It’s not always practical to insert complex charts into spreadsheets, especially when working with multiple sets of data. The SPARKLINE() formula allows you to create quick and convenient at-a-glance charts that each fit neatly into one cell. They can be in the form of line graphs, single-cell bar charts, or column charts.
So, for example, if you have a row of numbers in cells A2 to E2, for a basic line graph, you would type:
- =SPARKLINE (A2:E2)
And to display a bar, column, or a win/loss graph, just use one of the following extensions:
This one does exactly what it says on the tin! Whether you need to decipher the language from the text in a single cell or from a list with various languages, you can do so right there in the spreadsheet.
Simply type in =DETECTLANGUAGE() followed by the cell you wish to detect the language of, and Sheets will work out the language and display it as a language code (a short-hand for each language, e.g., ‘en’ for English, ‘fr’ for French, etc.).
If you have a list, simply drag down the box, and they’ll all appear as if by magic!
The SORT() formula is perfect for rearranging lists of data into numerical or alphabetical order. Within this formula, we use the word ‘TRUE’ for ascending lists (in the case of numbers, they will then increase as you go down the list) or ‘FALSE’ to produce a descending list (in the case of numbers they will start high and get lower as you go down the list). However, if you don’t put either, Sheets will assume ‘TRUE’.
Take this list of numbers in column A that you need to put into numerical order from smallest to largest. In the empty column B, type =SORT(A1:A17) and your numbers will appear there and arranged perfectly in ascending order.
Like SUM(), the SUMIF() formula calculates the sum of the cells you have selected. But it has the additional feature of being able to set certain conditions, as shown here in the formula syntax:
- =SUMIF (range, criterion, [sum_range])
Using this unsorted list of team scores, we can use SUMIF to add only the scores of each team. So starting with ‘blue’ typed in D2, we write the formula as:
Here, B2:14 is the range of cells we are analysing; D2 is the condition we want to find (blue); and A2:A14 represents the numbers we want to add.
Then, just pull the blue square down to perform the same operation on the remaining cells.
ARRAYFORMULA() allows you to apply the same formula to an array of cells at the same time. Just type =ARRAYFORMULA(), followed by the formula you want to apply in the parentheses.
For example, if you want to multiply the numbers in cells A1:A10 by 2, you would enter “=ARRAYFORMULA(A1:A10*2)”
Here, * is used to denote multiplication, but you can, of course, replace this with various different equations depending on the task at hand.
If you need to change a particular word within a cell, then the SUBSTITUTE() formula can save you loads of time!
It’s as simple as typing =SUBSTITUTE(), followed by the cell you’re affecting, the word you want to change, and then what you want to replace it with, all in the brackets.
For example, if you want to replace the word “new” with “New” in “new York” in cell A2, type:
VLOOKUP allows you to search for a specific value in one column of a table and then return a value from another column of the same row.
Suppose you have a table with two columns, “Item” and “Price,” and you want to find the price of a specific product. In the case when searching for “microphone”, you would type:
- =VLOOKUP(“microphone”, A2:B5, 2)
This formula will search for the value “microphone” in the first column of the range A2:B5 and return the value from the second column in the same row—in this case, the price.
Here, the “2” signifies the column you want to extract the information relating to the microphone from, as it is the second column in the range selected, if counting from left to right.
The SEARCH formula in Google Sheets allows you to search for the position of a specific piece of text within a larger string of text.
For example, here, we want to search for the word “valley” in the string of text within cell A1.
By typing =SEARCH (“valley”, A1) we tell the program to locate the position of the word “valley” within cell A1. In this case, the location is 7, meaning 6 characters are in front of it in the string of text when counting left to right (with the “h” in “hill” being located in position 1). Although this is a short example, this could really come in handy when dealing with much longer strings of text.
The IF() formula allows you to test a condition and return a specified value if the condition is true and another value if the condition is false.
For example, if you want to record if numbers within a list in column A are above 10 or not, you could type the following into the next free column:
- =IF(A2>10, “yes”, “no”)
Here, the greater than (>) sign is used and the formula will return “yes” if the value in cell A2 is greater than 10 and “no” if the value is less than or equal to 10. To apply it to the whole list, simply drag the blue square down the rest of the column, and it will fill it all in automatically!
This neat little formula allows you to input Unicode numbers to generate a particular character. Simply type =CHAR() followed by the Unicode number you want. For example:
- =CHAR(8226) will return a bullet point as shown here:
Or, perhaps you need a smiley? That’s:
Have you got a set of data laid out in vertical columns that you need to turn in rows, or the opposite? Then, the TRANSPOSE function is the formula for you!
Take the following table as an example:
To easily move all the data into a horizontal table, just type =TRANSPOSE into a new cell and drag the box over your original table.
Sheets will automatically transpose all the data, including the titles of the columns.
Did you know you can easily import data directly from a website into Google Sheets? This is particularly useful when web scraping, a vital part of doing research.
Say you want to import all the links used in a particular website. In an empty cell, just type the formula, followed by the website URL in quotation marks and what data you want to import from it. In this case, that will be “//@href”, which will gather all the links.
The FILTER() formula allows you to filter and extract only the data you want from a list or table.
For example, let’s say you have a table of information about food items and you want to make a new table but with only the data relevant to one category—in this case, snacks.
Here, the formula =FILTER(A2:C10,A2:A10 =”Snack”) tells the sheet to filter data from cells B2:C10 by the word “Snack” from column A2:A10 and place only those bits of information together in a new table, as seen here:
QUERY() is perhaps the most versatile and powerful Google Sheet formula. You can use it to perform a range of different tasks, even doing the job of other formulas! That’s because it allows you to use basic SQL functions.
The basic formula for the QUERY function is:
- =QUERY(‘range’, ‘query’)
Where “range” is the range of cells to be queried and “query” is a string containing the query.
The key query words are SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, LABEL.
Depending on the task at hand, you can use various combinations of these functions, but they must appear in this order.
For example, if you have a set of data in the range A2:B10 and you want to retrieve all rows where the value in column B is greater than 100, you would use the following formula:
- =QUERY(A1:B10, “SELECT * WHERE B > 100”)
In this example, the key query words are “SELECT” and “WHERE”, and the * character instructs Sheets to select all columns from the data range. While this a relatively simple example, the versatility of the QUERY() function means it can be used to do many more complex tasks, making it one of the best formulas to master!
One of the benefits of using Google Sheets is having access to some of Google’s other useful tools right there in your spreadsheet. The GOOGLETRANSLATE() function does exactly that and can conveniently translate the text in a cell or over a whole set of cells.
Let’s say you have a list in column A written in English that needs to be translated for use in Spain.
In column B, simply type the formula followed by the cell you want to translate and the languages you wish to translate between.
- =GOOGLETRANSLATE (A1,”en”,”es”)
Note that Google Sheets uses abbreviated language codes, so English becomes “en” and Spanish–“es”.
As with other formulas, you can then drag the blue square downward to complete the list.
Last but not least, this formula is great for when you want to pull a particular range of data from one Google Sheet into another.
You’ll need to copy the URL of the spreadsheet you want to import and note the range of the cells that you wish to extract.
- =IMPORTRANGE (“https://docs.google.com/spreadsheets/example1”, “A1:B10”)
You may be prompted to give permission for access to the source spreadsheet. Once you have done this, the imported data will automatically update whenever the source data is changed.
Now you know about what Google Sheets formulas one, and you have a list of the top 20 Google Sheets formulas. Put them to good use, and come back to this guide when you need a refresher.
Let’s recap what we covered:
- Google Sheets formulas are an easy way to organise the data in your Google Sheets spreadsheets.
- You can use them for performing a range of functions – from simple mathematical equations to more complicated tasks.
- They always begin with an equal sign (=) and can include a combination of numbers, cell references, and mathematical operators.
- You can reference a specific cell in a formula by simply typing the cell’s address, such as A1 or B2, or selecting a range by typing A1:A10.
- Remember that copying and pasting formulas with quotation marks from other sources can result in errors, so try typing these manually if an error occurs.