Google Sheets Formulas Every Professional Needs

### 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")

=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}")

### REGEXEXTRACT

Extracts matching text:

=REGEXEXTRACT(A2, "\d+")

### REGEXREPLACE

Replace matching patterns:

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

## 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:

=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.