The mean is the workhorse of spreadsheet analysis, and Excel makes it almost embarrassingly easy to compute one. Type =AVERAGE(A1:A10), hit Enter, and you get the arithmetic mean of whatever numbers live in that range. That is the headline. Everything else, the trimmed means, the conditional means, the weighted means, the means that ignore zeros or skip text, is just Excel giving you more control over which numbers actually count.
If you have used SUM, you already know how AVERAGE behaves. It accepts ranges, individual cells, named ranges, and entire columns. It ignores text and blank cells but, and this trips people up, treats logical TRUE as 1 and FALSE as 0 only when you pass them directly. References to cells holding TRUE or FALSE are ignored. The function has been around since Excel 1.0, and Microsoft has built six more variations on top of it for the situations where vanilla AVERAGE falls short.
This guide walks you through every practical way to calculate a mean in Excel, from the keyboard shortcut almost nobody uses to the array formulas that handle million-row datasets without flinching. Whether you are sitting an MOS Excel Expert exam, building a finance dashboard, or just trying to figure out your average monthly grocery bill, the formulas below will get you there. Bookmark this page, because at some point you will need every single one.
Before you fire off formulas, get clear on what kind of average you want. Excel does not assume. The mean, the median, and the mode are three different beasts, and the function names matter. =AVERAGE() returns the arithmetic mean: sum of values divided by count of values. =MEDIAN() returns the middle value once everything is sorted. =MODE.SNGL() returns the most frequent value. People say "average" when they mean any of these. Excel will not correct you.
The arithmetic mean is the sensible default for most situations. Daily sales, exam scores, monthly temperatures, response times, you reach for AVERAGE. But the moment your data is skewed, the moment one outlier yanks the result sideways, the mean stops being honest. That is when MEDIAN earns its keep, or when you switch to TRIMMEAN and explicitly chop off the top and bottom slices before averaging the middle.
Pick the right tool. If a recruiter asks for your average commute and one day you got stuck behind a marathon for three hours, the mean tells one story and the median tells another. Excel is happy to compute both, side by side, in two adjacent cells. That is the move.
Select your range, look at the bottom-right of the status bar, and Excel already shows the average, count, and sum without a single formula. Right-click the status bar to add min, max, and numerical count. For one-off checks, this beats typing any formula. For anything you need to repeat or reference later, write the formula. The status bar updates instantly as you change your selection, which makes it a fantastic exploratory tool when you are still figuring out which slice of the data you actually want to average. Just remember it is read-only — you cannot copy the value out, you have to write the formula for that.
Here is the no-nonsense walkthrough. Open your workbook, click the empty cell where you want the result, and type an equals sign followed by AVERAGE and an opening parenthesis. Excel will pop up an argument hint. Now select your range, either by dragging or by typing the cell addresses. Close the parenthesis and press Enter. Done.
Say your monthly revenue numbers sit in B2:B13. In any blank cell write =AVERAGE(B2:B13) and Excel returns the mean monthly revenue. Want the mean of two non-adjacent ranges? Use a comma: =AVERAGE(B2:B13, D2:D13). Want to mix individual cells with ranges? Same syntax: =AVERAGE(B2, B5, B8:B13). Excel ignores any cell that holds text, an error value, or nothing at all. It does NOT ignore zero, so a cell with 0 pulls your mean down. Keep that in mind.
The keyboard shortcut almost nobody uses lives on the AutoSum dropdown. Hit Alt + = to summon AutoSum, then click the small arrow next to it and pick Average. Excel guesses your range, you confirm or adjust, hit Enter. This is faster than typing the function name once you internalize the muscle memory. On Mac the shortcut is the same key combination.
Imagine a column of student test scores running A2 to A21, twenty values, one per row. You want the class mean. Click A23, type =AVERAGE(A2:A21), press Enter. Excel returns, say, 76.8. To round that to a whole number, wrap it: =ROUND(AVERAGE(A2:A21), 0). To display it as a percentage, format the cell or use =AVERAGE(A2:A21)/100. To compute the mean only for scores above 50 (passing), pivot to AVERAGEIF, which we cover below.
Plain arithmetic mean of a range. Ignores text and blanks but counts zeros as valid numbers. The default choice for everyday calculations and the one most exam questions assume you will use.
Like AVERAGE but counts text values as zero and logical values as 0 or 1. Useful for mixed-content columns where blank entries genuinely should drag the mean down.
Mean of cells that meet one condition. Syntax: range, criteria, optional average_range. Supports wildcards like * and ? for partial text matches.
Mean of cells meeting multiple conditions. Up to 127 criteria pairs supported. Average range comes first, then alternating criteria range and criteria value pairs.
Drops a percentage of the highest and lowest values before averaging the rest. Built for noisy data with outliers, used by Olympic judges.
Manual mean for weighted or complex scenarios. Total flexibility but more typing. The right tool for weighted grades, portfolio returns, and survey aggregates.
Real data is messy. You rarely want the mean of every number in a column. You want the mean of sales above $1,000. The mean for the Texas region. The mean monthly close that fell on a Friday. That is what AVERAGEIF and its bigger sibling AVERAGEIFS exist for.
The single-condition version: =AVERAGEIF(B2:B100, ">1000") averages only the cells in B2:B100 that exceed 1000. If you want the average of one column based on a condition in another, supply the optional third argument. =AVERAGEIF(A2:A100, "Texas", B2:B100) reads as: look at A2:A100, find every row where the value is Texas, and average the corresponding cells in B2:B100. Wildcards work too. =AVERAGEIF(A2:A100, "Tex*", B2:B100) catches Texas, Texan, Texarkana, anything starting with Tex.
For multiple conditions, switch to AVERAGEIFS. The syntax flips: the range to average comes first, then pairs of criteria range and criteria. =AVERAGEIFS(B2:B100, A2:A100, "Texas", C2:C100, ">2025-01-01") averages B2:B100 where A is Texas and C is after January 1st 2025. You can stack up to 127 criteria pairs. That is more than anyone will ever need, but it is there.
Date criteria need quotes and the right operators. ">="&DATE(2025,1,1) is safer than typing the date as a literal string. Text criteria are case-insensitive: "texas" matches "Texas". Numbers as criteria work without quotes for equality but need quotes with operators: 1000 versus ">1000". If your criteria range and average range are different sizes, Excel either errors out or silently returns wrong results, so always match dimensions exactly.
Zeros drag your mean down. To exclude them: =AVERAGEIF(B2:B100, "<>0"). The criteria <>0 means not equal to zero. This is one of the most-asked Excel questions on every forum, and the answer is one short formula. Use it whenever your range contains placeholders or default zero entries that should not influence the average.
If your range has #N/A or #DIV/0! values, AVERAGE returns an error. Wrap it: =AVERAGEIF(B2:B100, "<>#N/A") or for any error use =AGGREGATE(1, 6, B2:B100). The 6 tells AGGREGATE to ignore errors. AGGREGATE is the modern choice because it handles every error type in one shot.
Use LARGE inside AVERAGE as an array: =AVERAGE(LARGE(B2:B100, {1,2,3,4,5})). In modern Excel (365 / 2021+) this works without Ctrl+Shift+Enter. In older versions, confirm as an array formula. Swap LARGE for SMALL to average the bottom five instead.
When you filter a table, AVERAGE still includes hidden rows. Switch to =SUBTOTAL(1, B2:B100) or the more flexible =AGGREGATE(1, 5, B2:B100). The 5 in AGGREGATE means ignore hidden rows. This is the right choice for live dashboards that update as users change filter selections.
The arithmetic mean assumes every value carries equal weight. That assumption breaks for grades (a final exam counts more than a quiz), for portfolio returns (a large holding moves the average more than a small one), and for survey results (responses from larger groups should pull harder). For these, you need the weighted mean.
The formula in plain English: multiply each value by its weight, sum those products, divide by the sum of the weights. In Excel that is one line with SUMPRODUCT: =SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10), where B holds your values and C holds the weights. SUMPRODUCT multiplies the two arrays element-by-element and adds the results, exactly what the math demands. Divide by the sum of weights and you have your weighted mean.
A grading example makes it concrete. Say a course has four assessments with weights 10%, 20%, 30%, 40%. A student scores 85, 78, 92, 88. The weighted final grade is =SUMPRODUCT({85,78,92,88}, {0.1,0.2,0.3,0.4}), which works out to 86.9. The arithmetic mean of those four scores is 85.75. The difference matters when grading scales are tight.
If your weights do not sum to 1 or 100%, no problem. The /SUM(C2:C10) step normalizes them. You can use raw weights like 1, 2, 5, 7, and the math still works out. This is the move when you have unequal sample sizes or response counts and want a single representative average.
Cross-sheet means are simpler than people fear. To average the same cell across multiple worksheets, use the 3D reference syntax: =AVERAGE(Sheet1:Sheet12!B5). That averages cell B5 from Sheet1 through Sheet12, every sheet in between included. The colon between sheet names is the giveaway, it is acting like a range, but for sheets instead of cells. This is gold for monthly reports stored one per tab.
For different ranges on different sheets, list them out: =AVERAGE(Q1!B2:B100, Q2!B2:B100, Q3!B2:B100, Q4!B2:B100). Verbose, but it works. Sheet names with spaces or special characters need single quotes around them: ='Sales Data'!B2:B100.
Cross-workbook is similar but the source file should ideally be open, otherwise Excel stores a path that breaks if files move. The syntax: =AVERAGE('[OtherFile.xlsx]Sheet1'!B2:B100). For anything more involved than two or three external references, Power Query is a far better path. It pulls data into a unified table and then a single =AVERAGE() on that table covers everything.
You have a workbook with one tab per month, January through December, each tab structured identically. You want the mean of column B for the whole year. Option one: write =AVERAGE(January:December!B2:B100) on a summary sheet. Option two: build a summary sheet that pulls each month's mean and then average those means (mathematically not the same unless monthly counts are equal, but often close enough). Option one is usually right. It treats the entire year as one big dataset, which is what you almost certainly want.
Sometimes one extreme value blows up your mean. A single $50,000 invoice in a column of $200 transactions pulls the average somewhere it should not be. The clean fix is TRIMMEAN, which drops a percentage of the top and bottom values before averaging the rest. The syntax: =TRIMMEAN(B2:B100, 0.1) trims 10 percent total, so 5 percent from the top and 5 percent from the bottom.
The percentage you pass in is the total fraction dropped, split equally. Pass 0.2 to trim 20 percent (10 percent each end), 0.4 for 40 percent total. Excel rounds the number of values to drop down to the nearest multiple of 2, so for a 100-cell range with 0.1 it drops 5 top and 5 bottom. For 99 cells with the same fraction, it drops 4 top and 4 bottom. Predictable but worth knowing.
TRIMMEAN is the standard mean used in Olympic judging events: drop the highest and lowest scores, average the rest. For business data it is equally useful. If you have hourly response times across a year and a few mid-day outages spiked things to 60 seconds, trimming 5 percent gives you a much more honest view of typical performance than the raw mean.
The median ignores everything except the middle value (or middle two). The trimmed mean still uses most of the data, just chops the extremes. For mildly skewed data, trimmed mean usually beats median because it retains more information. For wildly skewed data with long tails, median is the cleaner statistic. Run both, compare them, and use the one that matches the question you are actually trying to answer.
If your data is in a pivot table, you do not write AVERAGE formulas at all. Drag your value field into the Values area, click the dropdown next to the field name, choose Summarize Values By, and pick Average. The pivot table now shows means broken down by every row and column field you have added. That is how you compute mean revenue per region per quarter without writing a single formula.
For dynamic-array Excel (365 and 2021+), GROUPBY and PIVOTBY functions let you build pivot-table-style aggregations directly in cells. =GROUPBY(A2:A100, B2:B100, AVERAGE) returns a two-column spilled range with each unique value from column A and the mean of column B for that group. This is faster than building a pivot table when you just want the numbers.
Older Excel versions need helper columns or array formulas to fake the same thing. The =AVERAGEIF approach we covered earlier handles the simple cases; for anything more complex, the pivot table is still the right tool. Do not torture yourself with multi-line array formulas when two clicks build a pivot table that does the same thing.
For genuinely large datasets, or datasets pulled from multiple files, Power Query (Get & Transform) is the right home. Load your data, group by whatever column you need, and add an aggregation that takes the average. The output is a clean table you can load back into Excel and reference with a one-line AVERAGE if you still need a scalar. Power Query is overkill for a hundred rows. For ten thousand, it is the only sane choice.
Excel gives you a small toolkit for computing means, and each tool has a job. AVERAGE handles the everyday case. AVERAGEIF and AVERAGEIFS slice by condition. TRIMMEAN defuses outliers. SUMPRODUCT divided by SUM handles weights. SUBTOTAL and AGGREGATE respect filters. Pivot tables and GROUPBY aggregate by category without formulas. That is the full lineup, and you can solve almost any mean-related problem with some combination of these.
The most common mistake is reaching for the wrong function out of habit. People type =AVERAGE() when they need =AVERAGEIF(), get a number that looks plausible, and ship it. Always check whether your range has zeros, blanks, text, outliers, or filters in play, because every one of those changes which function is correct. A two-minute audit of the data beats a polished but wrong dashboard every time.
If you are studying for an Excel certification, practice these functions on real data and watch the results change as you tweak ranges and criteria. The exam will not just ask you to write =AVERAGE(A1:A10). It will give you a dataset with intentional traps, zeros where you do not expect them, hidden rows, error values, and ask you to compute the mean correctly.
Knowing which function ignores what is half the battle. The other half is practice, and that is what our Excel quizzes are for. Take a few rounds, find the gaps, fill them, and walk into the exam confident that whatever Excel throws at you, you can compute the mean and move on.