A handful of Excel formulas answers most business questions

You don't need to memorize hundreds of functions. A short list of them answers almost everything you'll actually be asked.

People think being good with spreadsheets means knowing a lot of formulas. It doesn't. Excel has hundreds of functions, and the people who actually get answers out of data use maybe a dozen. The rest are specialized tools that sit in the drawer for years.

The reason this matters isn't trivia. It's that "I need to learn Excel" is a vague, intimidating goal that people bounce off of. "I need to learn the six formulas that answer most questions" is a Tuesday afternoon. And once you frame it that way — by what each formula answers rather than by memorizing syntax — the whole thing gets small and doable.

You're not memorizing functions. You're matching questions.

Here's the shift. Don't learn formulas as a list of names to memorize. Learn them as answers to the plain-language questions people actually ask: How much? How many? What's typical? Which ones meet a rule? Match the question to the formula and you never have to remember syntax cold — you remember what you're trying to find out, and that points you at the tool.

Most business questions are one of a few shapes. "How much did we make?" is a total. "How many orders came in?" is a count. "What's a typical order?" is an average or a median. "How many big orders?" is a count with a condition. Learn the formula for each shape and you can answer the large majority of what lands on your desk.

The short list, by what each one answers

How much? — SUM. Adds a range. =SUM(D2:D13) totals a column of expenses. This is the workhorse; most "what's the total" questions end here.

How many? — COUNT, COUNTA, COUNTIF. COUNT counts cells with numbers; COUNTA counts any filled cell, numbers or text. COUNTIF is the one you'll reach for most — it counts only the cells that meet a rule. =COUNTIF(F2:F100,"Texas") counts orders from Texas; =COUNTIF(F2:F100,">500") counts orders over $500. Any "how many of X" question is a COUNTIF.

What's typical? — AVERAGE and MEDIAN. AVERAGE gives the mean. MEDIAN gives the middle value of a sorted list. They matter more than they look, because they can disagree hard — more on that in a second.

Smallest and largest? — MIN and MAX. Exactly what they sound like. The cheapest order, the biggest sale.

That's the aggregate toolkit. Add one more category and you've covered most of the ground.

Which ones meet a rule? — IF. IF checks a condition and returns one thing if it's true, another if it's false. This is how you stop labeling rows by hand. =IF(ISBLANK([@Email]),"Incomplete","OK") flags every customer missing an email, instantly and consistently. Its cousins OR (true if at least one condition holds) and AND (true only if all do) let you build richer rules: =IF(OR([@Contract_Ending]="Yes",[@Hours_Worked]>40),"Review","") flags any employee who either has a contract ending or worked over 40 hours. Write the rule once; it labels every row the same way, every time.

You'll notice you don't memorize any of these. You learn what each one answers. The syntax you can look up, or ask a tool for, in ten seconds.

The one that trips people up: AVERAGE vs MEDIAN

Here's a case worth slowing down on, because it's where a "correct" formula gives a misleading answer.

Say you want a typical customer's spend. You reach for AVERAGE, because that's the reflex. But you have one customer who spent $50,000 while everyone else spent a few hundred. The average gets dragged way up by that one number, and now your "typical" customer looks far richer than any real customer is. The formula worked perfectly. The answer is misleading.

MEDIAN — the middle value when you sort everyone — ignores how extreme the outliers are. It just asks who's in the middle. When a few unusual numbers would distort the picture, MEDIAN is the more honest "typical." So the rule: reach for an aggregate when a question starts with how much, how many, or what's typical — and pick MEDIAN over AVERAGE when a handful of outliers would skew the result. That choice isn't syntax. It's judgment, and it's yours.

Making formulas that don't break: named ranges

One more idea, because it's the difference between a formula that survives and one that quietly breaks the first time your data changes.

When you copy a formula down, a relative reference like A2*B2 shifts with it, so each row uses its own values — usually what you want. An absolute reference locks with dollar signs, $A$2, so it always points at the same cell — use it for a fixed rate or a grand total that shouldn't move. And a named range gives a cell a plain-language label. Instead of =B2/$D$1, you write =Sales/Total_Q1_Revenue, which reads like a sentence and never drifts. Update the named range once and every formula using it updates too.

The habit worth building: never hardcode an important number inside a formula. Put it in a cell, name it, and reference the name. Six months later, when the rate changes, you update one labeled cell instead of hunting through fifty formulas for a buried 0.30.

Where AI fits, and its limit

Because this is mostly mechanical, it's a natural place for a tool. In the course, AI-4: Formula Builder takes a plain-language description of what you want to calculate and your column names, and hands back the exact formula plus which reference type or named range to use. That removes the syntax barrier almost entirely — you describe the question, it writes the formula.

But the tool won't tell you that MEDIAN is the honest choice for your skewed customer data, or that "orders over $500" is the right definition of a big customer for your business this quarter. It writes the formula you ask for. Deciding which formula answers the real question — that part doesn't automate, and it's the part actually worth being good at.

The caveat

A short list of formulas answers most questions, not all of them. When you need to compare or summarize across groups — sales by product and region, tickets by month and agent — a pile of COUNTIFs gets unwieldy fast, and a pivot table is the right tool instead. The point of the short list isn't that it's all you'll ever need. It's that it clears the intimidation out of the way so you'll actually start, and covers enough real questions that you'll feel the payoff quickly. Learn these first. Reach for the heavier tools when a question genuinely calls for them.