Google Sheets Formulas for Beginners (10 You Need to Know)

A Google Sheets formula is a rule that starts with an equals sign (=) and tells a cell what to calculate or display. Type =SUM(A1:A5) into a cell and press Enter, and Sheets adds up cells A1 through A5. The ten functions below — SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, CONCATENATE, VLOOKUP, and TODAY — cover about 90% of everyday spreadsheet work, and by the end of this guide you’ll have built a real sales sheet using four of them in sequence.

How to read a formula

Before typing formulas, learn the punctuation. Every symbol below changes what a formula does, and beginners who skip this end up copying formulas they don’t understand.

The $ sign deserves special attention. A reference like A1 is relative — copy the formula to the next row and it becomes A2. A reference like $A$1 is absolute — it stays pointed at A1 no matter where you copy it. Use $ for lookup tables and fixed rates.

One locale note: in some countries Sheets uses a semicolon (;) instead of a comma to separate arguments. If =SUM(A1:A5, C1) gives an error, try =SUM(A1:A5; C1).

How to enter a formula

1. Click the cell where you want the result.
2. Type = (or click fx in the formula bar).
3. Type the formula, then press Enter.
4. Drag the small square in the cell’s corner to copy it down a column.

The essential 10

Using a range vs. individual cells

• Range: SUM(A1:A10) — everything between A1 and A10.
• Individual: SUM(A1, A3, A5) — only the listed cells.
• Mixed: SUM(A1:A5, C1) — a range plus one extra cell.

Worked example: build a mini sales sheet

Nothing sticks like doing it once. Set up this small sheet, then build each formula in order. We’ll use a sales table and a separate region lookup table on the same sheet.

Enter these headers and values:

Now build the formulas one at a time.

Step 1 — SUM: total the units

Click C6 and type =SUM(C2:C5), then press Enter. Sheets adds 120 + 80 + 60 + 140.

Expected result: 400.

Step 2 — AVERAGE: average units per rep

Click C7 and type =AVERAGE(C2:C5), then press Enter.

Expected result: 100.

Step 3 — IF: flag who hit the target

Click F2 (leave column E blank as a spacer) and type =IF(C2>=100, "Hit target", "Below target"), then press Enter. Drag the fill handle down to F5 to apply it to every rep.

Expected results: Ana → Hit target (120), Ben → Below target (80), Cara → Below target (60), Dan → Hit target (140).

Step 4 — VLOOKUP: pull in the region lead

Add a lookup table in columns H and I:

Click G2 and type =VLOOKUP(B2, $H$2:$I$4, 2, FALSE), then press Enter and drag down to G5.

Here’s what each part means: B2 is the value to find (the region), $H$2:$I$4 is the locked lookup table, 2 says “return the second column,” and FALSE demands an exact match. The $ signs stop the table reference from drifting when you copy the formula down.

Expected results: Ana → Priya, Ben → Marco, Cara → Priya, Dan → Dana.

You just used four of the ten core functions in a real workflow. See how to use VLOOKUP in Google Sheets if the lookup step gives you trouble.

Going beyond the basics

Once the core ten feel comfortable, these five functions handle most of what’s left.

SUMIF and COUNTIF add or count only the rows that match a condition. In the sales sheet, =SUMIF(B2:B5, "North", C2:C5) returns 180 (120 + 60), and =COUNTIF(B2:B5, "North") returns 2. Our SUMIF guide walks through the criteria syntax.

IFERROR wraps another formula and shows a friendly message instead of an error. =IFERROR(VLOOKUP(B2, $H$2:$I$4, 2, FALSE), "Not found") displays Not found when the region is missing, rather than #N/A.

INDEX/MATCH does the same job as VLOOKUP but is more flexible — it can look to the left as well as the right: =INDEX($I$2:$I$4, MATCH(B2, $H$2:$H$4, 0)). The MATCH finds the row number, and INDEX returns the value from that row. The trailing 0 in MATCH means exact match.

XLOOKUP is the modern replacement for both, and Google Sheets has it too: =XLOOKUP(B2, $H$2:$H$4, $I$2:$I$4). It defaults to an exact match, needs only the search column and the result column, and returns #N/A cleanly when nothing is found. If you’re starting fresh, learn XLOOKUP; learn VLOOKUP and INDEX/MATCH because so many existing spreadsheets use them.

Two beginner mistakes to avoid

1. Text in a number column — SUM and AVERAGE ignore text, but COUNT only counts numbers. Use COUNTA to count any non-empty cell.
2. Forgetting the = sign — without it, Sheets treats what you typed as plain text.

For the Excel version of lookups, see how to use VLOOKUP in Excel.

Building a monthly budget is the classic first spreadsheet - see how to make a budget in Excel, or make a graph once your numbers are in.

FAQ

How do I apply a formula to a whole column?

Enter it in the first cell, then drag the blue fill handle (bottom-right corner) down the column — Sheets adjusts the relative references automatically. Use $ to keep any reference fixed.

What’s the difference between COUNT and COUNTA?

COUNT counts only numbers; COUNTA counts any cell that isn’t empty (text, dates, numbers).

What’s the difference between VLOOKUP and INDEX/MATCH?

VLOOKUP searches the first column of a range and can only return values to its right. INDEX/MATCH combines two functions so you can look up a value and return a result from either side, which is why many power users prefer it.

What does the $ sign do in a formula?

It locks a reference so it doesn’t change when you copy the formula. $A$1 locks both column and row, $A1 locks only the column, and A$1 locks only the row.

How do I make a formula reference another sheet?

Prefix the cell with the sheet name and an exclamation mark: =SUM(Sheet2!A1:A10).