Thanks to Google Sheets formulas, you can do far more than just perform general sorts or add columns. The most useful formulas go beyond adding, counting, and giving you averages, though all of those are useful too. Imagine if you could easily import just the data you needed into another spreadsheet or only count cells that match specific criteria. That’s just the start of what’s possible.
Tip: If you’re working on a budget, you can apply these budgeting templates for Google Sheets.
Using Google Sheets Formulas
If you’re new to using formulas on Google Sheets, they work similarly to Excel. Simply select a cell or range and enter the formula. All formulas start the same, with an equals (=) sign.
As you start typing a formula, Google might provide suggestions. You can also go to the “Insert” menu and select “Function.” This gives you a list of possible formulas/functions to use. This is ideal if you need something, but can’t remember how it starts.
What’s also great is that Google gives you the formula format so that you know what parameters are necessary.
COUNTIF builds upon the commonly used COUNT formula. While simply counting all the cells in a range is useful, you don’t always want to count everything. Instead, you choose the criteria, such as above or below a certain number or just a specific name.
The formula is formatted like:
Use this Google Sheets formula by doing the following:
- Pick an empty cell and start your formula:
- Enter your desired cell range.
- Enter your criteria in quotation marks.
For my example, I wanted to see how many cells had totals over $20,000 for January. My formula was
=COUNTIF(F2:F352,">$20,000"). You don’t have to place the formula in the same column or row as what you’re counting.
Tip: Learn how to change the cell color in Google Sheets to making the form a little more interesting.
One of the most useful and underrated Google Sheets formulas is without a doubt VLOOKUP. Vertical lookup lets you pull information from a set of data based on a single piece of information.
For instance, you might have a list of employee ID numbers and names in one table, but have another set of data that only has the ID and pay rate. If you wanted to add the employee names to this second set of data, you could do it manually or use VLOOKUP to automatically match the names based on the employee ID number that the two data sets share.
You can use VLOOKUP within the same sheet or on a different sheet in the same file. For example, you might have multiple instances of VLOOKUP on other sheets that automatically look up data based on a master sheet. Change the data on the master sheet, and the formula results change on the other sheets.
At first, it does seem complicated, especially when you look at the formula parameters:
=VLOOKUP(lookup_value, table_array, column, range_lookup)
Simplified, you need the value you’re looking up, the cell range the data’s in, the column number of the data you need to display, and and to note whether you’re doing an exact or approximate match. Our VLOOKUP guide explains this in more detail.
For this example, I’m using a small dataset of employee ID data and pulling the names into another table that has ID and pay but not the name.
- Select the cell you want the result in and start your formula:
- Enter the cell the lookup will be based on. In my case, I’m using the employee ID:
- Enter the range you want to search. The only downside to VLOOKUP is the column that includes the search ID must be the first column in your range, and the data you want to pull must be to the right of it.
- Enter the column number (not letter) that you want to pull information from. Since I want the first name, I’m pulling from Column 3.
Falsefor your range_lookup. True is for approximate, while False is for exact. If no match is found, you’ll see “ERROR” instead.
My final formula ends up as:
If you’re using VLOOKUP between different sheets, add the name of the sheet before your table array, such as =
Working on Android? Check our our list of the best spreadsheet apps for Android.
If you need to frequently import data from one Google Sheets spreadsheet to another, IMPORTRANGE can quickly become your best friend. Sure, you could just copy and paste what you need, but for large sheets, this can take a while, especially if you only want to import certain parts of a sheet. You can do this between completely different Google Sheets files.
The syntax is simple:
- Open the spreadsheet that you want to import data to and start your formula in the first cell to place the data:
- Copy and paste the spreadsheet URL of the spreadsheet you want to import from.
- Select the range you want to import. This can be from any sheet on the spreadsheet. By default, Google Sheets uses the first sheet. If the data’s on the first sheet, simply enter a range like
A2:F100. If it’s on another sheet, you’d enter it as
To pull just 2023’s forecast data from a financial’s sheet, I used the following formula:
If you get a #REF error, you’ll be prompted to connect the sheets. You only have to do this once, and Google does it automatically when you select
Anytime the data is updated, your sheet will update as well.
If you need to link spreadsheets to share data between them, use this guide to make it easier.
No one likes a spreadsheet full of #VALUE errors. This just distracts from your data. This doesn’t mean anything’s wrong with your formulas; it just means there isn’t a result to display. For example, if you’re performing calculations between columns but some cells are blank, you might get errors. Using =IFERROR lets you choose a value to use as the result instead.
For this formula, you’ll nest your original formula within IFERROR, such as:
For this example, I’m calculating the average price per item based on a sale total. But, recurring payments have an item total of zero.
- Select the first cell where you use your original formula.
- In the formula/function box just above the sheet’s data, add
IFERROR(directly after the = sign.
- The first parameter is your existing formula. For more complex formulas, you may need to add extra parentheses to enclose it properly.
- Add your desired value as the second parameter and close the formula with a parenthesis. In my case, I want the cell to be blank, so I’m adding ” ” as the second parameter.
My final formula is:
As with many of the most useful Google Sheets formulas, ARRAYFORMULA works best with larger data sets. Google Sheets tends to slow down when it has to deal with numerous formulas. ARRAYFORMULA helps solve this issue by using a single formula for a range of cells.
This is faster, and if you ever need to change the formula, you do it once and don’t have to worry about copying it to other cells. Plus, if you add in another row, this formula automatically includes it.
For this example, I want to add an employee’s pay and bonus and place the total in a new column. I could just use
=B2+C2 and drag the formula down the column, but if I’m dealing with hundreds or thousands of rows, this gets tedious quickly.
- Select the first cell that you want to use a formula. For me, this is D2.
- Start the formula with
- Enter your usual formula but use cell ranges versus individual cells. For example, instead of
B2, I’d use
B2:B14since that’s my current range.
- Once you enclose your formula in parentheses, your entire column should fill in using the single formula.
My final formula was
=ARRAYFORMULA(B2:B14+C2:C14). Naturally, these can get more complex, but that’s the basic syntax. You can easily change the operator, such as getting a percentage or subtracting the bonus from the pay. You’d only need to do it in the first cell to change all other cells in the range.
As long as you add a new row before the end of the range listed in your formula, it will automatically adjust ARRAYFORMULA with the new range.
Good to know: you can use Google Pay to track your spending and budget your money.
While you can easily filter and sort data by going to “Data -> Create a Filter,” it’s easier to just use the FILTER formula to get just the results you want to see. The parameters are fairy simple:
=FILTER(range, condition1, otherconditions)
The “otherconditions” part is optional. These are essentially true/false comparisons of other cells to further filter your results.
- In a blank cell, start your formula. Ideally, you’ll create the same column headers as the data you’re filtering, then start the formula in the first cell under your first column header. For example, I’m filtering Employee IDs with a Pay greater than $120,000.
- Enter your range and first condition. You can close the formula here or enter more conditions separated by commas. For me, my condition is only choosing values in the range of
F2:F14that are over $120,000.
- Your results will appear under your new column heads (if you have any).
The great thing about this formula is that if you make any changes to your data, your results automatically update. Add a new row within the original range, and it’s automatically included. This is a far more dynamic option than the filters within the Google Sheets menu.
Have you ever needed to join two or more cells into a single cell? For instance, maybe you ended up with a spreadsheet with everyone’s first and last name in different columns. You can join them using the JOIN formula. It’s one of the more surprisingly useful Google Sheets formulas for anyone that needs to combine data into single cells.
The parameters include:
=JOIN(delimiter, value or array 1, value or array 2, etc.)
While you can have more than two values or arrays, you must have at least two. You’re free to join just single cells or full arrays or ranges into a single cell.
- Select the empty cell to start your formula.
=JOIN(and enter your delimiter. This can be a comma, blank space, hyphen, or anything else you want. Just be sure you place the delimiter in quotes, such as “,” or “-“.
- Enter your first and second values/arrays separated by commas. My final formula for joining first and last names is
Alternately, you could use ARRAYFORMULA to do this for larger sheets. Using the same example above, I’d use:
=ARRAYFORMULA(B2:B14 & " " & C2:C14)
In this case, you add the range for the first column and use the ampersand (&) symbol to join them. The middle section is for your delimiter.
Tip: Learn how to type ampersands and other special characters in Windows.
Do you have the opposite problem and need to split items apart in a cell? Just use the SPLIT formula. It’s the opposite of JOIN. For instance, maybe you want to separate first and last names to make it easier to sort data alphabetically by last name.
For SPLIT, the parameters are:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Made more simple, “text” is the cell you want to split, the delimiter is the character used to specify where to split the text, and the last two are optional.
split_by_each refers to whether you want to split at every matching character, such as splitting apart the word “character” based on each “a” or just the first one.
Remove_empty_text removes empty text from your results. It’s set to TRUE by default.
You’ll need two or more empty cells, one for each part of the text that will be split. In my example, I’m splitting the full name into first and last names, so I only need two empty cells.
- In your first empty cell, start your formula with
- Enter the cell you want to split.
- Enter your desired delimiter. For me, it’s a blank space, so I’m using ” “, but this can be anything, such as a letter or symbol.
- Close your formula and multiple cells in the same row fill in.
Tip: learn about working with Apple Numbers files to make them Windows friendly.
Frequently Asked Questions
Can I just highlight ranges versus entering them manually?
Absolutely! Once you start your formula and reach a parameter where a range is necessary, use your mouse or finger to highlight the range. Google will automatically fill in the column and row information, and you can move onto the next part of your formula.
How can I learn more about a formula?
Outside of looking up examples, Google Sheets offers a great explanation of formulas. Go to “Insert -> Function” and select your function/formula. Click the + sign next to the formula name when it appears in the cell, and click the drop-down arrow to view parameters and what those parameters mean.
If you click “Learn More,” you’ll see more details from Google Sheets Help.
Are all Google Sheets and Microsoft Excel formulas interchangeable?
Image credit: Unsplash
Our latest tutorials delivered straight to your inbox