From “I broke the spreadsheet” to “I’ve got this”

The plain-English guide to Google Sheets™ formulas for busy offices and schools

If you manage a field-trip roster, a conference sign-up, a supply budget or a clinic schedule, you already use a spreadsheet every day. You probably did not choose to become “the spreadsheet person.” It just happened, and now a single cell that says #REF! can ruin a perfectly good afternoon. This guide is written for you. It uses everyday words, real office situations and short examples you can try in the practice lab above without touching a real file.

How a formula is put together

Every formula has the same four parts, and once you can see them, formulas stop looking like secret code.

Part	Example	What it means
The equals sign	=	“Sheet, please calculate what follows.” Without it, the cell is just text.
The function name	COUNTIF	The job you want done: count, add, look up, filter.
The parentheses	( … )	A container for the information the job needs. Every opening one needs a closing partner.
The pieces inside	B2:B8, "Bus"	Where to look and what to look for, separated by commas. Words go in double quotes.

Read =COUNTIF(B2:B8, "Bus") out loud as “count the cells from B2 to B8 that say Bus.” Almost every formula in this guide can be read aloud that way. If you can say it, you can write it.

Why errors are less scary than they look

An error message in a cell is the spreadsheet asking a question. It is not a sign that data has been lost, and it is almost never a sign that you broke the master file. Each message has a small number of usual causes:

• #ERROR! — a typing slip, usually a missing parenthesis, comma or quote mark.
• #NAME? — a misspelled function, or a word that needs quote marks.
• #N/A — a lookup searched for something and did not find it.
• #REF! — the formula points at a cell, tab or file it cannot reach.
• #VALUE! — math was attempted on text, or two ranges are different sizes.
• #DIV/0! — something was divided by zero or by an empty cell.

The Error Un-Breaker at the top of this page walks through each of these with a before-and-after example. Module 1 of the practice lab lets you cause every one of them on purpose and then fix it, which is the fastest way to stop fearing them.

IFERROR: the calm wrapper

Sometimes an error is expected. A new student has no bus assignment yet, or a volunteer has zero shifts so far. In those moments you do not need to fix anything; you only need the cell to say something sensible. IFERROR does exactly that. Put it around any formula, add a comma, and then say what to show instead: =IFERROR(C4/D4, "No shifts yet"). Use it for errors you understand. If an error surprises you, look at it first, because the message is telling you something useful.

Cleaning up a messy list

Sign-up forms filled in on phones produce names with stray spaces, all capitals or no capitals at all. Three small functions tidy nearly everything:

• TRIM removes extra spaces at the beginning, the end and in between words.
• PROPER capitalizes the first letter of every word.
• UNIQUE removes repeated entries, and SORT puts what remains in order.

You can place one inside another, like nesting cups. =SORT(UNIQUE(B2:B8)) first removes the repeats, then sorts the result. That one formula builds a clean list for a dropdown menu, and it updates itself whenever the original column changes.

Counting and totaling without a calculator

Two questions come up in every office: “how many?” and “how much?” COUNTIF answers the first and SUMIF answers the second. When the question has two conditions — approved purchases from the Art Room, Grade 3 students who ride the bus — add an S and use COUNTIFS or SUMIFS. There are only two rules to remember. First, every range in the formula must cover the same rows. Second, with SUMIFS the column you are adding up always comes first. Module 2 practices both on a pretend supply purchasing log, including comparisons such as ">100" and the wildcard star in "*Supplies*".

Lists that update themselves

Copying and pasting a filtered list creates a second copy that is out of date within the hour. FILTER creates a live view instead. =FILTER(A2:C8, E2:E8="Missing") shows the rows where column E says Missing, and it changes the moment a slip is marked Returned. A comma between conditions means “and.” A plus sign between conditions in parentheses means “or.” When nothing matches, FILTER shows #N/A, which is the perfect place for the calm wrapper: =IFERROR(FILTER(…), "All caught up").

Pulling data from another file

IMPORTRANGE brings a range from a different spreadsheet into yours, so the transportation office can keep its bus roster and you can still total the riders. The first time you connect two files, the cell shows #REF! with a small “Allow access” button. That is not a failure. It is Google asking permission once, like a doorbell. Module 3 simulates the whole handshake so that the first time you see it for real, you already know what to click.

Matching two lists

