How To Use The Average Function In Excel

Last week, I spent a solid hour manually adding up my coffee expenses for the month. I’m talking receipts spread across my desk like a crime scene, a calculator getting warm from overuse, and a growing sense that I’d forgotten basic math. Then my coworker glanced over, snorted, and whispered, “You know Excel does that in one click, right?” My pride took a hit, but my spreadsheet game leveled up instantly. That’s the moment I fell in love with the AVERAGE function—a tool so simple it feels like cheating.

Let’s be real: most of us avoid Excel formulas because they look like alien code. But the AVERAGE function? It’s the friendly alien who just wants to help you find the middle ground. You don’t need a PhD in data science to use it—just a curious finger and a willingness to click fewer buttons. Trust me, if I can do it while half-asleep with a coffee hangover, you can too.

What Exactly Is the AVERAGE Function?

Think of it as your super-smart friend who instantly sums up a bunch of numbers and divides them by how many there are. Excel does the heavy lifting—no mental arithmetic required. You just tell it where the numbers live, and it spits out the magical middle value.

Technically, it’s written as =AVERAGE( followed by the cells you want to average. But don’t let the parentheses scare you—they’re just digital hugs for your data. You can type it, or find it under the “Formulas” tab under “More Functions” > “Statistical.” But typing is faster. I’m lazy, so I type.

The Bare-Bones Basics (No Jargon, Promise)

Open your spreadsheet and click on the cell where you want the result to appear. Maybe it’s the bottom of a column of test scores or your weekly budget. Then type: =AVERAGE(.

Now, highlight the cells you want to average—say, from A2 to A10. Excel will automatically fill in the range as A2:A10. Close the parenthesis and hit Enter. Boom. You’ve got your average. It’s honestly embarrassing how easy this is compared to manual counting.

Pro tip (from someone who learned the hard way)

Don’t include the cell where you’re typing the formula in your range. I once did that and created a self-referencing black hole that broke my entire sheet. Excel didn’t crash, but my dignity did.

What If Your Numbers Have Holes? (Blank Cells)

Life isn’t perfect, and neither are spreadsheets. If you have blank cells in your range, Excel ignores them automatically. It only counts cells with numbers. So if you’re averaging five numbers but one cell is empty, it divides by four, not five. That’s actually smart—but it can trick you if you’re not paying attention.

Check your data first. If that blank cell represents a zero (like a sale that didn’t happen), type in a 0 manually. Otherwise, Excel assumes it never existed, and your average might look rosier than reality. I learned this when my “average monthly spending” looked weirdly low—turns out I’d missed a $500 receipt.

Going Fancy: Average with Conditions (Yes, Even Fancier)

Sometimes you don’t want the average of everything. You want the average of only the sales from last week, or only the scores above 80. That’s where =AVERAGEIF struts in like a hero with a sequin cape. It lets you set a rule.

For example: =AVERAGEIF(B2:B10, ">80") will average only the numbers in that range that are greater than 80. The syntax is a bit picky—you need the quotes around the condition—but once you get it, you’ll feel like a spreadsheet wizard. I still fumble the quotes sometimes, but that’s okay. Excel will scream an error, and I just try again.

Common Mistakes That Make You Go “Ugh”

We’ve all been there. You get a result that looks like #DIV/0!. Don’t panic—that just means you tried to average zero numbers. If your range is empty or contains only text, Excel throws up its hands. Check that you actually selected cells with numbers.

Another classic: accidentally including a cell with text or a header row. Excel ignores text, but it shifts your range and muddles your count. Always double-check your selection—or better yet, use named ranges if you’re fancy. I’m not fancy, so I just squint.

When Should You NOT Use Average?

Here’s the ironic twist: averages can lie. If your data has extreme outliers—like one billionaire living on a street of paupers—the average income looks wildly unrepresentative. In that case, use MEDIAN instead (Excel has that function too). But for everyday stuff like average temperatures, test scores, or coffee spending, AVERAGE is your BFF.

Just remember: a single number never tells the whole story. But it’s a hell of a lot better than your calculator and a pile of crumpled receipts.

Final (slightly smug) Thought

I now average my coffee costs in under three seconds. That’s time I use to judge myself for my caffeine addiction. You too can reclaim your time—go play with AVERAGE, laugh at the errors, and feel like the subtle genius you were always meant to be. Your desk will thank you.