10 Powerful Google Sheets Formulas That Will Transform Your Spreadsheet Game

Master these underrated formulas to automate tasks, clean data, and work smarter — not harder

Most people know the basics of Google Sheets — SUM, AVERAGE, maybe even VLOOKUP if they’re feeling adventurous. But there’s a whole world of powerful formulas hiding in plain sight that can save you hours of manual work.

After years of building spreadsheets for everything from project management to data analysis, I’ve discovered that these five lesser-known formulas are absolute game-changers. Let me show you how they work and when to use them.

Image from https://www.lovesdata.com/blog/google-sheets-course/

1. QUERY: SQL for Spreadsheets

What it does: Uses SQL-like syntax to filter, sort, and manipulate data — all in one formula.

Why it’s powerful: Combine multiple operations (filtering, sorting, grouping, aggregating) that would normally require multiple formulas or pivot tables.

Real-World Example

You’re tracking sales data with columns for Date, Salesperson, Region, and Revenue. You want to see total revenue by region for sales over $1,000, sorted from highest to lowest.

=QUERY(A2:D100, "SELECT C, SUM(D) WHERE D > 1000 GROUP BY C ORDER BY SUM(D) DESC LABEL SUM(D) 'Total Revenue'")

This single formula:

• Filters for sales over $1,000
• Groups by region (column C)
• Calculates totals
• Sorts by revenue
• Labels the output column

Another practical use:

=QUERY(A2:D100, "SELECT A, B, D WHERE B = 'Sarah' AND D > 500 ORDER BY A DESC")

This shows all of Sarah’s sales over $500, sorted by most recent date.

When to use it: Complex data analysis, creating dynamic reports, replacing multiple FILTER and SORT combinations, or when you need SQL-like power without leaving your spreadsheet.

2. ISNUMBER(SEARCH): The Smart Text Detective

What it does: Checks if specific text exists anywhere within a cell, returning TRUE or FALSE.

Why it’s powerful: Unlike exact match functions, this combo handles partial matches and is case-insensitive — perfect for messy real-world data.

Real-World Example

Imagine you’re managing a customer support ticket system. You need to identify all tickets containing urgent keywords like “ASAP,” “urgent,” or “emergency.”

=ISNUMBER(SEARCH("urgent", A2))

If cell A2 contains “This issue needs urgent attention,” the formula returns TRUE. If it says “Please review when possible,” it returns FALSE.

Pro tip: Combine with IF to create custom labels:

=IF(ISNUMBER(SEARCH("urgent", A2)), "Priority", "Standard")

This automatically categorizes your tickets without manual tagging.

3. UNIQUE: Eliminate Duplicates Instantly

What it does: Extracts only unique values from a range, automatically updating as your data changes.

Why it’s powerful: No more copy-paste-remove duplicates workflows. This creates a dynamic list that maintains itself.

Real-World Example

You’re tracking email signups across multiple campaigns and need a clean list of unique email addresses for your newsletter.

=UNIQUE(A2:A100)

This pulls every unique email from your list, ignoring duplicates. Add new emails to column A, and your UNIQUE list updates automatically.

Bonus move: Combine with SORT for alphabetized results:

=SORT(UNIQUE(A2:A100))

I use this constantly for creating dropdown lists, consolidating survey responses, and generating summary reports.

4. REGEXMATCH: Pattern Matching on Steroids

What it does: Uses regular expressions to find complex text patterns — like email addresses, phone numbers, or specific formatting.

Why it’s powerful: It’s like FIND or SEARCH, but infinitely more sophisticated. Perfect for data validation and cleanup.

Real-World Example

You’ve collected user data, but some people entered invalid email addresses. Let’s validate them:

