Spreadsheets feel intimidating until you learn a small set of formulas that handle almost everything. The truth is that most people only ever need about twenty google sheets formulas to manage budgets, track work, clean up lists, and build simple reports. Once you understand these, Google Sheets stops being a blank grid and starts doing the math for you.
This guide explains each formula in plain English, with a small example you can picture right away. You do not need a math background. If you are brand new to spreadsheets in general, pairing this guide with some basic excel tips for beginners will help, because the core ideas are nearly identical in both programs.
How Formulas Work in Google Sheets
Every formula starts with an equals sign. Type = in a cell, and Sheets knows you want a calculation instead of plain text. After the equals sign comes the formula name, followed by brackets holding the cells it should use, like =SUM(B2:B10).
A reference such as B2:B10 is called a range — it means every cell from B2 down to B10. When the numbers in those cells change, the formula updates automatically. That single idea is what makes spreadsheets so powerful: you build the logic once, and the sheet keeps your totals current forever.
One more habit is worth learning early. Because your files live in Google Drive, several people can work on the same sheet at once. Choosing the best cloud storage setup for your team keeps those shared files organized and easy to find later.
The Google Sheets Formulas You Will Use Every Day
1. SUM — Add a column of numbers
=SUM(B2:B10) adds everything in the range. Use it for monthly spending, hours worked, or items sold. It is the first formula most people learn, and probably the one you will type most often.
2. AVERAGE — Find the middle value
=AVERAGE(B2:B10) adds the numbers and divides by how many there are. It is perfect for average order value, average test score, or average weekly grocery spend.
3. COUNT — Count cells with numbers
=COUNT(B2:B10) counts only the cells that contain numbers. If some cells are blank or hold text, COUNT ignores them, which makes it handy for checking how many entries actually have data.
4. COUNTA — Count everything that is not empty
=COUNTA(A2:A10) counts numbers, text, and dates alike. Use it to count names on a signup list or responses on a form where every filled cell matters.
5. SUMIF — Add only what matches a rule
=SUMIF(A2:A20,”Rent”,B2:B20) adds the amounts in column B only where column A says “Rent”. One formula replaces a pile of manual highlighting, and it is the backbone of most home budget sheets.
6. COUNTIF — Count only what matches a rule
=COUNTIF(C2:C20,”Paid”) counts how many rows say “Paid”. Swap in any word or number to count late orders, finished tasks, or students who passed.
Decision Formulas: IF, IFS, and IFERROR
7. IF — Make a simple yes-or-no choice
=IF(B2>1000,”Over budget”,”OK”) checks a condition and returns one answer if it is true and another if it is false. It is how sheets flag problems for you, instead of you scanning hundreds of rows by eye.
8. IFS — Test several conditions in order
=IFS(B2>=90,”A”,B2>=80,”B”,B2>=70,”C”) works through conditions from left to right and stops at the first match. Grades, priority labels, and shipping tiers all become one clean formula instead of a tangle of nested IFs.
9. IFERROR — Replace ugly error messages
=IFERROR(VLOOKUP(A2,Data!A:B,2,FALSE),”Not found”) catches errors and shows your message instead. Reports look far more professional when a missing value reads “Not found” rather than #N/A.
Lookup Formulas: Find Information Fast
10. VLOOKUP — Pull a value from another table
=VLOOKUP(“Item-104”,A2:D50,3,FALSE) searches the first column for a match, then returns a value from the third column of the same row. Use it to look up prices, names, or statuses from a master list. Always end with FALSE so it finds an exact match.
11. XLOOKUP — The newer, simpler lookup
=XLOOKUP(“Item-104″,A2:A50,C2:C50,”Not found”) does the same job with cleaner logic: what to find, where to look, and where the answer lives. It searches in any direction and includes a built-in “if not found” message, so it has largely replaced VLOOKUP in new sheets.
12 and 13. INDEX and MATCH — The flexible power pair
=INDEX(C2:C50,MATCH(“Item-104”,A2:A50,0)) combines two formulas: MATCH finds the position of your item, and INDEX returns the value at that position. It takes a little practice, but it never breaks when you insert columns — one reason data-heavy teams rely on it. Business teams that track operations in shared sheets, the way a business workflow board organizes tasks, often standardize on INDEX and MATCH for exactly this reliability.
Filtering and Sorting Formulas
14. FILTER — Show only the rows you want
=FILTER(A2:C50,B2:B50>100) creates a live, smaller table showing just the rows that meet your condition. Unlike the manual filter buttons, a FILTER result updates itself whenever the source data changes.
15. SORT — Reorder data automatically
=SORT(A2:C50,2,FALSE) sorts a range by its second column, largest first. Leaderboards, top-customer lists, and expense rankings all stay current without you dragging rows around.
16. UNIQUE — Remove duplicates in one step
=UNIQUE(A2:A50) returns each value once. It is the fastest way to turn a long list of orders into a clean list of distinct customers, products, or cities.
Text Formulas for Cleaning Up Data
17. TEXTJOIN — Combine text with a separator
=TEXTJOIN(“, “,TRUE,A2:A5) joins several cells into one line, skipping blanks if you set the second argument to TRUE. It is ideal for building lists of names, tags, or email recipients.
18 and 19. LEFT and MID — Pull out part of a cell
=LEFT(A2,3) takes the first three characters, while =MID(A2,4,3) takes three characters starting at position four. These shine when codes, SKUs, or dates arrive packed inside longer strings and you need just one piece of them.
20. TEXT — Format numbers inside sentences
=”Total sales: “&TEXT(B2,”$#,##0”) turns a raw number into neatly formatted text. Without TEXT, that same formula would show an ugly string of decimals. With it, your summary lines read like they were typed by a person.
Simple Ways to Get Better Results
Start small. Build one formula, check the answer against a quick manual calculation, and only then copy it down the column. A thirty-second check catches almost every beginner mistake, which is usually a wrong range or a missing bracket.
When you get stuck, describe what you want in plain words and let an assistant draft the formula for you. The chatgpt prompts for work approach works well here: paste your column layout, ask for the formula, then test it on two or three rows before trusting it. Comparing the best ai tools for spreadsheet help is worth ten minutes if you do this often.
Finally, present your work well. A dual monitor setup makes large sheets far easier to edit, and if you are teaching a teammate, you can screen record a short walkthrough of your sheet instead of writing a long explanation. Teachers and managers who present reports from a home office setup use these tricks constantly. Marketers do the same when they pull campaign numbers together for digital marketing reporting each month.
Three Habits That Make Every Formula Easier
First, learn absolute references. When you copy a formula down a column, references move with it — usually helpful, sometimes wrong. Adding dollar signs, as in $B$2, locks that reference in place. Tax rates, exchange rates, and any single “settings” cell should be locked this way, so one update changes the whole sheet.
Second, build formulas in pieces. If a long formula confuses you, calculate each part in its own helper column first, confirm the numbers look right, and only then combine them. Professional analysts do this constantly; it is not a beginner crutch, it is how errors get caught early.
Third, label everything. A header row that says “Monthly cost” beats a mystery column of numbers six months from now, and a short note at the top of the sheet explaining what it tracks saves every future reader — including future you — a lot of guessing.
Quick Questions
Do these formulas also work in Excel? Most of them do, with nearly identical syntax. XLOOKUP, FILTER, SORT, and UNIQUE exist in modern versions of Excel as well. A few small differences appear in how arrays behave, but the ideas transfer directly.
How do I keep a total at the top of a growing list? Use an open-ended range such as =SUM(B2:B), which includes every row below B2, even ones you add later. Just keep the total cell itself outside that column’s range.
What should I learn after these twenty? Pivot tables and the QUERY function are the natural next steps. Keeping study notes in one of the best note-taking apps while you practice helps the patterns stick.
The Bottom Line
You do not need hundreds of formulas — you need these twenty, understood well. SUM and its conditional cousins handle totals, IF family formulas make decisions, lookups connect your tables, and FILTER, SORT, and UNIQUE keep data tidy. Practice each one on a real sheet this week, and google sheets formulas will quickly go from intimidating to indispensable.
