10 Advanced Google Sheets Formulas for Data Analysis (Cheat Sheet)

Working with large datasets in Google Sheets often becomes slow and tedious when relying on basic arithmetic functions. Mastering advanced Google Sheets formulas for data analysis allows you to automate repetitive tasks, build interactive dashboards, and manipulate complex data pipelines without writing code.

This comprehensive cheat sheet covers the top 10 formulas essential for efficient spreadsheet analysis, complete with real-world business scenarios, syntax breakdowns, and copy-paste examples.

---

Our Practice Dataset

To demonstrate each formula, consider the following sales transaction table located in range A1:E7:

---

1. QUERY: The Swiss Army Knife of Data Manipulation

The QUERY function runs pseudo-SQL statements on your sheet data to filter, sort, and aggregate records simultaneously.

• Syntax: =QUERY(data, query, [headers])
• Scenario: Extract all transactions in the "East" region for "Electronics" exceeding $10,000, sorted descending by revenue.

=QUERY(A1:E7, "SELECT B, E WHERE C = 'East' AND D = 'Electronics' AND E > 10000 ORDER BY E DESC", 1)

Result: Automatically generates an ordered 2-column table displaying Elena ($15,000), Sarah ($12,000), and James ($11,500).

---

2. XLOOKUP: Modern and Flexible Search

XLOOKUP supersedes legacy VLOOKUP and HLOOKUP functions. It looks up values in any direction without column index counting and supports built-in fallback values.

• Syntax: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode])
• Scenario: Retrieve the sales rep assigned to Order ID ORD-104 with a fallback if missing.

=XLOOKUP("ORD-104", A2:A7, B2:B7, "Order Not Found")

Result: Returns Sarah. If the ID does not exist, it cleanly outputs Order Not Found instead of throwing a #N/A error.

---

3. ARRAYFORMULA: Batch Calculate Entire Columns

Instead of manually dragging formulas down hundreds of rows, ARRAYFORMULA evaluates mathematical operations across whole ranges in a single cell execution.

• Syntax: =ARRAYFORMULA(array_formula)
• Scenario: Compute an 8% commission on column E for all populated rows.

=ARRAYFORMULA(IF(E2:E="", "", E2:E * 0.08))

Result: Automatically calculates commissions across the entire column while leaving empty trailing rows blank.

---

4. INDEX & MATCH: Dynamic Matrix Lookups

While XLOOKUP covers 1D vector lookups, the INDEX/MATCH combo remains the standard for dynamic 2-way matrix lookups across rows and columns.

• Syntax: =INDEX(reference, MATCH(lookup_value, lookup_range, 0))
• Scenario: Return the revenue associated with ORD-105 reliably, even if columns are rearranged later.

=INDEX(E2:E7, MATCH("ORD-105", A2:A7, 0))

---

5. FILTER: Extract Subsets Without Modifying Source Data

The FILTER function returns subsets of a table meeting one or more boolean criteria without altering the underlying master records.

• Syntax: =FILTER(range, condition1, [condition2, ...])
• Scenario: Pull all records for the "East" region where revenue is greater than or equal to $10,000.

=FILTER(A2:E7, C2:C7 = "East", E2:E7 >= 10000)

---

6. UNIQUE: Deduplicate Lists Instantly

When compiling dimensional lists for dropdowns or validation tables, UNIQUE strips duplicate records instantly.

• Syntax: =UNIQUE(range)
• Scenario: Produce a distinct list of active sales representatives.

=UNIQUE(B2:B7)

Result: Outputs clean distinct values: Sarah, James, Elena, and David.

---

7. SUMIFS: Multi-Criteria Aggregation

Calculate sums based on several conditions spanning different criteria ranges simultaneously.

• Syntax: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
• Scenario: Calculate total revenue generated from "Electronics" inside the "East" territory.

=SUMIFS(E2:E7, C2:C7, "East", D2:D7, "Electronics")

Result: Sums $12,000 + $15,000 + $11,500 to output $38,500.

---

8. COUNTIFS: Conditional Frequency Tracking

Track how often specific multi-factor events occur within your dataset.

• Syntax: =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
• Scenario: Count how many deals Sarah closed with a revenue value of $5,000 or greater.

=COUNTIFS(B2:B7, "Sarah", E2:E7, ">=5000")

Result: Returns 1 (Only ORD-101 meets both criteria).

---

9. IFERROR: Maintain Clean Reporting Dashboards

Prevent unsightly calculation errors (#DIV/0!, #VALUE!, #N/A) from breaking your summaries.

• Syntax: =IFERROR(original_formula, [value_if_error])
• Scenario: Safely divide target milestones without breaking on zero values.

=IFERROR(E2 / TargetCell, 0)

---

10. TEXTJOIN & SPLIT: String Parsing & Concatenation

Combine or break apart text strings across rows and columns using custom delimiters.

• Combine distinct values:

  =TEXTJOIN(", ", TRUE, UNIQUE(C2:C7))

  Output: East, West, South
• Extract order numbers:

  =SPLIT(A2, "-")

  Output: Splits "ORD" and "101" into adjacent columns

---

Common Pitfalls and Best Practices

• Avoid Blocking ARRAYFORMULA Spills: Ensure cells beneath an ARRAYFORMULA remain completely empty. Any manual text or numbers will trigger a #REF! expansion error.
• String Matching Sensitivity: Remember that SQL strings in QUERY are case-sensitive. "East" and "east" will evaluate as distinct inputs.
• Lock References When Appropriate: When not using array formulas, remember to use absolute referencing ($A$2:$E$7) to prevent lookup ranges from sliding as formulas are copied.

---

Frequently Asked Questions (FAQ)

Q: Is XLOOKUP fully supported in Google Sheets?
A: Yes, Google Sheets natively supports XLOOKUP across desktop and mobile versions without requiring external scripts.

Q: Can the QUERY function merge data from multiple sheets?
A: Yes. You can stack data from different sheets by wrapping ranges in curly braces: =QUERY({Sheet1!A2:E; Sheet2!A2:E}, "SELECT * WHERE Col1 IS NOT NULL", 0).