Google Sheets Formulas Cheat Sheet: Become a SpreadSheet Master!

Google Sheets is a powerful tool for organizing, analyzing, and manipulating data. One of the key features that makes Google Sheets so versatile is its extensive library of formulas. Whether you're a beginner or an experienced user, having a handy Google Sheets formulas cheat sheet can save you time and help you work more efficiently. In this article, we'll explore some of the most useful formulas in Google Sheets and provide examples to help you master your spreadsheets.

Google Sheets AI Formula Generator | Formula Builder | Anakin.ai

Want to effortlessly create Google Sheets Formulas? This AI tool is here to help you easily create Google Sheets Formula Generator with ease!

Sam AltwomanSam Altwoman2

1. Basic Arithmetic Formulas (Google Sheets Formulas Cheat Sheet)

Let's start with the basics. Google Sheets supports all the standard arithmetic operations, such as addition, subtraction, multiplication, and division. Here are some examples:

• =A1+B1: Adds the values in cells A1 and B1
• =A1-B1: Subtracts the value in cell B1 from the value in cell A1
• =A1*B1: Multiplies the values in cells A1 and B1
• =A1/B1: Divides the value in cell A1 by the value in cell B1

You can also use parentheses to control the order of operations, just like in standard arithmetic. For example:

• =(A1+B1)*C1: Adds the values in cells A1 and B1, then multiplies the result by the value in cell C1

Example 1: Calculating Total Sales

Suppose you have a spreadsheet with sales data for different products. You have the quantity sold in column A and the price per unit in column B. To calculate the total sales for each product, you can use the multiplication formula:

• In cell C1, enter the formula =A1*B1
• Copy the formula down the column to calculate the total sales for each product

Google Sheets AI Formula Generator | Formula Builder | Anakin.ai

Want to effortlessly create Google Sheets Formulas? This AI tool is here to help you easily create Google Sheets Formula Generator with ease!

Sam AltwomanSam Altwoman2

2. Text Manipulation Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets also offers a variety of formulas for working with text data. Here are a few commonly used text formulas:

• =CONCATENATE(A1, " ", B1): Combines the text in cells A1 and B1, with a space in between
• =LEFT(A1, 3): Extracts the first 3 characters from the text in cell A1
• =RIGHT(A1, 3): Extracts the last 3 characters from the text in cell A1
• =MID(A1, 2, 4): Extracts 4 characters from the text in cell A1, starting from the 2nd character
• =LEN(A1): Returns the number of characters in the text in cell A1

Example 2: Extracting First and Last Names

Let's say you have a spreadsheet with full names in column A, and you want to separate them into first and last names. You can use the LEFT, RIGHT, and FIND formulas to accomplish this:

• In cell B1, enter the formula =LEFT(A1, FIND(" ", A1)-1) to extract the first name
• In cell C1, enter the formula =RIGHT(A1, LEN(A1)-FIND(" ", A1)) to extract the last name
• Copy the formulas down the columns to extract the first and last names for each person

3. Date and Time Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets has a set of formulas for working with dates and times. Here are a few useful ones:

• =TODAY(): Returns the current date
• =NOW(): Returns the current date and time
• =DATE(2023, 6, 15): Returns the date June 15, 2023
• =YEAR(A1): Extracts the year from the date in cell A1
• =MONTH(A1): Extracts the month from the date in cell A1
• =DAY(A1): Extracts the day from the date in cell A1
• =WEEKDAY(A1): Returns the day of the week for the date in cell A1 (1 = Sunday, 2 = Monday, etc.)

Example 3: Calculating Age from Date of Birth

Suppose you have a spreadsheet with dates of birth in column A, and you want to calculate the current age of each person. You can use the DATEDIF formula to do this:

• In cell B1, enter the formula =DATEDIF(A1, TODAY(), "Y") to calculate the age in years
• Copy the formula down the column to calculate the age for each person

4. Conditional Formulas (Google Sheets Formulas Cheat Sheet)

Conditional formulas allow you to perform different calculations based on certain conditions. The most commonly used conditional formula in Google Sheets is IF. Here's the basic syntax:

• =IF(condition, value_if_true, value_if_false)

For example:

• =IF(A1>10, "Greater than 10", "Less than or equal to 10"): Returns "Greater than 10" if the value in cell A1 is greater than 10, otherwise returns "Less than or equal to 10"