=REGEXMATCH(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

This returns TRUE only for properly formatted email addresses like “user@example.com” and FALSE for invalid entries like “notanemail” or “missing@domain.”

Another powerful use case: Extract specific information:

=REGEXMATCH(A2, "\d{3}-\d{3}-\d{4}")

This identifies US phone numbers in the format 123–456–7890, even within longer text strings.

When to use it: Data cleaning, form validation, identifying specific patterns in customer feedback, or categorizing text based on complex rules.

5. IMPORTRANGE with FILTER: Cross-Spreadsheet Magic

What it does: Pulls data from another Google Sheet, and when combined with FILTER, lets you grab exactly what you need.

Why it’s powerful: Create a single source of truth while giving different teams customized views — no more version control nightmares.

Real-World Example

Your sales team maintains a master inventory sheet, but customer service needs a filtered view showing only available items under $50.

First, in your customer service sheet:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

This imports the entire range. Now add FILTER to refine it:

=FILTER(IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100"), 
        IMPORTRANGE("spreadsheet_url", "Sheet1!C1:C100")<50,
        IMPORTRANGE("spreadsheet_url", "Sheet1!D1:D100")="In Stock")

Now your customer service team sees only items under $50 that are currently available. When sales updates the master sheet, their view updates automatically.

First-time setup tip: Google Sheets will ask for permission the first time you connect spreadsheets. Just click “Allow access” when prompted.

6. ARRAYFORMULA: Write Once, Apply to Thousands

What it does: Applies a formula to an entire column with a single cell entry — no dragging or copying required.

Why it’s powerful: Saves time, reduces errors, and makes your spreadsheet more maintainable. Game-changing for large datasets.

Real-World Example

You have a list of first names in column A and last names in column B. You need to create full names in column C for 1,000 rows.

The old way: Write =A2&" "&B2 in C2, then drag down 1,000 times.

The ARRAYFORMULA way:

=ARRAYFORMULA(IF(A2:A<>"", A2:A&" "&B2:B, ""))

One formula in C2 handles everything. Add a new person to row 1001? It’s automatically included.

More advanced example: Calculate percentages for an entire column:

=ARRAYFORMULA(IF(B2:B<>"", B2:B/C2:C*100, ""))

This divides column B by column C and multiplies by 100 for every row — all from a single cell.

The IF condition (IF(A2:A<>"", ...)) prevents the formula from filling empty rows with zeros, keeping your sheet clean.

7. SPLIT: Turn One Column Into Many

What it does: Breaks text in a cell into separate columns based on a delimiter (comma, space, dash, etc.).

Why it’s powerful: Instantly parse messy data that arrived in a single column — no manual copy-pasting required.

Real-World Example

You’ve imported customer data where full names are in one column: “John Smith”, “Sarah Johnson”, etc. You need separate first and last name columns.

=SPLIT(A2, " ")

This splits “John Smith” into two columns: “John” | “Smith”

More complex example: Parse email addresses with multiple delimiters:

If A2 contains “john.smith@company.com”:

=SPLIT(A2, ".@")

This splits into three columns: “john” | “smith” | “company” | “com”

Real business use case: You receive CSV exports where the address is formatted as “123 Main St, Apt 4B, Boston, MA, 02101”. Split it:

=SPLIT(A2, ", ")

Now you have separate columns for street address, apartment, city, state, and ZIP code.

When to use it: Cleaning imported data, parsing CSV files, separating concatenated information, or preparing data for mail merges.

8. TRANSPOSE: Flip Your Data Instantly

What it does: Converts rows to columns and columns to rows.

Why it’s powerful: Restructure your data layout without manual copying — essential when your data is organized the “wrong” way for your analysis.

Real-World Example

You’re tracking monthly sales, but the data came horizontally (months across the top, products down the side). You need it vertical for charting or further analysis.

Original data:

Product | Jan | Feb | Mar
Widget  | 100 | 150 | 200
Gadget  | 80  | 90  | 110

Formula:

=TRANSPOSE(A1:D3)

Result:

Product | Widget | Gadget
Jan     | 100    | 80
Feb     | 150    | 90
Mar     | 200    | 110

Another practical use: Creating dynamic dropdowns from horizontal lists:

If you have categories listed horizontally in row 1 (A1:F1), use:

=TRANSPOSE(A1:F1)

This creates a vertical list perfect for data validation dropdowns.

When to use it: Reformatting imported data, creating charts from horizontal data, building dynamic dropdowns, or preparing data for pivot tables.

9. TEXTJOIN: Combine Text with Control

What it does: Joins multiple text values with a custom delimiter, and optionally ignores empty cells.

Why it’s powerful: Unlike simple concatenation (&), this handles ranges, skips blanks, and adds separators automatically — perfect for creating lists, tags, or formatted output.

Real-World Example

You’re managing a project tracker where tasks have multiple assigned team members across columns C, D, and E. You want to create a single “Assigned To” column with names separated by commas.

=TEXTJOIN(", ", TRUE, C2:E2)

If C2=”Sarah”, D2=”John”, E2=”” (empty), this outputs: “Sarah, John”

The TRUE parameter automatically skips that empty cell in E2.

Creating dynamic email lists:

If you have email addresses in column A, rows 2–10:

=TEXTJOIN("; ", TRUE, A2:A10)

This creates: “email1@company.com; email2@company.com; email3@company.com” — ready to paste into your email client’s CC field.

Advanced use case: Combine with IF for conditional joining:

=TEXTJOIN(", ", TRUE, IF(B2:B10="Complete", A2:A10, ""))

This joins only the names (column A) where the status (column B) is “Complete.”

When to use it: Creating comma-separated lists, building email recipient lists, generating hashtags, combining filtered values, or formatting output for reports.

10. SUMIFS / COUNTIFS: Multi-Condition Counting and Summing

What it does: Sums or counts cells that meet multiple criteria simultaneously.

Why it’s powerful: Analyze data with complex conditions without pivot tables — perfect for dashboards and reports.

Real-World Example

You’re analyzing sales data and need to calculate total revenue from the West region for Sarah’s sales above $1,000.

=SUMIFS(D:D, B:B, "Sarah", C:C, "West", D:D, ">1000")

Breaking it down:

• D:D — Sum this column (Revenue)
• B:B, “Sarah” — Where Salesperson column equals Sarah
• C:C, “West” — AND Region column equals West
• D:D, “>1000” — AND Revenue is greater than 1000

Count instead of sum:

=COUNTIFS(B:B, "Sarah", C:C, "West", D:D, ">1000")

This tells you HOW MANY sales meet those criteria, rather than the total dollar amount.

Dynamic dashboard example:

Create a cell (F1) where users can select a region from a dropdown. Then:

=SUMIFS(D:D, C:C, F1, D:D, ">="&G1, D:D, "<="&H1)

This sums revenue for the selected region (F1) between a minimum (G1) and maximum (H1) value — creating an interactive dashboard.

When to use it: Building dashboards, creating conditional reports, analyzing subsets of data, calculating KPIs with multiple filters, or replacing complex pivot tables with simple formulas.

Why These Formulas Matter

Mastering these ten formulas transformed how I work with spreadsheets. Instead of spending hours on manual data cleanup and updates, I build systems that maintain themselves.

Here’s how they work together in real workflows:

Data Import & Cleanup:

1. Use IMPORTRANGE to pull data from multiple sources
2. Apply SPLIT to parse messy formatting
3. Use REGEXMATCH to validate entries
4. UNIQUE removes duplicates
5. TRANSPOSE restructures if needed

Analysis & Reporting:

1. QUERY creates complex filtered views
2. SUMIFS/COUNTIFS calculate conditional metrics
3. ARRAYFORMULA applies calculations to entire datasets
4. TEXTJOIN formats output for reports

Automation:

1. ISNUMBER(SEARCH) categorizes incoming data
2. ARRAYFORMULA ensures formulas persist
3. IMPORTRANGE keeps data synchronized

Start small: Pick one formula that solves a current problem. Once you see the time savings, you’ll naturally explore the others.

The difference between a basic spreadsheet user and a power user isn’t knowing every formula — it’s knowing which formulas eliminate repetitive work.

Which formula will you use first and how?