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.