You can also nest multiple IF statements to create more complex conditions:

• =IF(A1>10, "Greater than 10", IF(A1>5, "Greater than 5", "Less than or equal to 5"))

Example 4: Applying Conditional Formatting

Let's say you want to highlight cells in column A that contain values greater than 100. You can use conditional formatting with the IF formula to achieve this:

1. Select the range of cells you want to format (e.g., A1:A100)
2. Go to Format > Conditional formatting
3. In the "Format cells if" dropdown, select "Custom formula is"
4. Enter the formula =A1>100
5. Click "Done" to apply the formatting

Now, any cells in column A with values greater than 100 will be highlighted based on your chosen formatting.

5. Lookup and Reference Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets offers several formulas for looking up and referencing data from other cells or sheets. Here are a few commonly used ones:

• =VLOOKUP(search_key, range, index, [is_sorted]): Searches for a value in the leftmost column of a range and returns the corresponding value from a specified column
• =HLOOKUP(search_key, range, index, [is_sorted]): Similar to VLOOKUP, but searches for a value in the top row of a range and returns the corresponding value from a specified row
• =INDEX(range, row, [column]): Returns the value at the specified row and column of a range
• =MATCH(search_key, range, [search_type]): Returns the relative position of a value in a range

Example 5: Using VLOOKUP to Retrieve Product Prices

Suppose you have a spreadsheet with product names in column A and their corresponding prices in another sheet named "Prices," where the product names are in column A and the prices are in column B. You can use the VLOOKUP formula to retrieve the prices in your main sheet:

• In cell B1 of your main sheet, enter the formula =VLOOKUP(A1, Prices!A:B, 2, FALSE)
• Copy the formula down the column to retrieve the prices for each product

Here are 10 more examples of useful Google Sheets formulas to add to your cheat sheet, along with detailed explanations to help you master your spreadsheets:

6. COUNTIF (Google Sheets Formulas Cheat Sheet)

The COUNTIF formula allows you to count the number of cells in a range that meet a specific criterion. Here's the syntax:

• =COUNTIF(range, criterion)

For example:

• =COUNTIF(A1:A100, ">50"): Counts the number of cells in the range A1:A100 that are greater than 50
• =COUNTIF(B1:B100, "Apples"): Counts the number of cells in the range B1:B100 that contain the text "Apples"

Example 6: Counting the Number of Sales Above a Threshold

Suppose you have a spreadsheet with sales data, and you want to count the number of sales that exceed $1,000. You can use the COUNTIF formula to achieve this:

• In cell B1, enter the formula =COUNTIF(A1:A100, ">1000")
• The result will show the number of sales in the range A1:A100 that are greater than $1,000

7. SUMIF (Google Sheets Formulas Cheat Sheet)

The SUMIF formula allows you to sum the values in a range that meet a specific criterion. Here's the syntax:

• =SUMIF(range, criterion, [sum_range])

For example:

• =SUMIF(A1:A100, ">50", B1:B100): Sums the values in the range B1:B100 where the corresponding values in the range A1:A100 are greater than 50
• =SUMIF(A1:A100, "Apples", B1:B100): Sums the values in the range B1:B100 where the corresponding values in the range A1:A100 contain the text "Apples"

Example 7: Calculating Total Sales for a Specific Product

Let's say you have a spreadsheet with product names in column A and their corresponding sales amounts in column B. You want to calculate the total sales for a specific product, such as "Apples." You can use the SUMIF formula to do this:

• In cell C1, enter the formula =SUMIF(A1:A100, "Apples", B1:B100)
• The result will show the total sales for "Apples" based on the data in the ranges A1:A100 and B1:B100

8. AVERAGEIF (Google Sheets Formulas Cheat Sheet)

The AVERAGEIF formula allows you to calculate the average of values in a range that meet a specific criterion. Here's the syntax:

• =AVERAGEIF(range, criterion, [average_range])

For example:

• =AVERAGEIF(A1:A100, ">50", B1:B100): Calculates the average of the values in the range B1:B100 where the corresponding values in the range A1:A100 are greater than 50
• =AVERAGEIF(A1:A100, "Apples", B1:B100): Calculates the average of the values in the range B1:B100 where the corresponding values in the range A1:A100 contain the text "Apples"

Example 8: Calculating the Average Price of a Specific Product

Suppose you have a spreadsheet with product names in column A and their corresponding prices in column B. You want to calculate the average price of a specific product, such as "Oranges." You can use the AVERAGEIF formula to achieve this:

