How to Calculate Averages in Excel: A Step-by-Step Guide

Excel averages look simple until they aren’t. Sure, you can average a few numbers in a row and call it a day. But real spreadsheets? They come with blanks that are “totally not missing data,” zeros that mean “not applicable,” and error values that show up like uninvited guests at a potluck.

This guide walks you through calculating averages in Excel step by stepfrom the basic AVERAGE function to conditional averaging with AVERAGEIF and AVERAGEIFS, plus practical tricks for excluding zeros, ignoring errors, and averaging only visible (filtered) rows. Along the way, you’ll get clear examples you can copy, and a few jokes to keep your spreadsheet spirits alive.

What “Average” Means in Excel (And Why It Sometimes Feels Personal)

In everyday life, “average” usually means the arithmetic mean: add everything up and divide by how many items you have. Excel mostly agrees. The classic approach is:

• Mean (arithmetic average) = sum of values ÷ count of values

The important part is what counts as a “value.” Excel can treat blanks, text, logical values (TRUE/FALSE), zeros, and errors differently depending on the function you use. If your average seems “off,” it’s usually because Excel is counting something you didn’t meanor ignoring something you assumed it would include.

Step 1: Calculate a Basic Average with AVERAGE

The fastest way (formula)

Suppose you have numbers in cells B2:B11. To calculate the average:

Press Enter. Congratsyou have an average. Your spreadsheet is now statistically judgmental.

What AVERAGE includes (and ignores)

• Includes: numbers (including 0)
• Ignores: blank cells and text in the referenced range
• Breaks on: error values like #DIV/0!, #N/A, #VALUE!

Translation: blanks usually won’t mess you up, but errors absolutely will. If even one cell in the range is an error, AVERAGE returns an error too (Excel’s way of saying, “I refuse to do math in these conditions”).

Step 2: Use Excel’s Built-In “Average” Buttons (No Formula Needed)

Option A: Status Bar average (quick check)

1. Select a range of cells with numbers.
2. Look at the bottom-right status bar. You’ll often see Average, Count, and Sum.

This is great for quick sanity checks. It doesn’t place a formula anywhereit’s like Excel whispering the answer and then pretending it never happened.

Option B: AutoSum dropdown

1. Click the cell where you want the result.
2. Go to Home → click the Σ AutoSum dropdown.
3. Select Average.
4. Excel will suggest a range. Confirm or adjust, then press Enter.

If Excel highlights the wrong range, don’t panicjust drag to select the right cells. Excel is helpful, but it’s also confident in the way that a toddler is confident about wearing rain boots to a wedding.

Step 3: Average Across Rows or Columns Like a Pro

Average a row

If a student’s quiz scores are in C5:F5:

Average a column

If all quiz scores are in F2:F200:

Tip: Use Excel Tables for cleaner formulas

If your data is in an Excel Table (Insert → Table) with a column named Score, your formula can look like:

Structured references are easier to read and less likely to break when you add more rowslike buying shoes with room to grow, except it’s for data.

Step 4: Calculate an Average That Includes Text or TRUE/FALSE (When You Actually Want That)

Most of the time, you want a numeric average. But if your range contains logical values or numbers stored as text, Excel has alternatives:

• AVERAGEA can include logical values and text representations of numbers in certain contexts.
• AVERAGE is stricter and typically ignores text and blanks.

Use these carefully. If you didn’t intend to include TRUE/FALSE or text, don’t. Nobody wants an average that’s secretly half made of vibes.

Step 5: Average Only the Values That Meet a Condition with AVERAGEIF

AVERAGEIF returns the average of cells that meet one criterion. It’s perfect for questions like: “What’s the average score for students who passed?” or “What’s the average sales for the West region?”

Syntax (human version)

Example 1: Average scores greater than or equal to 70

Scores are in B2:B21:

Example 2: Average sales for a specific region

Region is in A2:A100 and Sales is in B2:B100. Average sales for “West”:

Example 3: Average but ignore zeros

If zeros represent “no score” or “not applicable,” use:

This averages only non-zero values, which is often what people mean when they say, “Please calculate the average like a reasonable person.”

Step 6: Average with Multiple Conditions Using AVERAGEIFS

AVERAGEIFS averages values that match multiple criteria. Think: “Average sales for West region and Product A and January.”

Syntax (still readable, promise)

Example: Average sales for West region and Product A

Regions in A2:A500, Product in B2:B500, Sales in C2:C500:

Example: Average scores between 70 and 90

Scores in B2:B200:

Yes, the criteria range repeats. No, Excel won’t judge you for it. It’s just how the function is built.

Step 7: Handle Blanks, Zeros, and Errors Without Losing Your Mind

Blanks

AVERAGE typically ignores blank cells, so blanks often don’t require special handling. But you should still confirm whether blanks mean “missing” or “zero” in your context. (Your spreadsheet has feelings, but your boss has deadlines.)

Zeros

If zeros should not be included, use AVERAGEIF:

