10 Google Sheets Formulas That Cover 90% of Real Work

Google Sheets has hundreds of functions. You need about ten. The rest are specialist tools you can look up the rare time you need them. This guide covers the formulas that do the actual heavy lifting in real spreadsheets, each with a plain example you can copy.

A quick note on syntax

Every formula starts with =. Inside, you reference cells (A2), ranges (A2:A100), or values. If something breaks, 90% of the time it's a missing parenthesis or a comma where you needed a colon. Sheets will usually tell you which.

1. SUM — add things up

The workhorse. Adds a range of numbers.

=SUM(B2:B20)

Use it for totals at the bottom of a column. Pair it with the next formula when you need conditional totals.

2. SUMIF — add things up, conditionally

Add numbers only when a condition is met. "Total sales, but only for the North region."

=SUMIF(A2:A100, "North", B2:B100)

Read it as: in range A2:A100, wherever the value is "North", add the matching number from B2:B100. This one formula replaces a shocking amount of manual filtering.

3. COUNTIF — count things, conditionally

Same idea, but counting instead of adding. "How many orders are marked Complete?"

=COUNTIF(C2:C100, "Complete")

Great for dashboards and quick tallies.

4. AVERAGE — the mean

=AVERAGE(B2:B20)

Exactly what it says. Use AVERAGEIF for the conditional version, which follows the same pattern as SUMIF.

5. IF — make decisions

The logic backbone. "If the score is 50 or more, say Pass, otherwise Fail."

=IF(B2>=50, "Pass", "Fail")

The structure is IF(condition, value-if-true, value-if-false). You can nest them, but once you're three deep, stop — there's almost always a cleaner approach.

6. VLOOKUP / XLOOKUP — pull data from another table

This is the formula that feels like magic the first time. You have an ID, and you want the matching name (or price, or status) from another table.

=XLOOKUP(A2, Products!A:A, Products!B:B)

XLOOKUP is the modern, friendlier version: look up the value in A2, find it in the Products sheet's column A, and return the matching value from column B. (The older VLOOKUP does the same but is fussier about column order.) This is how separate sheets start talking to each other — and it pairs beautifully with data fed in automatically, like the form-to-spreadsheet automation we cover elsewhere.

7. CONCATENATE / & — join text

Stick text together. The & operator is the quick way.

=A2 & " " & B2

Joins a first name and last name with a space between. Useful for building full names, labels, and URLs.

8. TODAY / NOW — live dates

=TODAY()

Returns the current date and updates automatically. Subtract dates to get durations: =TODAY()-A2 tells you how many days since the date in A2. Indispensable for deadlines and aging reports.

9. TEXT — format numbers and dates

Control how a value displays — currency, percentages, date formats.

=TEXT(B2, "$#,##0.00")

Turns 1234.5 into $1,234.50. The format string looks cryptic but you'll reuse the same few patterns.

10. ARRAYFORMULA — apply a formula to a whole column

The power-user favorite. Instead of copying a formula down 1,000 rows, write it once and let it fill the column automatically.

=ARRAYFORMULA(B2:B & " — " & C2:C)

This keeps spreadsheets clean and fast, and means new rows get the formula automatically. It's especially valuable when rows arrive via automation and you can't manually drag a formula down each time.

How to actually learn these

Don't try to memorize all ten today. Open a real spreadsheet you already use and replace one manual step with a formula. Next week, replace another. Within a month these become reflexes, and you'll wonder how you ever totaled columns by hand.

If your spreadsheet is collecting data automatically — say, from a form or an automation — these formulas turn that raw pile into something readable. That combination of automatic input plus a few good formulas is where spreadsheets quietly become one of the most powerful tools on your computer. For the input side, see how to route form submissions and leads into Sheets automatically.