Google Sheets Formulas

Google Sheets formulas allow you to perform calculations, analyze data, and automate repetitive tasks directly inside a spreadsheet. From simple totals to more advanced logic, formulas are what turn a spreadsheet into a powerful data tool.

This appendix presents a curated set of commonly used Google Sheets formulas. Each formula is demonstrated with a practical example so you can quickly understand how it works and what kind of result it produces.

Use this appendix as a quick reference while working through exercises or projects; it’s designed to help you recall formulas without needing to memorize every detail.

Formula basics (quick refresher)

Google Sheets formulas let you perform calculations and dynamically manipulate data. Every formula begins with an equals sign (=), which tells the spreadsheet to evaluate the expression in the cell.

Formulas rely on cell references, which indicate the data they use. There are three main types:

• Relative references like A1 change automatically if you copy the formula to another cell.

• Absolute references like $A$1 remain fixed, even when copied.

• Mixed references like $A1 or A$1 lock either the row or the column while letting the other adjust.

Functions are a common way to use formulas. The general syntax looks like this:

Here, FUNCTION_NAME is the name of the operation (like SUM or AVERAGE), and the arguments tell it what data to use.

A simple example is a formula that sums a range of numbers:

This adds all values in cells A2 through A6 and displays the result in the cell where the formula is entered. One advantage of using formulas is that the result updates automatically whenever the input values change, saving time and reducing errors compared to manual calculations.

Mathematical and aggregation formulas

Mathematical and aggregation formulas help you summarize and analyze data quickly. Whether you need totals, averages, or counts, these formulas let you process information without manual calculations, saving time and reducing errors.

One of the most common formulas is SUM which we have seen above, but to calculate the average, use AVERAGE:

It computes the mean of the values in C2 through C7, helping you track performance metrics or trends over time.

Finding extremes in your data is easy with MIN and MAX:

MIN returns the smallest number, while MAX returns the largest. For example, you can quickly identify the lowest and highest priced in items in a dataset.

Counting values is just as simple. COUNT tallies numeric entries, while COUNTA counts all non-empty cells:

In the example above, the goal of this specific formula is to compare two different lists to ensure they are "in sync."

• Column B (COUNTA): Counts the "Inventory" (Text).

• Column A (COUNT): Counts the "Price Tags" (Numbers).

If you have 6 items (Column B) but only 5 prices (Column A), the formula returns FALSE. It’s like a digital smoke detector for your data; it tells you immediately if you forgot to enter a number somewhere.

This distinction matters when working with mixed data types, numbers, text, or blank cells.

These formulas are dynamic: any change in the underlying data automatically updates the result. This makes your spreadsheet more reliable and reduces the need for repetitive manual calculations.

Logical formulas

Logical formulas let you make decisions in your spreadsheet based on conditions. They are essential for automating tasks like flagging values, categorizing data, or handling errors.

The most common logical formula is IF, which checks a condition and returns one value if the condition is true and another if it is false. For example:

This formula checks if the value in A2 is greater than 50. If it is, it returns "Pass"; otherwise, it returns "Fail". Using IF can help you categorize exam scores, sales targets, or any data with thresholds.

You can combine multiple conditions with AND and OR. For instance:

What it means:

• Excel checks both conditions:

  • Is B2 > 50?

  • Is C2 > 50?

• If both are true, then result is "Qualified"

• If even one is false, then result is "Not Qualified"

• You only pass if both scores are above 50.

To handle errors gracefully, use IFERROR:

If the formula D2/E2 produces an error (like dividing by zero), it will return "Error" instead of showing a warning. This keeps your spreadsheet clean and easier to read.

Logical formulas can also be nested, allowing more complex decisions in a single cell. They make your spreadsheets dynamic and responsive, automatically updating results as the underlying data changes.

Text and string formulas

Text formulas help you manipulate and clean text data, which is especially useful when working with names, codes, or other non-numeric information. These formulas let you combine, split, or extract text efficiently without manual editing.

One common formula is CONCAT (or CONCATENATE), which joins text from multiple cells:

This example combines the first name in A2 and the last name in B2, separated by a space. It’s useful for creating full names, addresses, or labels from separate columns.

To measure or manipulate text length, you can use LEN:

This formula returns the number of characters in C2, including spaces. It helps check for inconsistencies or overly long entries.

For extracting parts of text, use LEFT or RIGHT:

LEFT returns the first characters, while RIGHT returns the last characters. These are helpful for parsing codes, IDs, or standardized formats.

Extra spaces can cause issues in analysis, but TRIM removes them:

This formula removes all leading, trailing, and extra spaces between words, leaving only single spaces. It’s essential for cleaning imported or messy data.

Finally, TEXT lets you format numbers or dates as text:

This converts a date or number into a readable text format, making it easier to display or combine with other strings.

Date and time formulas

Date and time formulas let you work efficiently with time-based data. They are useful for calculating durations, tracking deadlines, or displaying dynamic timestamps.

The simplest formulas are TODAY and NOW:

