Skip to main content

A List of Google Sheet Formulas

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.