Master Google Sheets Formulas: The Complete Cheat Sheet & Guide

Google Sheets is one of the most powerful, accessible tools for managing data, tracking projects, and automating calculations. Whether you are balancing a personal budget, managing a multi-channel inventory, or analyzing sales pipelines, mastering Google Sheets formulas and functions turns raw numbers into actionable insights.

If you are just getting started, our comprehensive [Google Sheets tutorial] covers the core workspace setup. In this guide, we dive deep into the essential formula syntax, top functions across major use cases, workflow-specific formulas for inventory and dates, and troubleshooting techniques when your formulas break.

What Is the Difference Between Formulas and Functions?

While people often use the terms interchangeably, spreadsheets distinguish between a formula and a function:

• Formula: Any custom mathematical or logical expression you build in a cell, starting with an equals sign (=). For example: =A1 + A2 or =B5 * 0.15.
• Function: A built-in, named routine designed to execute a specific calculation or operation automatically. For example: SUM(A1:A10) or AVERAGE(B1:B20).

A formula can consist solely of basic operators, a single function, or multiple nested functions working together.

Core Anatomy of a Spreadsheet Formula

Every formula in Google Sheets follows a strict syntax:

1. Equal Sign (=): Tells the spreadsheet that the text following is an executable command, not a literal string.
2. Function Name: The uppercase or lowercase keyword indicating the operation (e.g., SUM, VLOOKUP, QUERY).
3. Arguments: The values, cell references, or conditions placed inside parentheses, separated by commas (e.g., (A2:A10, ">50")).

Are Google Sheets Formulas the Same as Excel?

A common question when migrating workflows is: are Google Sheets formulas the same as Excel?

For standard calculations, yes. Core mathematical operations, conditional logic (IF, AND, OR), and standard lookups (VLOOKUP, INDEX/MATCH, XLOOKUP) share identical syntax and arguments across both platforms.

However, Google Sheets offers distinct cloud-native functions that do not operate the same way in legacy Excel desktop environments:

• QUERY: Runs structured SQL-like queries directly against cell ranges.
• IMPORTRANGE: Pulls dynamic data live from an entirely separate Google Sheet URL.
• GOOGLEFINANCE: Fetches live and historical stock, currency, and mutual fund data.
• ARRAYFORMULA: Applies a single formula across an entire column or row dynamically without dragging it down manually.

Basic Math Formulas: Sum, Subtraction, Multiplication, and Division

Before tackling complex lookups, establish a solid foundation with standard arithmetic. You can execute basic math using operators directly or by wrapping them in native functions.

Google Sheets Formulas: SUM

To calculate the total of an entire column range:

=SUM(B2:B100)

To sum only values that meet a specific condition, use SUMIF:

=SUMIF(A2:A100, "Electronics", C2:C100)

Google Sheets Formulas: Subtraction

Google Sheets lacks a multi-cell SUBTRACT range function, so standard subtraction relies on the minus operator:

=A2 - B2 - C2

To subtract a series of expenses from a single gross revenue figure:

=A2 - SUM(B2:B10)

Google Sheets Formulas: Multiply

For simple row-by-row multiplication (such as Quantity × Unit Price):

=B2 * C2

To multiply a continuous range of numbers together, use PRODUCT:

=PRODUCT(B2:D2)

Essential Google Sheets Formulas List with Examples

Here is a quick-reference google sheets formulas cheat sheet detailing the most practical functions for daily business and personal operations.

Lookup and Reference Functions

• XLOOKUP (Modern Lookup):

  • Syntax: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode])

  • Example: =XLOOKUP(E2, A2:A100, C2:C100, "Not Found")

  • Why it matters: Searches both left and right, doesn’t break when columns move, and handles errors gracefully.

• VLOOKUP (Vertical Lookup):

  • Syntax: =VLOOKUP(search_key, range, index, [is_sorted])

  • Example: =VLOOKUP(D2, A2:C50, 3, FALSE)

  • Note: Requires the lookup key to be in the first column of the selected range.

• INDEX & MATCH:

  • Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

  • Example: =INDEX(C2:C100, MATCH(E2, A2:A100, 0))

  • Why it matters: A flexible, robust alternative to VLOOKUP for complex, dynamic datasets.

Logical and Conditional Functions

• IF:

  • Syntax: =IF(logical_expression, value_if_true, value_if_false)

  • Example: =IF(C2 >= 70, "Pass", "Fail")

• IFS (Multiple Conditions):

  • Syntax: =IFS(condition1, value1, condition2, value2, ...)

  • Example: =IFS(A2>90, "A", A2>80, "B", A2>70, "C", TRUE, "F")

• COUNTIF / COUNTIFS:

  • Syntax: =COUNTIF(range, criterion)

  • Example: =COUNTIFS(B2:B50, "Pending", C2:C50, ">100")

Text Manipulation Functions

• TRIM: Removes accidental leading, trailing, and repeated spaces from text imports.

  • Example: =TRIM(A2)