TODAY returns the current date, while NOW returns the current date and time. Both formulas update automatically each day or whenever the spreadsheet recalculates, which is helpful for deadlines or daily reports.

To create a specific date, use the DATE formula:

This produces a date value for January 27, 2026. It is more reliable than typing dates manually, ensuring consistent formatting across your spreadsheet.

Calculating the difference between dates is done with DATEDIF:

This returns the number of days between the dates in A2 and B2. You can also calculate months ("M") or years ("Y") depending on your needs. DATEDIF is particularly useful for age calculations, project timelines, or membership durations.

Date and time formulas can be combined with other formulas. For example, you can use IF with TODAY to flag overdue tasks:

This formula automatically labels tasks based on whether their due date has passed.

Lookup and reference formulas

Lookup formulas help you find and retrieve data from large tables or ranges. They are essential for matching values, pulling records, or consolidating information from different sheets.

One of the most common lookup formulas is VLOOKUP:

This searches for the value in A2 within the first column of the range B2:D4 and returns the corresponding value from the third column. The FALSE argument ensures an exact match. It’s useful for finding prices, employee information, or any value tied to a key identifier.

HLOOKUP works similarly but searches horizontally:

It looks for the value in B1 across the first row of B2:F5 and returns the value from the third row. Use HLOOKUP when your data is organized in rows instead of columns.

The newer XLOOKUP combines the power of vertical and horizontal lookups:

XLOOKUP searches for the value in A2 within B2:B10 and returns the matching value from C2:C10. If no match is found, it returns "Not Found". This formula is more flexible and easier to use than VLOOKUP or HLOOKUP.

For more complex lookups, INDEX and MATCH can be combined:

MATCH finds the position of A2 in B2:B10, and INDEX returns the corresponding value from C2:C10. The 0 means: “only return a result if the value matches exactly.” This approach allows dynamic column referencing and works even when the lookup column is not the first one.

Lookup formulas save time and reduce errors when working with large datasets. They make spreadsheets more dynamic because results update automatically whenever the source data changes.

Conditional aggregation formulas

Conditional aggregation formulas let you perform calculations based on specific criteria. They are useful for summarizing data selectively, such as counting only certain items, summing specific sales, or filtering rows that meet a condition.

The SUMIF formula adds values in a range that meet a condition:

This adds all values in B2:B6 where the corresponding cell in A2:A6 is greater than 100. It’s ideal for calculating totals for orders above a certain amount or sales exceeding a target.

COUNTIF counts how many cells meet a specific condition:

This counts all cells in C2:C6 that contain the word "Completed". Use it to track progress, mark completed tasks, or count occurrences of a specific value.

AVERAGEIF calculates the average of values that meet a condition:

It averages all values in E2:E6 where the corresponding D2:D6 value is greater than 50. This helps analyze performance metrics for only the relevant subset of data.

FILTER extracts all rows that meet certain criteria:

This returns all rows in A2:C6 where column B is greater than 100. FILTER is powerful for creating dynamic views or dashboards without manually copying rows.

Conditional aggregation formulas make your spreadsheets smarter by performing calculations automatically on filtered data, saving time and reducing errors.

Common formula errors and debugging

Even in well-structured spreadsheets, formulas can still produce errors. Understanding what these errors mean and how to fix them helps keep your sheets accurate, readable, and professional.

• #DIV/0! — Division by Zero: This error appears when a formula tries to divide a number by zero or an empty cell. For example, if B2 is zero or blank, a division formula will return #DIV/0!. Use IFERROR to show a friendly message instead of the error:

• #N/A — Value not found: This error occurs when a lookup formula cannot find a matching value. For example, if the value in A2 does not exist in the lookup range B2:D10, the formula returns #N/A. Wrap the formula with IFERROR or use XLOOKUP with a custom message:

• #VALUE! — Wrong data type: This error happens when a formula receives the wrong type of input. For example, adding text to a number ("Apple" + 5) triggers #VALUE!. Ensure all inputs match the formula’s expected data type (numbers with numbers, text with text).

• #REF! — Invalid cell reference: This error appears when a formula refers to a cell that has been deleted or no longer exists. For example, deleting B2 or C2 while a formula still references them will cause #REF!. Check and update all referenced cells to make sure they still exist.

Errors aren’t always bad; they often point directly to what needs fixing. Learning to read them saves time and prevents silent mistakes in your data.

Closing notes and tips

This appendix is designed as a quick reference for Google Sheets formulas. It is not meant to replace hands-on practice, but to help you recall formulas and understand how they behave when applied to real data.

When using this guide:

• Refer back to the relevant section whenever you encounter a formula you’re unsure about.

• Combine formulas creatively. Google Sheets becomes much more powerful when you layer functions like IF, SUMIF, and FILTER.

• Use screenshots or small test datasets to see formulas in action before applying them to large datasets.

• Pay attention to common errors and learn to handle them proactively with tools like IFERROR.

By revisiting this appendix regularly, you’ll become faster and more confident in creating dynamic, automated spreadsheets. Think of it as a toolkit that supports your projects, rather than a list you need to memorize.