Excel Statistical Functions: AVERAGE, MEDIAN & More

Which function should you reach for when your column of sales numbers has one absurdly high value skewing everything? That's not a trick question, but it is the one that separates people who use Excel statistical functions from people who trust them. By the end of this guide, you'll know how to calculate AVERAGE, MEDIAN, MODE, standard deviation, and more — and, more usefully, you'll know when each one can quietly mislead you.

If you're new to spreadsheets, it helps to start with the Excel for Beginners starter guide before working through these steps. If you're ready to go, open a blank workbook and build a small dataset to follow along: use Q3 sales figures for five reps — Sarah Chen ($48,200), Marcus Rivera ($51,400), James Okafor ($49,800), Diana Lau ($52,100), and one outlier, Todd Briggs ($118,500). That single outlier is going to do a lot of work in this article.

---

Step 1: Choose the Right Center — When to Use AVERAGE, MEDIAN, and MODE in Excel

Descriptive statistics in Excel start with one job: summarize a column of numbers into something meaningful. AVERAGE, MEDIAN, and MODE all do that — they just do it differently, and on the wrong dataset, the difference matters.

How Outliers Make AVERAGE Misleading (and When MEDIAN Tells a Truer Story)

Type =AVERAGE(B2:B6) on the sales dataset above and you'll get roughly $63,980. That's technically correct. It's also useless as a description of what a "typical" rep earned in Q3, because Todd's $118,500 is pulling the number up by more than $14,000 compared to everyone else's range.

Now try =MEDIAN(B2:B6). You get $51,400 — Marcus Rivera's number, sitting right in the middle of the sorted list. That's a much more honest picture of what a normal Q3 looked like. MEDIAN tells you what a typical value looks like without being thrown off by outliers.

If your data has even one value that seems extreme — a bonus, an anomaly, a data entry error you haven't caught yet — MEDIAN is usually more honest than AVERAGE. AVERAGE works well for symmetrical data, like temperatures across a month. For skewed data like income, sales, or real estate prices, MEDIAN is the honest choice.

One thing most tutorials skip: AVERAGE includes zeros but ignores blank cells. A blank cell and a zero are not the same thing statistically. If a rep missed a quarter and their cell is blank, AVERAGE treats them as if they never existed. If someone typed a zero, they get counted. Check your blanks before you trust your AVERAGE.

A zero and a blank cell behave differently inside AVERAGE. A blank is excluded from the calculation entirely; a zero is counted as a real value. Always audit your data for unintentional blanks before running summary statistics.

Where MODE Fits In (and Why It's Rarely the First Function You Should Reach For)

MODE returns the most frequently occurring value in a range. =MODE(B2:B6) on this dataset returns an error because no value repeats, which is the realistic outcome in most financial datasets. MODE is more useful on categorical-style numeric data: survey scores, support ticket ratings, number of items ordered per transaction.

One version note worth knowing: the legacy MODE function only returns the first mode when a dataset has multiple tied values. MODE.MULT, available since Excel 2010, returns all of them as an array. If you're running Microsoft 365, use MODE.MULT whenever you suspect ties.

---

Step 2: Measure Spread with Standard Deviation and Variance (STDEV.S vs. STDEV.P Explained)

Once you know your center, the next question is how spread out your data actually is. Two datasets can share the same AVERAGE and look completely different in practice. That's where standard deviation and variance come in.

What the Difference Between STDEV.S and STDEV.P Actually Means

Use =STDEV.S(B2:B6) when your data is a sample — meaning it represents a subset of a larger group. Use =STDEV.P(B2:B6) only when your data is the entire population, every value that exists, not just a slice.

In practice, most Excel users are working with samples. Your Q3 sales figures represent five reps out of a larger possible pool, so STDEV.S is the right call. STDEV.P would slightly understate the variability — not dramatically, but enough to matter in a report going to stakeholders.

For variance, the same S vs. P logic applies: =VAR.S() and =VAR.P(). Variance is standard deviation squared. It's less intuitive to read directly, but it's used in more advanced statistical analysis, including regression inputs.

If you want to count how many values fall within a specific range before running spread calculations, =COUNTIF(B2:B6,">50000") is a clean way to do it. COUNTIF answers "how many cells meet this condition" — useful for a quick sanity check before you decide which spread function applies. For more on getting your data clean before analysis, the guide on preparing data for analysis in Excel covers it in detail.

---

Step 3: Use PERCENTILE and CORREL to Go Beyond the Basics

With your center and spread calculated, you have a real descriptive picture of your data. Two more functions push that picture further without requiring a statistics degree.

=PERCENTILE(B2:B6, 0.75) returns the value below which 75% of your data falls — the 75th percentile. On the sales dataset, that tells you what a top-quartile rep actually earned, which is a more useful benchmark than AVERAGE alone. The function accepts any value between 0 and 1 as the second argument.

=CORREL(B2:B6, C2:C6) measures the relationship between two columns — say, Q3 sales and number of client calls made. A result close to 1 means they move together; close to -1 means they move in opposite directions; close to 0 means no detectable linear relationship. It's one of the most underused functions in standard reporting.

If you're using FORECAST in legacy files, know that it was replaced by FORECAST.LINEAR in Excel 2016. Both still work in Microsoft 365 for backward compatibility, but FORECAST is deprecated — Microsoft could remove it in a future update. Swap it out before building anything new.

---

Common Mistakes With Excel Statistical Functions

The most expensive mistake is using AVERAGE on skewed data without checking for outliers first. Todd Briggs's $118,500 doesn't just shift the AVERAGE — it makes the average actively misleading for anyone reading your report without context. Check your data distribution before you decide which measure of center to report.

1. Using AVERAGE on skewed data. One extreme value can pull your average far from what's typical. Always check for outliers, then decide whether AVERAGE or MEDIAN fits the story your data is actually telling.
2. Using STDEV.P when you mean STDEV.S. Unless you're measuring every member of a defined group, you're working with a sample. STDEV.P on sample data understates variability, and that understatement compounds in larger analyses.
3. Keeping deprecated functions in new files. If you inherited a workbook that uses FORECAST, it'll still run — but swap in FORECAST.LINEAR before sharing it forward. It's a five-second fix.

If you use the Excel Data Analysis ToolPak, most of these functions have point-and-click equivalents — useful for one-off analyses, less useful for repeatable reports where a formula is the right call. Google Sheets supports AVERAGE, MEDIAN, STDEV, and CORREL with identical syntax, so everything here transfers if you're switching between platforms.

For a real-world application of these functions in an inventory or retail context, the walkthrough on data analysis in Excel for retail inventory shows how AVERAGE, COUNTIF, and STDEV work together in a business setting.

If you take one thing from this article: AVERAGE is fast, but MEDIAN is often more honest. Know which one your data actually needs before you put it in a report.

---