The Ultimate Google Sheets Formulas Cheat Sheet | SSP (2024)

Google Sheets offers various capabilities for organizing, analyzing, and visualizing data. However, mastering all the functions and formulas can be overwhelming — especially for beginners. There are plenty of courses online, but they can also be time-consuming. That’s where our ultimate Google Sheets formulas cheat sheet comes in.

Thistool makes your job easier, boosts productivity, and equips you to fully utilize Google Sheets’ formula features. Read on to optimize your spreadsheet creation.

Table of Contents

Download the Google Sheets Cheat Sheet PDF

While we cover everything in this article, be sure to download our Google Sheets cheat sheet PDF. We will cover the same topics touched on here —so you don’t have to worry about missing anything.

If you’re a more experienced user and only want a formula reference,feel free to download this Google Sheets formulasPDF file instead.

Why You Need a Google Sheets Formula Cheat Sheet

Whether you’re a beginner or a pro, a Google Sheets functions cheat sheet is an incredibly useful resource. Here are some ways that this cheat sheet could be helpful to you:

1. Easy Reference

A formula cheat sheet serves as a convenient reference guide. Ours provides a comprehensive list of Google Sheets formulas and their functionalities.

Instead of searching through lengthy documentation or browsing the internet for answers, you can have all the information at your fingertips. It lets you find the formula you need and check its syntax quickly.

2. Quick Troubleshooting

If your formulas are returning errors — they aren’t working quite right — it can be challenging to find the problem. Our cheat sheet is a quick reference to ensure that formulas are organized and nested correctly.

You can reduce the likelihood of mistakes (such as incorrect formula usage or formatting issues) and create accurate, reliable spreadsheets.

3. Efficient Workflow

A formula cheat sheet speeds up and streamlines your productivityand efficiency. Simply refer to the cheat sheet for a clear and brief overview (rather than squandering valuable time trying to remember a specific formula or experimenting with alternative combinations).

Rather than figuring out functions and formulas from scratch, referring to the cheat sheet is fast work.

4. Discover Hidden Gems

Google Sheets has a vast array of formulas. As such, you may not know some lesser-known functions (and combinations) that can significantly enhance your spreadsheet capabilities. A formula cheat sheet can introduce you to these hidden gems by providing an organized compilation of all available formulas.

5. Learn and Expand Your Google Sheets Skills

A formula cheat sheet isn’t just a tool for quick reference:It’s also an educational resource. Using the cheat sheet regularly makes you more familiar with the formulas and their applications. You’ll also discover how to leverage this knowledge to tackle complex tasks or build more sophisticated spreadsheets.

Related: The Best Google Sheets Templates to Improve Workflow

The Most Common Functions Most People Use

Google Sheets has many functions and uses to make your work easier (and your spreadsheet more intuitive). Some of the most popular functions include:

1. IF

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (1)

IF functions perform multiple calculations (such as SUMIF).

The IF function performs conditional logic that’s based on a given condition. It evaluates a logical expression and returns one value if the condition is true (and another value if the condition is false).

The basic syntax of the IF function is as follows:

IF(logical_expression, value_if_true, value_if_false)

The IF function can be nested within itself — or combined with other functions — to create more complex logical tests. Some examples include search functions, such as:

  • AND
  • OR

You can use multiple IF functions to build nested conditions for various scenarios and outcomes.

Note: You may also use the IFS function for this purpose.


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (2)

VLOOKUP is another popular and powerful function in Google Sheets. It searches for a value in the first column of a range (table) and returns a corresponding value from a specified column in the same row.

This function is particularly useful for performing table lookups and retrieving associated data. LOOKUP can manage and analyze databases by retrieving relevant information based on specified criteria. It enables you to search for specific records, retrieve related data, and perform various data manipulations and calculations. It works similarly to the HLOOKUP function.

The syntax for the VLOOKUP function in Google Sheets is as follows:

=VLOOKUP(search_key, range, index, is_sorted)


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (3)

The SUMIF function in Google Sheets allows you to add up values in a range that’s based on a specified condition or criteria. To perform more complex calculations, you can also use logical operators (e.g., less than, greater than) in the criteria.

The syntax for the SUMIF function is as follows:

=SUMIF(range, criteria, [sum_range])

In the above example, we are using SUMIF to calculate only positive integersby using the “less than or equal to operator” and 0 as the criteria.


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (4)

The ARRAYFORMULA function lets you apply a formula to a range of cells rather than entering the formula in each cell. It’s especially useful when working with functions that can’t automatically handle arrays — and eliminates the need to manually drag or copy formulas across a range.