• CONCATENATE / TEXTJOIN: Combines text strings from multiple cells into one with a custom delimiter.

  • Example: =TEXTJOIN(" ", TRUE, A2, B2, C2)

• SPLIT: Breaks a single string into distinct columns based on a delimiter (e.g., separating “First Last” by a space).

  • Example: =SPLIT(A2, " ")

Google Sheets Formulas for Dates

Date calculations are crucial for tracking deadlines, calculating employee tenure, monitoring subscription renewals, and building project schedules.

Key Date Functions and Formulas

• Current Date and Timestamp:

  • =TODAY(): Returns today’s dynamic date.

  • =NOW(): Returns the current date and precise time.

• Calculating Days Between Dates:

  • Direct Subtraction: =B2 - A2 (Where B2 is the end date and A2 is the start date).

  • DATEDIF: =DATEDIF(A2, B2, "D") (Can also calculate full months with "M" or years with "Y").

• Adding Business Days (Excluding Weekends):

  • WORKDAY: Calculates project delivery dates based on working days.

  • Example: =WORKDAY(TODAY(), 15) (Finds the date 15 business days from today).

• Calculating Work Days Between Two Dates:

  • NETWORKDAYS: =NETWORKDAYS(A2, B2) (Returns total working days between start and end dates).

Google Sheets Formulas for Inventory Management

Running inventory in Google Sheets requires tracking stock levels, reorder points, warehouse valuations, and safety stock thresholds.

Real-Time Total Stock In Hand

Calculate stock balance by subtracting total sold or damaged units from received purchase orders across data logs:

=SUMIF(Orders!A:A, A2, Orders!C:C) - SUMIF(Sales!A:A, A2, Sales!C:C)

Automatic Reorder Alerts

Keep product supply consistent by generating an automatic warning whenever available units drop below the minimum safety threshold:

=IF(D2 <= C2, "REORDER REQUIRED", "Sufficient Stock")

(Where D2 is Current Stock and C2 is the Minimum Reorder Point).

Total Inventory Valuation

To calculate the gross value of all active warehouse stock:

=SUMPRODUCT(B2:B100, C2:C100)

(Where column B contains Unit Counts and column C contains Unit Cost).

Troubleshooting: Why Are Your Google Sheets Formulas Not Working?

If your formula returns an error code or an unexpected result, use this checklist to diagnose and resolve the issue quickly:

• #N/A Error: The search term does not exist in the referenced dataset. Wrap your formula in =IFNA(formula, "Custom Fallback") to handle missing entries neatly.
• #VALUE! Error: Formula expects a number but received text, or dates are formatted as raw strings. Double-check for non-numeric characters inside number fields.
• #REF! Error: A referenced row or column was deleted, or an array output cannot expand because existing data is blocking the downstream cells. Clear the cells below the formula to allow expansion.
• #NAME? Error: Function name is misspelled (e.g., =SUMM(A1:A10)) or quotes are missing around text strings inside arguments.
• Formulas Showing as Plain Text: Ensure the cell is formatted as Format > Number > Automatic rather than Plain Text, and verify there is no accidental leading space or apostrophe (') before the = sign.
• Calculation Settings: If TODAY() or NOW() is not refreshing, check File > Settings > Calculation and adjust the recalculation frequency to “On change and every minute.”

Downloadable Resources & Cheat Sheets

If you prefer offline reference materials, saving your formulas into an accessible document streamlines team training:

• Create an internal google sheets formulas pdf or quick-reference card summarizing your organization’s core formulas.
• Maintain a dedicated “Formulas Reference” tab inside your shared organizational templates containing verified syntax examples for new team members.

Master Your Spreadsheets Today

Transforming complex datasets into clear, actionable dashboards starts with mastering the fundamentals of Google Sheets formulas and functions. By combining basic arithmetic, modern lookup functions like XLOOKUP, clean date logic, and structured inventory trackers, you eliminate tedious manual recalculations and minimize human error across your workflows.

Explore our comprehensive [Google Sheets tutorial] to expand your spreadsheet skills with pivot tables, automated macros, and dynamic dashboard design.

Frequently Asked Questions (FAQ)

What are the 5 most commonly used formulas in Google Sheets?

The five most widely used functions are:

1. SUM: Adds all numbers in a selected range.
2. COUNT / COUNTA: Tallies numeric or non-empty cells.
3. AVERAGE: Computes the arithmetic mean of a range.
4. IF: Evaluates a condition and returns specific values for TRUE or FALSE.
5. XLOOKUP / VLOOKUP: Locates and retrieves data from corresponding rows across tables.

How do I learn spreadsheet formulas effectively?

Start by mastering basic arithmetic operators (+, -, *, /) and cell referencing. Practice on clean sample datasets with basic conditional logic (IF) before progressing to lookup functions (XLOOKUP, INDEX/MATCH) and dynamic arrays (ARRAYFORMULA, QUERY).

Why is my formula not calculating automatically?

If your formula displays the raw syntax instead of the result, check that the cell format is not set to “Plain Text,” verify that there is no leading space or quote before the = sign, and ensure View > Show > Formulas is unchecked.