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
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
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.
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:
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:
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 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:
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:
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:
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:
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:
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:
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:
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:
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.
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.