It enables you to perform calculations, apply logical tests, and manipulate text —all within a single formula. This is especially useful if you’re working with large datasets.

The syntax of ARRAYFORMULA is simple:



The Ultimate Google Sheets Formulas Cheat Sheet | SSP (5)

CONCATENATE is used to combine multiple text strings into a single string. It takes two or more text arguments and joins them in the order provided. This function is invaluable when merging data or creating customized labels, reports, or summaries.

The syntax of the CONCATENATE function is:

=CONCATENATE(text1, [text2, …])


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (6)

The LEN function calculates the number of characters in a given cell, including letters, numbers, spaces, and special characters. The LEN function can also be used to manipulate text, extract substrings, dynamically calculate lengths, and perform various other operations on strings in Google Sheets.

The syntax of the LEN function is straightforward:


In this syntax, theTEXT argument is the cell or text string you want to measure. In the image above, the TEXT argument is under A2. Let’s explain this further.

You can see that the formula: =LEN(A2)gives the result 17, which is the number of characters in the text Avengers: Endgame. This is a simple example of what the LEN function could be used for.


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (7)

The IMPORTRANGE function in Google Sheets allows you to import data from one spreadsheet to another. It enables you to establish a connection between different sheets, even if they are located in separate files or folders.

Using IMPORTRANGE can fetch smaller sections of specific data or an entire sheet of cells from a source sheet, then incorporate it into your destination sheet.

The syntax for the IMPORTRANGE function in Google Sheets is as follows:

=IMPORTRANGE(spreadsheet_url, range_string)


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (8)

The QUERY function in Google Sheets allows users to extract and manipulate data from a specified range based on queries similar to those you would use in databases (SQL queries).

It provides an efficient way to filter, sort, and aggregate data within your spreadsheet. Use it to extract specific columns or rows from your data range by specifying the desired fields in the query string. You’ll be able to retrieve only the relevant information for your analysis.

The syntax of the QUERY function is as follows:

=QUERY(data, query, [headers])


The Ultimate Google Sheets Formulas Cheat Sheet | SSP (9)

The INDEX function in Google Sheets is a versatile function that allows you to retrieve the value of a cell within a specified range based on its row and column numbers. It provides flexibility in extracting specific data points from a range.

INDEX is commonly used in combination with other functions for advanced data retrieval and analysis (like MATCH, in the example above).

By using the INDEX function in combination with array formulas or other functions, you can retrieve entire rows or columns from a range.

Note: INDEX is most commonly used together with the MATCH function to perform advanced lookups that VLOOKUP and HLOOKUP aren’t able to do.

The syntax of the INDEX function is:

=INDEX(range, row_num, [column_num])

Related:Excel vs. Google Sheets

Table for Google Sheets Formula Cheat Sheet

The following table shows the functions in Google Sheets and their syntax:

SUMCalculates the sum of a range of cellsSUM(range1, [range2, …])
AVERAGECalculates the average of a range of cellsAVERAGE(range1, [range2, …])
COUNTCounts the number of cells in a rangeCOUNT(range1, [range2, …])
MAXFinds the maximum value in a range of cellsMAX(range1, [range2, …])
MINFinds the minimum value in a range of cellsMIN(range1, [range2, …])
IFPerforms conditional logic based on a given conditionIF(logical_expression, value_if_true, value_if_false)
VLOOKUPSearches for a value in the first column of a rangeVLOOKUP(search_key, range, index, [is_sorted])
CONCATENATECombines multiple strings into a single stringCONCATENATE(string1, [string2, …])
SUBSTITUTEReplaces specific text within a stringSUBSTITUTE(text, old_text, new_text, [occurrence])
DATECreates a date value from year, month, and day componentsDATE(year, month, day)
TODAYReturns the current dateTODAY()
LENCalculates the length of a text stringLEN(text)
LEFTExtracts a specified number of characters from the beginning of a text stringLEFT(text, num_chars)
RIGHTExtracts a specified number of characters from the end of a text stringRIGHT(text, num_chars)
MIDExtracts a substring from a text stringMID(text, start, num_chars)
CONCATCombines multiple strings into a single stringCONCAT(string1, [string2, …])
COUNTIFCounts the number of cells in a range that meet a given criterionCOUNTIF(range, criterion)
SUMIFSums the values in a range that meet a given criterionSUMIF(range, criterion, [sum_range])
IFERRORReturns a value if a formula generates an errorIFERROR(value, [value if error])
OFFSETReturns a range reference that is offset from a given referenceOFFSET(reference, rows, cols, [height], [width])
ROUNDRounds a number to a specified number of decimal placesROUND(number, num_digits)
TRIMRemoves leading and trailing spaces from a text stringTRIM(text)
FINDFinds the position of a specified text within another text stringFIND(find_text, within_text, [start_num])
LEFTBExtracts a specified number of bytes from the beginning of a text stringLEFTB(text, num_bytes)
RIGHTBExtracts a specified number of bytes from the end of a text stringRIGHTB(text, num_bytes)
MIDBExtracts a substring from a text string using bytesMIDB(text, start_byte, num_bytes)
SUBSTITUTEReplaces specific text within a stringSUBSTITUTE(text, old_text, new_text, [occurrence])
REGEXEXTRACTExtracts a matching substring from a text string using a regular expressionREGEXEXTRACT(text, regular_expression)
REGEXREPLACEReplaces matching substrings within a text string using a regular expressionREGEXREPLACE(text, regular_expression, replacement)
SPLITSplits a text string into separate substrings based on a delimiterSPLIT(text, delimiter, [split_by_each])
TRANSPOSETransposes a range of cells (converts rows into columns and vice versa)TRANSPOSE(array)

