Though it may seem far-fetched, our world revolves around numbers. From business decisions supported by data to medical applications, there’s the usage of data and spreadsheets.
With the demand for numbers increasing by the minute, knowing how to use formulas in Google Sheets and the best tips and tricks may help you secure opportunities and make data-driven decisions.
In this article, we’ll show you how to make formulas in spreadsheets. We’ll also explore some Google spreadsheet examples and discuss the basics you’d be thankful you knew.
Table of Contents
Using Simple Formulas In Sheets
It’s pretty straightforward to start a simple formula in Google Sheets. All you have to do is click on a cell and type an equal sign (=) to prompt your spreadsheet that you’re going to do mathematical operations next.
Formulas are any equations that you put manually on the spreadsheet, such as basic arithmetic operations like addition, subtraction, and so on. While commonly used interchangeably with the term function, they’re less complicated than the other.
Here are a few examples of Google Sheets formulas:
=A1+B6
=1+2*6/3
=B6/C3+5*8
Basic Google Sheets Functions List
On the other hand, functions are predefined formulas that are built into the system of Google Sheets. They’re represented by keywords that you can call the same way you make a formula — by starting with an equal sign.
Functions are used to make your work more efficient. You just have to feed them your data, and they’ll provide you with the answer you’re looking for almost instantly. For example, you can use the functions below to get the sum and average of certain sets of numbers:
=SUM(A1:A16)
=AVERAGE(A1:A16)
Like the ease? Here are 10 of the most useful Google Sheets formulas and functions worth practicing.
1. SPLIT() to Break Down Data Into Multiple Cells
Let’s say you have a list of emails on your spreadsheet and want to separate their usernames from their account domain. You can use SPLIT(), whose purpose is very apparent in its name, to do this. Here’s the generic formula of this function:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
The text here refers to the original data, while the delimiter is the character that separates it. In our example, your email is the text, and the @ symbol is your delimiting factor. Here’s how you use it:
- Toggle the cell that you want to put the function in.
- Input =SPLIT(.
- Select the cell that contains the original text (the email) and put a comma.
- Put the delimiter in quotation marks like “@” and close the parenthesis.
- Press Enter, and you’re done.
- To repeat the results, just drag the cell down to copy the formula for each row.
Learn More About the SPLIT Function
2. CONCATENATE() to Join Values Into One Cell
If you’re trying to put together two values from separate cells to just one cell, the CONCATENATE() function may be useful. Below is the general syntax of this function on Google Sheets:
=CONCATENATE(string1, [string2, ...])
For example, you want to auto-generate email addresses for the employees of a certain company. You can do this by listing suitable usernames in one column and concatenating your preferred domain in the latter part.
Here’s what you can do:
- Select your preferred cell.
- Type =CONCATENATE(.
- Click on the cell that contains the username and put a comma after the cell name.
- Input “@yourdomain.com”) to append the email domain and close the function.
- Press Enter, and you’re done.
- Google Sheets will likely suggest an autofill; you just need to toggle the check mark to complete the rest.
3. JOIN() to Combine Arrays Into One Cell
A very similar function to CONCATENATE() is JOIN(), with its key distinction being its use of an array of values. Below is its general form:
=JOIN(delimiter, value_or_array1, [value_or_array2, ...])
By the term array, we are referring to data in the form {value1, value2, value3, …} or a range like A1:D1. Keep in mind that it works like CONCATENATE() when you don’t specify a delimiter. Here’s how you use it in a practical example:
- Toggle your preferred cell.
- Type =JOIN(.
- Input your delimiter, such as “, ” to separate the values, and put a comma after.
- Select your value range and close the function.
- Press Enter and toggle the autofill check mark to finish the rest.
Learn More About JOIN in Google Sheets
4. SUBSTITUTE() to Replace Texts Inside Texts
Have you encountered some texts that you accidentally spelled wrong on your spreadsheet or want to change them arbitrarily? The SUBSTITUTE() function is a quick method to use.
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
The arguments are pretty self-explanatory, so let’s jump to using the function practically. Let’s say you want to change all small a’s on the names listed on your file. Follow these steps:
- Select a cell on your spreadsheet and type =SUBSTITUTE(.
- Toggle the cell that you want to search values from and put a comma after.
- Enclose the text that you want to find in quotations, e.g. “a”.
- Put a comma after the second argument and type in the replacement text you want, e.g. “o,” and close the function.
- Press Enter and let the function do its magic.
5. IF() to Set Values Given a Condition
When you want to give certain values depending on true or false conditions, the IF() function is what you need. It usually has the following form, though it may be used in conjunction with other functions:
=IF(logical_expression, value_if_true, value_if_false)
As ever, the arguments of most functions are named according to their purpose. Here’s how you use it in your spreadsheet:
- Toggle a cell and type =IF(.
- Type a condition, e.g., <cell name>=123 or B4=123, and separate with a comma.
- Give a value when the condition is met, such as “Match,” and put a comma after.
- Provide a value when the answer is false like “Mismatch” and close the function.
- Press Enter and copy the formula to the other cells.
6. IFERROR() to Catch Formula Mistakes and Invalid Values
Human error is bound to happen, especially when dealing with long formulas and functions. You may be selecting invalid data or doing operations that result in undefined values. It’s easy to prevent that by using a warning function like IFERROR():
=IFERROR(value, [value_if_error])
It will retain the original value if there’s nothing wrong with it but returns another value when an error is detected, which is usually blank by default. Here’s an example to help you understand it better:
- Select your preferred cell and type =IFERROR(.
- Type in your operation e.g., B4/C4, and put a comma after.
- Set a predefined value when the operation fails like “Undefined” and close the function with a parenthesis.
- Press Enter and repeat the formulas in other cells if you prefer.
7. SUMIF() to Add Values According to Set Conditions
If you’re one to calculate your expenses given their paid or unpaid status, the SUMIF() function is a handy tool to use. It returns the sum of all values that meet certain conditions that you set and is in the following general form:
=SUMIF(range, criterion, [sum_range])
The range refers to the set of values you’re trying to retrieve data from, the criterion is your condition, and the [sum_range] is the range of numbers you want to add:
- Select a cell and put =SUMIF(.
- Highlight your preferred range and put a comma after.
- Place your criterion, e.g., “Paid.”
- Separate the above with a comma, select your sum range, and close the function.
- Press Enter and see your results.
8. VLOOKUP() to Retrieve Data From Tables and Databases
As with its name, the VLOOKUP() function helps you check out certain values that meet your set conditions and retrieve them to another cell. Here’s its generic form:
=VLOOKUP(search_key, range, index, [is_sorted])
The search_key serves as the identifier of the row that you want to locate, while the range is the table or set of values that you want to check out. The index tells which column you want to retrieve data from starting from 1. If your data isn’t sorted in any way, setting the [is_sorted] argument FALSE would avoid errors:
- Select a cell and put =VLOOKUP(.
- Toggle the cell that contains your search key.
- Highlight your data range and press F4 on your keyboard to make it absolute.
- Specify your wanted index (or column), such as 2.
- If your data isn’t sorted, type FALSE.
- Ensure that the above are separated with commas for the function to work properly.
- Close the function, press Enter, and drag the formula to other cells if preferred.
Related: 20 Google Sheets Shortcuts You Must Know!
9. SPARKLINE() to Create Miniature Charts In a Cell
Perhaps one of the most useful functions on Google Sheets, the SPARKLINE() function is a godsend if you want to present your data visually — in a single cell. And it’s pretty easy to use too if you want to keep things simple:
=SPARKLINE(data, [options])
The data here refers to your set of values, while the [options] are the customization choices that you can try. For example, you can set the chart type to a column or bar, which usually is a line graph by default. Plus, you also get to change colors and more to your taste.
For the purposes of this guide, we’ll keep things simple:
- Go to a cell and type =SPARKLINE(
- Highlight your data range and close the function with a parenthesis
- Press Enter and you should see a simple line graph on your selected cell
Learn More About Google Sheets Sparkline Charts
10. IMPORTRANGE() to Retrieve Data from Another Spreadsheet
If you’re looking to retrieve data from an entirely separate spreadsheet, the IMPORTRANGE() function would be your best partner. Here’s how it looks:
=IMPORTRANGE(spreadsheet_url, range_string)
You just need to provide the URL of your spreadsheet for the spreadsheet_url argument and the specific sheet and range of your data for the latter:
- On a cell, type =IMPORTRANGE(
- Paste your other spreadsheet’s URL in between quotation marks and put a comma after it
- Indicate the range of your data by typing the sheet and cell range like “Sheet1!B4:D8”
- Close the function with a parenthesis, press Enter, and wait for your data to load
How To Use Formulas In Google Sheets the Right Way
After knowing the basic functions and formulas you can use on your Google Sheets, you want to ensure you’re doing it correctly. Aside from keeping your spelling and data selection correct, you also want to use the onion method.
In an onion bulb, there are smaller pieces enclosed by larger portions. Similarly, you want to start from a smaller function and complete it before nesting it under a bigger one. This makes your functions much easier to understand and avoids errors.
Plus, visiting our guides at Spreadsheet Point and the Google Sheets documentation would be key to familiarizing yourself with the new functions you encounter.
Related: Easy Guide to Google Sheets Array Formulas [Step-by-Step]
Explore More Google Sheets Formulas
We hope you have a better and more efficient workflow with these simple but powerful Google Sheets formulas. Imagine the improvements you can gain further should you learn more advanced techniques with the software. To become the Google Sheets power user you want to be, don’t forget to explore our other guides too.
