A Guide to Google Sheets Formulas

What is Google Sheets?

Google Sheets is a popular spreadsheet utility that is included as a part of Google's free, web-based Google Docs Editors suite. The service also features Google Docs, Google Drawings, Google Forms, Google Sites, Google Slides, and Google Keep. In short, it’s Google’s take on Excel, although there are differences between the two apps.

Although both applications handle calculations and formulas, each brings different strengths and weaknesses. For instance, Excel is better at handling massive volumes of data, while Google Sheets is better for collaborations. Here’s a full breakdown of both spreadsheets.

Here's five short reasons why you should use Google Sheets: 

• It helps you keep your data up to date
• It uses Google’s artificial intelligence to speed up data analysis
• It uses Google’s suite of security features to keep data secure
• It contains functionalities that users have been long accustomed to using
• It lets users automate various processes, saving time and increasing efficiency 

Why Are Google Sheets Formulas Important? 

Google Sheets formulas are essential for businesses and data analysts because they increase the efficiency of data processing functions such as data entry, modification, and maintenance. These benefits are especially ideal for professionals who must work with large amounts of information; creating spreadsheets without the help of automatic functions can result in slow processes and data errors. 

How Do Google Sheets Formulas Help? 

Google Sheets formulas make it easy for data processing professionals to check their entries for accuracy, quickly make changes, and boost overall productivity. In addition, Google Sheets formulas simplify the spreadsheet creation and maintenance process.

Using formulas in Google Sheets allows users to quickly make calculations and come up with totals of multiple cells, rows, or columns in the spreadsheet. 

What’s the Difference Between a Formula and a Function in Google Sheets? 

It's not difficult to blur the line between a formula and a function in Google Sheets and possibly confuse one for the other. So let's keep it simple: a formula is a calculation in the spreadsheet; a function is a pre-defined calculation.

Users write formulas, which are statements that can be as complex or as simple as the user wants. Functions are already written up and ready to use. Consider functions to be shortcuts!

A List of Google Sheet Formulas

Here is a sampling of a dozen and a half useful Google Sheet formulas. You typically enter formulas manually by starting the syntax with an equal sign (=). Or you can use a function shortcut and save time and typing. Whatever works best for you!

ARRAYFORMULA

This command lets you perform an action on multiple data ranges (e.g., adding the data values from two separate lists). Enter =ARRAYFORMULA in an empty cell or click the cell in question and select “ARRAYFORMULA” from the “GOOGLE” menu located in the “FORMULA” drop-down list.

AVERAGE

This formula gets the average of the data found in a given range of cells. You can get the average of cells in a single row or column or input random cells to be added together. Choose an empty cell and pick “AVERAGE” from the “FUNCTION” drop-down list. Alternately, enter =AVERAGE in a cell.

COUNT

COUNT determines how many cells within a specific range have a value. So, cells B1 through B4, cell B7, and cells B9 through B12 may all have value, and the others are empty. Quickly find the number of cells with values by clicking on an empty cell, choosing “COUNT” from the “FUNCTION” drop-down list, or typing in =COUNT into the cell and selecting the cells in question.

COUNTIF

This formula counts how many cells within a given range have a value provided they meet certain conditions. Pick an empty cell, enter =COUNTIF or choose “COUNTIF” from the “MATH” menu in the “FORMULAS” drop-down list. Then, enter your conditions and ranges between parentheses.

ESCAPE Key

If you need to leave the Formula view and return to Result view quickly, hit the "ESC" key and leave the view without saving any changes.

EXACT

This formula lets you figure out if two values in different cells on your spreadsheet are the same. Click an empty cell and pick “EXACT” from the “TEXT” menu located in the “FORMULA” list, or type =EXACT in the appropriate cell. Next, enter the two cells you want to compare within a set of parentheses and broken up by a comma, then hit “ENTER.” If the values are exact, you will see the word “TRUE” in the cell; otherwise, you will see “FALSE.”

