Google Sheets Formulas for Beginners: The Essential List
Learn Google Sheets formulas from scratch: SUM, AVERAGE, IF, COUNTIF, VLOOKUP, $ references, plus Sheets-only tools like ARRAYFORMULA, QUERY and SPLIT.
By Ayoub Farid · · 2 min read
Google Sheets formulas work almost exactly like Excel's. If you learn them in one, you know them in the other. This guide covers the essentials in the right order, plus a few tools only Sheets has.
The basics
- Start every formula with
=. - Point at cells:
=B2+B3. - Ranges use a colon:
B2:B13. - As you type a function, Sheets shows a help box with the arguments. Press F1 to expand it.
Essential formulas
| Formula | Example |
|---|---|
| SUM | =SUM(B2:B13) |
| AVERAGE | =AVERAGE(B2:B13) |
| MIN / MAX | =MAX(B2:B13) |
| COUNT / COUNTA | =COUNTA(A2:A100) |
| IF | =IF(B2>=5000,"Hit","Miss") |
| COUNTIF | =COUNTIF(C2:C100,"Paid") |
| SUMIF | =SUMIF(C2:C100,"Food",D2:D100) |
| VLOOKUP | =VLOOKUP(F2,A2:C100,3,FALSE) |
| XLOOKUP | =XLOOKUP(F2,A2:A100,C2:C100) |
| IFERROR | =IFERROR(B2/C2,"") |
Guides: IF in Sheets, COUNTIF in Sheets, SUMIF in Sheets, VLOOKUP in Sheets.
$ references
Same as Excel: $B$14 stays fixed when copied. Press F4 while editing (Fn+F4 on many laptops). See absolute references in Google Sheets.
Sheets-only tools
- ARRAYFORMULA: one formula for a whole column.
- QUERY: SQL-like questions on your data.
- SPLIT:
=SPLIT(A2," ")splits text into columns. - GOOGLEFINANCE, GOOGLETRANSLATE, IMPORTRANGE: pull data from other places.
Differences from Excel to know
See Excel vs Google Sheets formulas for the full list. The big ones: some spreadsheet locales use ; instead of , between arguments, localized Excel versions may translate function names (VLOOKUP is SVERWEIS in German Excel), and a few Excel-only functions don't exist in Sheets.
Practise on your phone
Everything Cellio teaches works the same in Google Sheets: references, SUM, AVERAGE, MIN, MAX, COUNT, fill down and $ locks. The lessons run on the app's own spreadsheet, so you don't need either app installed.
Frequently asked questions
Are Google Sheets formulas the same as Excel?
Mostly yes. Common functions have the same names and arguments.
How do I enter a formula in Google Sheets?
Click a cell, type =, then the formula, and press Enter.
Why does my formula need semicolons?
Your spreadsheet locale uses commas as decimal separators. Change it in File > Settings, or use semicolons.
What formulas are unique to Google Sheets?
ARRAYFORMULA, QUERY, SPLIT, GOOGLEFINANCE, IMPORTRANGE and IMAGE are among the best known.