Summarizing with Formulas

Lesson 5 · taught by Pip

Pip again. Sorting and filtering helped you see your data. Now we'll summarize it — boil a whole column of numbers down to one figure that answers a question. That single figure is what people usually mean by "an insight." A formula is the small instruction you write in a cell to get it.

Think of a jar of coins. You could examine each coin, or you could just count the total. Summarizing is reaching for the total instead of the pile. A spreadsheet does the counting in a blink, no matter how many coins are in the jar.

How a formula looks

Every formula starts the same way: with an equals sign, =. That sign tells the spreadsheet "don't store this as text — calculate it." After the equals sign comes the name of a built-in helper called a function, and then, in parentheses, the cells you want it to work on.

You point at a stretch of cells using a range — a shorthand like B2:B50, meaning "from cell B2 down to B50." So =SUM(B2:B50) reads, in plain English, "add up everything from B2 down to B50." The answer appears right in the cell.

The five workhorses

You can go a very long way with just five functions. Each one collapses a range into a single number.

SUM adds everything up. =SUM(B2:B50) gives your total sales, total hours, total anything. This is the one you'll reach for most.

AVERAGE finds the typical value by adding everything and dividing by how many there are. =AVERAGE(B2:B50) tells you the average order size or the average daily visitors.

COUNT tells you how many numbers are in the range — useful for "how many sales did we make?" There's a cousin, COUNTA, that counts filled cells of any kind, numbers or text, handy for "how many customers do we have?"

MIN and MAX find the smallest and the largest value. =MAX(B2:B50) is your best day; =MIN(B2:B50) is your slowest. (Sorting could show you these too — but a formula gives you the number alone, ready to drop into a report.)

The average's quiet trap

The average is the most popular summary and the most easily misread, so let me wave a small flag here. The average can be pulled hard by one extreme value.

Picture a room of ten people. Nine earn ordinary wages; one is a billionaire who walks in. The average wealth in the room is now enormous — but it describes no one actually standing there. One giant number dragged the average somewhere unrepresentative.

When that happens, a better summary is the median: the middle value when you line everything up smallest to largest. Half are above it, half below, and a single billionaire can't budge it. Most spreadsheets have a MEDIAN function that works just like the others. When your data has a few wild outliers, reach for the median and the average together, and notice when they disagree — that gap is itself telling you something.

Why this matters

A column of three hundred numbers means nothing to a human eye. "Total sales: $42,180. Average order: $58. Best day: $1,290." — that lands. Summarizing is the step that turns raw rows into figures a person can actually use to decide something. It's the heart of getting insight from data.

Your turn

In a sheet with a column of numbers:

1. In an empty cell, write =SUM( then select the column, close the parenthesis, and press Enter. Read your total.
2. Do the same with =AVERAGE and =COUNT.
3. Try =MAX and =MIN to find your biggest and smallest.
4. If your numbers have any extreme outliers, also compute =MEDIAN and compare it to the average. Are they close, or far apart?

Next, we'll combine summarizing with grouping — the pivot table, where data work really starts to feel like magic.