When you use Google spreadsheets for production, you have access to a variety of formulas that can help make your workflow much easier. Various functions allow stakeholders to organize, edit, search for and analyze different types of information.
Let’s look at five of the Google Sheets formulas that business owners and bloggers should learn more about.
SPLIT() to Divide Data Into Multiple Cells
Sometimes you may need to take data in one cell and split it into multiple ones. You can perform this function by way of the split formula.
Formula: SPLIT(Text, Delimiter)
The text equals the text that will be divided by the parameter. The delimiter is the character(s) used to split the text.
Use this function if you have a list of names, but the first and the last name are not separated. You can use the split formula to easily place each first name and each last name into appropriate columns.
SUBSTITUTE() to Replace Existing Text
There’s nothing worse than completing a huge document only to realize that you now have to go back and make several replacements for specific text. Well, instead of meticulously searching for and replacing each word one by one, we could simply use the convenient substitute formula.
Formula: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Be mindful that this formula is case sensitive and you must use a comma to separate each attribute for it to work properly. In other words, if you want to replace “Woman” but type “woman”, the formula will only recognize the instances of “woman”.
IMPORTRANGE() to Import a Range of Cells
Sometimes we need to import information from one document into another. The Importrange formula allows us to import a specific range of cells from a different worksheet into another.
Formula: IMPORTRANGE(spreadsheet_url, range_string)
The formula may mistakenly seem complex — especially when you view the long spreadsheet URL — but it is quite simple. All you need is the spreadsheet URL and the cell range of the data you want to import.
Here’s an example:
IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1Zoq0M0RG-RLYZ9HjOf01ff9eSPIYY3s/edit#gid=1027643093”, “Sheet2!A1:C12”).
Notice that quotes enclose both the spreadsheet URL and the range. That is because Google Sheets treat both arguments as strings. You will also notice an exclamation mark between the sheet name (Sheet2) and the cell range ( A1:C12). You must include this mark to separate the two.
SUMIF() to Add Numbers in a Range Cell
You can use this formula to deliver the sum of cells based on the condition in a given range.
A perfect use of this formula is to add up the prices in specific categories while excluding others.
One case scenario for this formula may involve a review of your budget. You may have a list of items in categories like Books, Movies, and Electronics. If you only want to see what you have spent on books, you can use this formula:
=SUMIF(B1:B10, “books”, C1:C10).
The B1:B10 range represents all the categories (Books, Movies, Electronics) while the C1:C10 range represents all the prices. When using the parameter “books” in the formula, we pull only the book prices from both columns.
Highlight Duplicates to Find Repetitive Text
Sometimes when we create a long list or spreadsheet, we may inadvertently have duplicate data in some cells. To find any repetitive text, we can make use of the highlight duplicates formula.
If you often have to worry about duplicate content in a spreadsheet, learn more about using highlight duplicates to clean up your document.
Google Sheets is an extensive tool with many features and formulas to make business workflows more efficient. This list is merely a very short sampling of the ideal formulas that you should know. As you become familiar with more of these formulas, you will see vast improvements in your overall production.