• In cell C1, enter the formula =AVERAGEIF(A1:A100, "Oranges", B1:B100)
• The result will show the average price of "Oranges" based on the data in the ranges A1:A100 and B1:B100

9. COUNTIFS (Google Sheets Formulas Cheat Sheet)

The COUNTIFS formula is an extension of the COUNTIF formula, allowing you to count the number of cells that meet multiple criteria across different ranges. Here's the syntax:

• =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])

For example:

• =COUNTIFS(A1:A100, ">50", B1:B100, "<100"): Counts the number of cells where the values in the range A1:A100 are greater than 50 and the corresponding values in the range B1:B100 are less than 100

Example 9: Counting the Number of Sales Within a Specific Range

Let's say you have a spreadsheet with sales data, and you want to count the number of sales between $500 and $1,000. You can use the COUNTIFS formula to do this:

• In cell C1, enter the formula =COUNTIFS(A1:A100, ">500", A1:A100, "<1000")
• The result will show the number of sales in the range A1:A100 that are between $500 and $1,000

10. SUMIFS (Google Sheets Formulas Cheat Sheet)

The SUMIFS formula is an extension of the SUMIF formula, allowing you to sum values in a range that meet multiple criteria across different ranges. Here's the syntax:

• =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

For example:

• =SUMIFS(C1:C100, A1:A100, ">50", B1:B100, "<100"): Sums the values in the range C1:C100 where the corresponding values in the range A1:A100 are greater than 50 and the corresponding values in the range B1:B100 are less than 100

Example 10: Calculating Total Sales for a Specific Product Within a Date Range

Suppose you have a spreadsheet with product names in column A, sales dates in column B, and sales amounts in column C. You want to calculate the total sales for a specific product, such as "Bananas," within a specific date range. You can use the SUMIFS formula to achieve this:

• In cell D1, enter the formula =SUMIFS(C1:C100, A1:A100, "Bananas", B1:B100, ">2023-01-01", B1:B100, "<2023-12-31")
• The result will show the total sales for "Bananas" within the specified date range based on the data in the ranges A1:A100, B1:B100, and C1:C100

11. IFERROR (Google Sheets Formulas Cheat Sheet)

The IFERROR formula allows you to handle errors in your formulas by providing an alternative value or action when an error occurs. Here's the syntax:

• =IFERROR(value, value_if_error)

For example:

• =IFERROR(A1/B1, "Division by zero"): Returns the result of dividing the value in cell A1 by the value in cell B1, but if a division by zero error occurs, it returns the text "Division by zero"

Example 11: Handling Division by Zero Errors

Let's say you have a spreadsheet with numbers in column A and column B, and you want to divide the values in column A by the corresponding values in column B. However, some cells in column B might contain zeros, which would result in division by zero errors. You can use the IFERROR formula to handle these errors:

• In cell C1, enter the formula =IFERROR(A1/B1, "N/A")
• Copy the formula down the column to divide the values in column A by the corresponding values in column B, but if a division by zero error occurs, it will display "N/A" instead

12. VLOOKUP with IFERROR (Google Sheets Formulas Cheat Sheet)

Combining the VLOOKUP formula with the IFERROR formula allows you to handle cases where the lookup value is not found in the lookup range. Here's an example:

• =IFERROR(VLOOKUP(A1, B1:C100, 2, FALSE), "Not found")

This formula looks up the value in cell A1 in the range B1:C100 and returns the corresponding value from the second column of the range. If the lookup value is not found, it returns the text "Not found" instead of an error.

Example 12: Looking Up Product Prices with Error Handling

Suppose you have a spreadsheet with product names in column A and another sheet named "Prices" with product names in column A and their corresponding prices in column B. You want to look up the prices of the products in your main sheet, but some products might not be listed in the "Prices" sheet. You can use the VLOOKUP formula with IFERROR to handle these cases:

• In cell B1 of your main sheet, enter the formula =IFERROR(VLOOKUP(A1, Prices!A:B, 2, FALSE), "Price not available")
• Copy the formula down the column to look up the prices for each product, but if a product is not found in the "Prices" sheet, it will display "Price not available" instead of an error

13. UNIQUE (Google Sheets Formulas Cheat Sheet)

The UNIQUE formula allows you to extract unique values from a range or array. Here's the syntax:

• =UNIQUE(range)

For example:

• =UNIQUE(A1:A100): Returns a list of unique values from the range A1:A100

Example 13: Extracting Unique Product Names

Let's say you have a spreadsheet with product names in column A, and you want to create a list of unique product names. You can use the UNIQUE formula to achieve this:

• In cell B1, enter the formula =UNIQUE(A1:A100)
• The result will be a list of unique product names from the range A1:A100

14. FILTER (Google Sheets Formulas Cheat Sheet)

The FILTER formula allows you to filter a range or array based on one or more criteria. Here's the syntax:

• =FILTER(range, condition1, [condition2, ...])

For example:

• =FILTER(A1:B100, B1:B100>50): Returns the values from the range A1:B100 where the corresponding values in the range B1:B100 are greater than 50

Example 14: Filtering Sales Data Based on a Threshold

Suppose you have a spreadsheet with product names in column A and sales amounts in column B. You want to create a list of products with sales greater than $1,000. You can use the FILTER formula to do this:

• In cell C1, enter the formula =FILTER(A1:A100, B1:B100>1000)
• The result will be a list of products from the range A1:A100 where the corresponding sales amounts in the range B1:B100 are greater than $1,000

15. QUERY (Google Sheets Formulas Cheat Sheet)

The QUERY formula allows you to perform SQL-like queries on your spreadsheet data. It's a powerful formula that can be used for filtering, sorting, and aggregating data. Here's a basic example of the syntax:

• =QUERY(data, query, [headers])

For example:

• =QUERY(A1:C100, "SELECT A, SUM(B) GROUP BY A", 1): Groups the data in the range A1:C100 by the values in column A and calculates the sum of the values in column B for each group, with headers in the first row

Example 15: Summarizing Sales Data by Product

Let's say you have a spreadsheet with product names in column A, sales dates in column B, and sales amounts in column C. You want to create a summary of total sales for each product. You can use the QUERY formula to achieve this:

• In cell D1, enter the formula =QUERY(A1:C100, "SELECT A, SUM(C) GROUP BY A LABEL SUM(C) 'Total Sales'", 1)
• The result will be a summary table with product names and their corresponding total sales, based on the data in the range A1:C100

Conclusion

This Google Sheets formulas cheat sheet, now expanded with 10 more examples, covers an even wider range of formulas that can help you work more efficiently and effectively with your spreadsheet data. From conditional counting and summing to error handling, unique value extraction, filtering, and powerful querying, these formulas offer a wealth of possibilities for data analysis and manipulation.

As you continue to explore and apply these formulas in your own work, remember that practice is key to mastering them. Don't hesitate to experiment with different combinations and variations to find the best solutions for your specific needs.

Keep this cheat sheet handy as a reference, and feel free to add your own examples and notes as you discover new ways to use these formulas. With time and experience, you'll develop a robust toolkit of Google Sheets formulas that will make you a true spreadsheet pro.

Happy formula-building!

Google Sheets AI Formula Generator | Formula Builder | Anakin.ai

Want to effortlessly create Google Sheets Formulas? This AI tool is here to help you easily create Google Sheets Formula Generator with ease!

Sam AltwomanSam Altwoman2

FAQ: Google Sheets Formulas

What are the most useful formulas in Google Sheets?

Some of the most useful formulas in Google Sheets include:

• VLOOKUP: Looks up and retrieves data from a specific column in a table
• ARRAYFORMULA: Applies a formula to an entire range of cells
• SUM: Adds up a range of cells
• IF: Performs a logical test and returns a value based on the result
• IMPORTRANGE: Imports data from another Google Sheets spreadsheet

What are the basic math formulas for Google Sheets?

The basic math formulas in Google Sheets include:

• SUM: Adds up a range of cells
• AVERAGE: Calculates the average of a range of cells
• COUNT: Counts the number of cells that contain numbers
• MAX: Returns the highest value in a range of cells
• MIN: Returns the lowest value in a range of cells

How to write Google Sheets formulas?

To write a formula in Google Sheets:

1. Click on the cell where you want to enter the formula
2. Type an equals sign (=) to begin the formula
3. Enter the formula using cell references, functions, and operators
4. Press Enter to calculate the result

What is <> in Google Sheet formula?

In Google Sheets formulas, the <> operator means "not equal to." It is used to compare two values and returns TRUE if they are not equal, and FALSE if they are equal.