F2

If you wish to copy a portion of a formula to use elsewhere, choose the cell that has the formula, and hit F2. You will then be in the formula.

F4

This key toggles between the absolute and relative range references in Google Sheets formulas. It’s considerably faster than clicking and then typing a dollar sign ($) to switch between references.

IFERROR

You can ascertain the value of a cell if a function returns an error in a given cell. Enter =IFERROR in an empty cell and click it or choose “IFERROR” from the “Logical” menu found in the “FORMULA” drop-down list. Then enter the value you want to populate between a set of parentheses and follow it with a comma. After this, include the value you wish to populate if the function comes back with an error in brackets.

Moving to the Front or End of a Google Sheet Formula

Let’s say you’re working on a very long spreadsheet formula, and you want to quickly move to one end or the other. The UP arrow gets you to the front, and the DOWN arrow takes you to the end.

Quick Fill Down

If you want to copy a formula quickly down a column, double-click on the blue mark in the highlighted cell’s corner. This action copies the cell’s format and contents as far down as the contiguous range in the previous column. Alternately, you can highlight the range you need to fill, then press “CMD + D” if you have a Mac or “CTRL + D” for a PC.

SHIFT and ENTER

Use “SHIFT” + “ENTER” as a shortcut to enter the formula edit view.

SORT

SORT lets you sort cells containing numerical data in ascending order, from lowest to highest. Enter =SORT in an empty cell or click on the cell and select “SORT” from the “FILTER” menu found in the “FORMULA” drop-down list. Enter the desired cell range to be sorted between parentheses.

SPLIT

This command allows you to split text in one cell that’s separated by commas into multiple cells. Enter =SPLIT in an empty cell or click on the cell and choose “SPLIT” found in the “TEXT” menu in the “FORMULA” drop-down. Then, input the cell you would like to split between parentheses and follow it with two quotation marks, separating each element with a comma.

SUM

This formula adds the values within a cell range. First, either enter “=SUM” manually into a cell or choose “SUM” from the “FUNCTION” drop-down list. Then hold the SHIFT key, and choose the cells you wish added together; otherwise, input them between a pair of parentheses.

SUMIF

The SUMIF formula is similar to COUNTIF, but you're looking for the sum of cells that meet given conditions in this case. Enter =SUMIF in an empty cell or pick "SUMIF" from the "MATH" menu, then input a condition (e.g., greater than, less than, or equal to).

TODAY

TODAY lets you enter the current date in a spreadsheet cell. Click an empty cell, choose the “TODAY” option or type in =TODAY(). You can also enter a future or past date by adding or subtracting the number of days from the TODAY formula.

VLOOKUP

The vertical lookup function lets you look up specific data on a spreadsheet. If you’re interested in looking up specific values, you will need to input the search_key, range, index and is_sorted data. Then, enter =VLOOKUP in an empty cell or click the desired cell and choose “VLOOKUP” found on the “LOOKUP” menu in the “FORMULA” drop-down list.

Are You Interested in Becoming a Project Manager? 

Speaking of spreadsheets and data, project managers often find themselves having to work with large volumes of data or managing other people to do the task. Project managers are in high demand, and if you’ve considered project management as a career choice, Simplilearn can help you get your start. 

Simplilearn offers a complete range of project management-related courses and programs. These include:

• Project Management Post Graduate Program
• Project Management Master’s Programs
• Digital Project Manager
• PMP Plus
• PMP Renewal Pack

• Project Management Certification Courses
• PRINCE2 Foundation
• CAPM

• Advanced Courses
• PMP Certification Training
• PRICNE2 Foundation and Practitioner
• PMI-RMP Certification
• PRINCE2 Agile Foundation and Practitioner

Indeed reports that project managers in the United States earn a yearly average of $81,263. S So, if you want a challenging career that offers great compensation and job security, consider project management, and check out Simplilearn's resources to make the journey easier.