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.
1. COUNTIF
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: 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.
2. VLOOKUP
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: 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. 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 =VLOOKUP(E2,FirstSheet!$A2:$C14,3,FALSE). Working on Android? Check our our list of the best spreadsheet apps for Android.
3. IMPORTRANGE
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: 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 Allow access. 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.
4. IFERROR
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. My final formula is:
5. ARRAYFORMULA
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. 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.
6. FILTER
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: The “otherconditions” part is optional. These are essentially true/false comparisons of other cells to further filter your results. 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.
7. JOIN
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: 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. Alternately, you could use ARRAYFORMULA to do this for larger sheets. Using the same example above, I’d use: 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.
8. SPLIT
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: 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. Tip: learn about working with Apple Numbers files to make them Windows friendly. Image credit: Unsplash If you click “Learn More,” you’ll see more details from Google Sheets Help.