Errors (#N/A, #DIV/0!, etc.)

Errors will make AVERAGE fail. Common approaches include:

• Fix the source of the error (best long-term solution).
• Ignore errors while averaging using functions designed to skip errors (more on that next).
• Wrap calculations that might error with IFERROR so the final range doesn’t contain errors.

Step 8: Average While Ignoring Errors with AGGREGATE (The “Bouncer” Function)

If you want an average but need to ignore errors, AGGREGATE can help. It’s like telling Excel: “Only let valid numbers into the club.”

One commonly used pattern for average while ignoring error values:

In this setup:

• 1 = the function number for AVERAGE
• 6 = option to ignore error values (so errors don’t blow up your result)

This is especially useful in messy spreadsheets where errors are expected (like imported data, sensor readings, or anything involving humans).

Step 9: Average Only Visible (Filtered) Rows with SUBTOTAL

Ever filter a table and think, “Cool, now average just what I’m seeing”? That’s what SUBTOTAL is for.

To average only visible rows (commonly used with filtered lists):

That 1 indicates the AVERAGE operation within SUBTOTAL. When you filter rows out, SUBTOTAL updates to reflect just the visible values. It’s like a polite average that respects your filters.

Step 10: Real-World Examples You Can Steal (Professionally)

Example A: Average monthly sales for a rep, excluding zero months

If monthly sales are in D2:D13 and zeros mean “no data reported,” use:

Example B: Average order value for “Completed” orders only

Status in C2:C1000, Order Value in E2:E1000:

Example C: Average score for a class, only for quizzes in January

Dates in A2:A500, Scores in B2:B500. If you store real Excel dates, you can use criteria like:

This approach is more robust than typing “1/1/2026” as text, especially if your file travels across systems with different date formats.

Common Mistakes (So You Don’t Have to Learn the Hard Way)

• Averaging the wrong range: double-check the highlighted range when Excel auto-suggests.
• Zeros pretending to be data: decide whether 0 is a true value or a placeholder and use AVERAGEIF if needed.
• Errors in the range: one error can ruin your whole averageuse AGGREGATE or clean the data.
• Text numbers: “100” stored as text won’t behave like 100 in all cases. Convert text-to-number where possible.

Conclusion: Your Average Should Match Your Intent

The best Excel average isn’t the fastest oneit’s the one that matches what you actually mean. Start with AVERAGE for clean data. Use AVERAGEIF when one condition matters. Step up to AVERAGEIFS for multi-criteria reality. And when errors, filters, and weird placeholders show up, bring in AGGREGATE and SUBTOTAL like the spreadsheet superhero duo they are.

If your averages ever look suspicious, remember: Excel is rarely “wrong.” It’s just following instructions you didn’t realize you gave it. (Which is also how pets interpret “go ahead” in the kitchen.)

Extra: of Real-World Excel Average “Experiences” (A.K.A. Things You’ll Absolutely Run Into)

Let’s talk about the part no one includes in a neat tutorial: what it feels like to calculate averages in Excel when your data is messy, your deadline is rude, and someone just Slacked you a CSV named “final_FINAL2_use_this_one(1).csv”.

First, you’ll discover the “average that looks wrong.” You calculate =AVERAGE(B2:B200) and get a number that makes no sense. Your first instinct is to blame Excel. Your second instinct is to blame the person who built the spreadsheet in 2017 and vanished into the mist. Then you notice half the cells are zerosnot real zeros, but “we didn’t get the value yet” zeros. That’s when =AVERAGEIF(B2:B200,"<>0") becomes your new best friend. It’s the Excel equivalent of saying, “Let’s only count the answers that are actually answers.”

Next comes the “error landmine.” Everything seems fine until one cell throws #DIV/0! because someone divided by a blank cell. Suddenly your average explodes into an error too, like a dramatic soap opera plot twist. This is when you either fix the root cause (ideal) or, when time is short, you reach for AGGREGATE to ignore errors. The relief is realyour dashboard stops screaming, your report looks normal again, and nobody has to know your data is held together with formulas and optimism.

Then you’ll meet the “filtered table illusion.” You filter your table to show only West region sales, glance at your average cell, and realize it didn’t change. That’s the moment you learn the difference between AVERAGE (doesn’t care about your filter feelings) and SUBTOTAL (respects the filter). Using =SUBTOTAL(1,SalesRange) can feel like discovering a secret menu item.

Finally, there’s the “criteria rabbit hole.” You start with one conditionaverage sales for West. Great. Then someone asks, “Can you do West, Product A, and only January, but exclude returns?” Welcome to AVERAGEIFS. At first, the syntax looks like a long grocery list, but once you get used to pairing criteria_range with criteria, it clicks. The best part is how scalable it is: new requirement? Add another range/criteria pair. It’s the rare Excel moment where more complexity is handled with a method instead of a meltdown.

In short, calculating averages in Excel isn’t just mathit’s communication. You’re telling Excel what you mean, and Excel is saying, “Cool. Be specific.” Once you learn the right functions for the right situations, your averages stop being suspicious numbers and start being trustworthy insights.