6 Time-Saving Shortcutsfor Google Sheets Formulas

Here are some tips, tricks, and shortcuts to enhance your experience when using functions in Google Sheets:

1. Autocomplete

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (10)

Instead of manually inputting your formulas, try an autocomplete function. Begin typing a function name in a cell, then press “Tab” to autocomplete the function (or display a list of available functions that match your input). Sheets will suggest filling an entire row or column with a function to save you time.

2. Use Keyboard Shortcuts

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (11)

Using keyboard shortcuts will significantly increase the speed of creating spreadsheets. You can find a list of explanations for all the Google Sheets keyboard shortcutssimply by pressing Ctrl + / (⌘+ / for MacOS devices).

3. Select Ranges

When specifying a range as an argument, you can use your mouse or the Shift+Arrow keys to quickly select a range of cells.

For example, Shift + Down Arrowwill select the next cell in a column. If you use Ctrl + Shift + Down Arrow,it will select all of the rows in a column until the last contiguous cell.

4. Range Names in Functions

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (12)

Instead of manually typing cell references in your formulas, consider using named ranges. Named ranges make formulas more readable and easier to maintain. You can create and manage named ranges from the “Data” menu under “Named ranges.”

5. Function Dragging

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (13)

When applying a function to multiple cells, drag the fill handle (a small blue square in the bottom-right corner of the selected cell) to populate the formula in the adjacent cells automatically. This is a quick way to replicate your formula across a range.

6. Function Nesting

The Ultimate Google Sheets Formulas Cheat Sheet | SSP (14)

Function nesting can perform complex calculations. You can use the result of one function as an argument in another function, allowing you to build more powerful and customized formulas.

An example would be to use another function inside an IF function (like if the result is TRUE), then perform another function. Like in the example above, the function is finding the SUM of N8 and O8, but only if N8 is greater than 15.

Related:The Best Online Google Sheets Courses

Wrapping Up

Our Google Sheets cheat sheet helps ensure accurate and efficient data processing. And these practical insights and tips will automate tasks, visualize data, and enhance how you use the functions in Google Sheets.

Be sure to check out our comprehensive guide to charts for Google Sheets, too!

Want to skip the basics of Google Sheets but still want access to powerful spreadsheets? Use code “SSP” to save 50% on our entire premium template library.

Get Premium Templates


  • How To Auto-Refresh Google Sheets Formulas (Updates Every 1-Minute)
  • How To Use Formulas In Google Sheets: Ultimate Guide
  • How To Copy Values and Not Formulas in Google Sheets
The Ultimate Google Sheets Formulas Cheat Sheet | SSP (2024)
Top Articles
Latest Posts
Article information

Author: Francesca Jacobs Ret

Last Updated:

Views: 6095

Rating: 4.8 / 5 (68 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Francesca Jacobs Ret

Birthday: 1996-12-09

Address: Apt. 141 1406 Mitch Summit, New Teganshire, UT 82655-0699

Phone: +2296092334654

Job: Technology Architect

Hobby: Snowboarding, Scouting, Foreign language learning, Dowsing, Baton twirling, Sculpting, Cabaret

Introduction: My name is Francesca Jacobs Ret, I am a innocent, super, beautiful, charming, lucky, gentle, clever person who loves writing and wants to share my knowledge and understanding with you.