Google Sheets is a Microsoft Excel replacement with almost all the functions you need to perform daily spreadsheet tasks.
There are productivity functions in Google Sheets like VLOOKUP and “IF” functions amongst hundreds of other formulas that you can use to make work and life easier.
These formulas can help you get quick answers to powerful functions that can do more than just sum a column for you.
With Google Sheets formulas, you can find the length of strings, sum only specific criteria, or count ranges. These are just a few of the things you can do to increase productivity with Google Sheets.
VLOOKUP: Helps Automate Finding Vertical Values
VLOOKUP stands for vertical lookup. It helps you find something specific in an array of columns.
Let’s say you have two columns with the first name in column A and the last name in column B. You can use VLOOKUP to find the first name “Ringo” in column A and return the last name, “Star,” from column B.
The variables in your formula are as follows:
- search_key: The value you are searching for
- range: The row and column you want to search in
- index: The column you want to take the value from
- is_sorted: True by default, but recommended that you use False if your first column is not sorted
Here’s how to enter the formula in the cell: =VLOOKUP (search_key, range, index, [is_sorted]). Parameters in brackets are optional.
LEN: Counts Characters
LEN is a formula that will count every character in a specific string. Let’s say you have only a few characters for a personalized bumper sticker for your brand and want to know how many characters are in your clients’ names. You could use LEN for that.
For example, you simply use the LEN function with the cell number =LEN(A1). Here, Google Sheets would read cell A1, and the LEN function would count the characters.
IF: It’s Only Logical
The IF function in Google Sheets is a logical test. If you want to learn if something is true or false, tell Google what condition you are testing, and ask for it to return a true or false result.
This will test a cell or range of cells to let you know your yes or no answer. The formula is: =IF(logical_expression, value_if_true, value_if_false).
Here are the variables for the IF formula:
- logical_expression: The condition you are testing
- value_if_true: Answer if true
- value_if_false: Answer if false
For example, in a list of first names, you could use an IF expression to show a true or false result for anyone named John. “John” would be your logical_expression. If the cell contains the name John, the result would be True; otherwise, the result would be False.
SUMIF: A Sum If Something is True
This formula will give you a sum of a range of cells that you specify.
The formula looks like this: = SUMIF(range, criterion, [sum-range]).
In this formula, the range is the selection of cells you are summing, the criterion is the condition you are testing, and the optional “sum-range” is used to compute a different range of cells based on the result of the first range.
You could use this formula to calculate the salaries of employees making greater than $50,000 per year.
COUNTIF: Counts Very Specifically
With this formula, you get a precise count of cells based on your criteria.
The formula looks as follows: =COUNTIF(range, criterion).
The range for this formula is the number of rows you are looking in, and the criterion is the variable you are counting.
So let’s say you want to find out how many of your cells in a set range will ship to New York City. The formula would look like: =COUNTIF(C1:C20, “New York City”).
IMPORTRANGE: No More Back and Forth
It’s a pain when you have multiple spreadsheets all over your desktop, and you are going back and forth between them.
Enter the importrange formula. All you need are the spreadsheet URL and the range of cells you are extracting to import that data into your primary worksheet.
This formula looks like this: =IMPORTRANGE(spreadsheet_url, range_string).
ARRAYFORMULA: One Formula, One Time
You can perform more than one function in rows on Google Sheets. ARRAYFORMULA will make your life easier by applying one formula to an entire row, and you’ll only have to type it one time. Just make a note of that if you are making any changes.
Here’s the formula: =ARRAYFORMULA(formula).
To see how many shipments are going to Miami instead of New York within a specific range, you would use: =ARRAYFORMULA(IF(C1:C20=” Miami”, “Yes”, “No”).
Easy Shortcuts You’ll Be Obsessed With
Keyboard shortcuts make productivity easier and efficient. You’ll never stop using them.
Here are a few easy formulas you will want to put on a post-it-note somewhere:
|Clear formatting||⌘ +||Ctrl + \|
|Today’s date||⌘ + ;||Ctrl + ;|
|Select table data||⌘ + a||Ctrl + a|
|Find and replace||⌘ + Shift + h||Ctrl + h|
These are just some of the most commonly used productivity tools and formulas that will make your Google Sheets life a lot easier. Play with them, and you’ll quickly become obsessed and want to use more.