Google Sheets Formulas Every Professional Needs

Google Sheets has evolved from a simple online spreadsheet into a powerful data analysis tool. While it shares many functions with Excel, Sheets has unique formulas that can supercharge your productivity. Here are the essential ones every professional should know.

QUERY: SQL for Spreadsheets

The QUERY function is Sheets' killer feature. It lets you run SQL-like queries on your data without leaving the spreadsheet.

Basic Syntax

=QUERY(data, query, [headers])

Example: Filter and Sort Sales Data

=QUERY(A:D, "SELECT A, B, D WHERE D > 1000 ORDER BY D DESC", 1)

This returns columns A, B, and D where column D exceeds 1000, sorted descending.

Common QUERY Operations

Filter by condition:

=QUERY(A:D, "SELECT * WHERE B = 'North'")

Group and aggregate:

=QUERY(A:D, "SELECT B, SUM(D) GROUP BY B")

Multiple conditions:

=QUERY(A:D, "SELECT * WHERE B = 'North' AND D > 500")

Limit results:

=QUERY(A:D, "SELECT * ORDER BY D DESC LIMIT 10")

ARRAYFORMULA: One Formula, Many Results

ARRAYFORMULA applies a formula to an entire range, outputting multiple results from a single formula.

Basic Usage

Instead of copying this formula down 1000 rows:

=A2*B2

Use one ARRAYFORMULA:

=ARRAYFORMULA(A2:A*B2:B)

Conditional ARRAYFORMULA

Combine with IF to handle empty cells:

=ARRAYFORMULA(IF(A2:A="","",A2:A*B2:B))

ARRAYFORMULA with Text

Concatenate entire columns:

=ARRAYFORMULA(A2:A&" - "&B2:B)

IMPORTRANGE: Connect Spreadsheets

Pull data from other Google Sheets files.

Syntax

=IMPORTRANGE("spreadsheet_url", "Sheet1!A:D")

Tips

• First use requires authorization (click "Allow access")
• Use named ranges for cleaner formulas
• Combine with QUERY for filtered imports:

=QUERY(IMPORTRANGE("url","Data!A:D"), "SELECT * WHERE Col1='Active'")

UNIQUE and FILTER: Dynamic Lists

UNIQUE

Extract unique values from a range:

=UNIQUE(A2:A)

FILTER

Return rows matching criteria:

=FILTER(A2:D, B2:B="North", D2:D>1000)

Multiple conditions are AND'ed together.

Combining UNIQUE and FILTER

Get unique values that meet criteria:

=UNIQUE(FILTER(A2:A, B2:B="North"))

REGEXMATCH, REGEXEXTRACT, REGEXREPLACE

Google Sheets has native regex support, a huge advantage over Excel.

REGEXMATCH

Returns TRUE/FALSE if pattern matches:

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

(Matches patterns like 123-4567)

REGEXEXTRACT

Extracts matching text:

=REGEXEXTRACT(A2, "\d+")

(Extracts first number sequence)

REGEXREPLACE

Replace matching patterns:

=REGEXREPLACE(A2, "\s+", " ")

(Replaces multiple spaces with single space)

SPLIT and JOIN

SPLIT

Break text into multiple cells:

=SPLIT(A2, ",")

JOIN

Combine cells with a delimiter:

=JOIN(", ", A2:A10)

Combining with ARRAYFORMULA

Split an entire column:

=ARRAYFORMULA(SPLIT(A2:A, ","))

SPARKLINE: Inline Charts

Create tiny charts within cells.

Line Sparkline

=SPARKLINE(A2:F2)

Bar Chart

=SPARKLINE(A2, {"charttype","bar";"max",100;"color1","green"})

Bullet Chart (Progress Bar)

=SPARKLINE(A2/B2, {"charttype","bar";"max",1;"color1","#4285f4"})

IMAGE: Embed Images in Cells

=IMAGE("https://example.com/image.png")

Options:

• Mode 1: Fit to cell (default)
• Mode 2: Stretch to cell
• Mode 3: Original size
• Mode 4: Custom size

=IMAGE("url", 4, 100, 100)

GOOGLEFINANCE: Live Stock Data

=GOOGLEFINANCE("GOOGL")

Get specific attributes:

=GOOGLEFINANCE("GOOGL", "price")
=GOOGLEFINANCE("GOOGL", "change")
=GOOGLEFINANCE("GOOGL", "high52")

Historical data:

=GOOGLEFINANCE("GOOGL", "close", DATE(2024,1,1), DATE(2024,12,1), "DAILY")

GOOGLETRANSLATE

Translate text between languages:

=GOOGLETRANSLATE(A2, "en", "es")

Auto-detect source language:

=GOOGLETRANSLATE(A2, "auto", "en")

Power Combinations

Dynamic Dropdown from Another Sheet

=UNIQUE(FILTER(IMPORTRANGE("url","Sheet1!A:A"), IMPORTRANGE("url","Sheet1!A:A")<>""))

Summarize with QUERY + ARRAYFORMULA

=QUERY(ARRAYFORMULA({A2:A,B2:B*C2:C}), "SELECT Col1, SUM(Col2) GROUP BY Col1")

Conditional Formatting with Formulas

Create a helper column:

=ARRAYFORMULA(IF(B2:B>AVERAGE(B2:B),"Above Average","Below Average"))

Performance Tips

1. Limit IMPORTRANGE: Only import needed columns/rows
2. Avoid volatile functions: NOW(), TODAY(), RAND() recalculate constantly
3. Use static ranges: A2:A1000 instead of A2:A when possible
4. Single ARRAYFORMULA: One ARRAYFORMULA is faster than 1000 individual formulas
5. Simplify QUERY: Complex queries can be slow; consider helper columns

Conclusion

These formulas represent the unique power of Google Sheets. QUERY alone is worth the switch from Excel for many users. Master these functions and you'll handle complex data tasks that would take hours in other tools.

Start with QUERY and ARRAYFORMULA. They'll have the biggest impact on your productivity. Then explore FILTER, UNIQUE, and the regex functions as you encounter specific needs.