Looking up a room number from a staff code, or a phone extension from a name, is the single most common spreadsheet task in an office. The modern tool is XLOOKUP, and it reads like a sentence: find this, in that column, and give me the matching cell from this other column. You never count columns, it can look to the left, and an optional fourth piece lets you choose what to show when nothing is found. If you inherited a sheet full of the older VLOOKUP, Module 4 shows how to replace it and why the replacement will not break when someone inserts a column next year.

Summary reports with QUERY

QUERY is the formula people are most nervous about and most delighted by. It turns a long roster into a finished summary: totals per grade, a grid of destinations by status, the top two fundraisers. Its instructions go inside one pair of double quotes and always follow the same order:

1. SELECT — which columns to show, by letter.
2. WHERE — which rows to keep.
3. GROUP BY — what to make one row for.
4. PIVOT — what to spread across the top.
5. ORDER BY — how to sort.
6. LIMIT and OFFSET — how many rows to show, and how many to skip.
7. LABEL — friendlier headings.
8. FORMAT — how numbers should look.

You rarely need all of them, but the ones you use must stay in that order. Two more habits prevent most trouble: column letters are capitals, and words inside the instructions use single quotes, as in WHERE A = 'Confirmed', because the double quotes are already in use around the whole instruction. Module 5 builds a complete report one clause at a time. The first five drills are free so you can reach the moment where a whole summary appears from one line.

One formula for a whole column

Dragging a formula down a column works until someone adds a row at the bottom and forgets to drag it again. ARRAYFORMULA removes that chore. You write the formula once, in the top cell, using ranges instead of single cells, and it fills every row — including rows added later if you use an open-ended range such as D2:D. The classic pattern is =ARRAYFORMULA(IF(A2:A="", "", D2:D - E2:E)): for every row, if there is no name leave the cell empty, otherwise calculate the balance. Module 6 practices this pattern until it feels natural.

Good habits that prevent most problems

• Keep one kind of thing in each column. Numbers in the amount column, notes in a notes column. “20 (cash)” in a number column is the usual cause of #VALUE!.
• Leave the original data alone. Build reports on a separate tab with QUERY or FILTER so the roster itself never gets sorted or overwritten.
• Name your tabs clearly. “Roster” and “Report” are easier to reference correctly than “Sheet7”.
• Use undo without hesitation. Ctrl+Z reverses almost anything, and File, then Version history, can restore the sheet as it was yesterday.
• Practice on pretend data. Every table in the lab above is synthetic, so you can experiment freely and then apply what worked to your real file.

How to use this page in a staff PD session

Many schools and offices use a single module as a one-hour professional development activity. Project the page, work the first two drills together, and then let everyone continue at their own pace on a Chromebook or laptop. When a staff member solves every drill in a module, the lab offers a printable certificate showing one clock hour of self-paced practice. Names are typed on the staff member’s own device at the moment of printing and are never transmitted. Print the one-page Desk Rescue Card for anyone who would like a reminder beside their monitor.

Common questions

Do I need to sign in or install anything?

No. The lab runs entirely in your web browser, with no account, no download and no browser extension. It works on school Chromebooks, office PCs, Macs and phones.

Can practicing here damage my real spreadsheet?

No. The lab is not connected to your Google account or to any of your files. Every table is made-up practice data that lives only on this page.

Is any student or patient information involved?

None. Every name, roster and schedule in the lab is synthetic. You never upload, paste or connect real records, which is why the lab is comfortable to use in schools and clinics.

Where is my progress saved?

Only in this browser on this device, using a small local setting. Nothing is sent to us. Use the “Reset My Progress” button under the lab to clear it at any time.

Which drills are free?

All of Module 1, the first two drills of Modules 2, 3, 4 and 6, and the first five drills of the QUERY module are open to everyone. The remaining drills open with a pass or a School Building Pass Code.

Will these formulas work in Microsoft Excel?

Most of them, yes: IFERROR, TRIM, COUNTIFS, SUMIFS, FILTER, UNIQUE, SORT and XLOOKUP work the same way in current versions of Excel. QUERY, IMPORTRANGE and ARRAYFORMULA are specific to Google Sheets™.

What if a formula works in the lab but not in my own sheet?

Check three things first: that your ranges match your real rows, that words are spelled exactly as they appear in your data, and that there are no hidden spaces. The Error Un-Breaker at the top of this page covers the rest.