Statistical Functions in Excel (Beginner-Friendly Guide)
Basic statistical ideas
When you start working with data, you’ll quickly realize it’s not enough to just look at raw numbers—you need ways to summarize and interpret them. That’s where statistics comes in.
Now, don’t worry—I’m not about to drown you in formulas from a math textbook. Instead, we’ll look at a few basic statistical ideas and see how Excel makes them super easy with built-in functions.
By the end of this post, you’ll know how to calculate averages, count values, measure variation, and rank data—all with a few simple formulas.
1. Averages (Finding the “Typical” Value)
What it means (concept):
An average (mean) is the sum of all values divided by the number of values. Sometimes, though, you don’t want the average of all rows—just those that meet specific conditions (e.g., average sales for the “North” region).
Technical explanation:
AVERAGEIF(range, criteria, [average_range])
range: The cells to test against the condition.
criteria: The condition to apply (e.g., "North", ">500").
average_range: The actual numbers to average (optional; if omitted, Excel averages the range).
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], … )
Similar, but allows multiple conditions.
Example:
=AVERAGEIF(B2:B100, "North", C2:C100) → Average of sales (C2:C100) where region (B2:B100) = “North”.
=AVERAGEIFS(C2:C100, B2:B100, "North", D2:D100, ">500") → Average sales in North region where sales > 500.
2. Counting Values (How Many?)
What it means (concept):
Counting is one of the simplest but most powerful tools in analysis. These functions count the number of rows that match certain conditions.
Technical explanation:
COUNTIF(range, criteria)
range: The cells to test.
criteria: The condition (e.g., "North", ">1000").
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], … )
Counts cells that satisfy multiple conditions.
Example:
=COUNTIF(B2:B100, "North") → Number of rows where region = North.
=COUNTIFS(B2:B100, "North", C2:C100, ">1000") → Number of North region sales above 1000.
3. Standard Deviation (Measuring Spread)
What it means (concept):
Standard deviation measures how spread out your data is compared to the average.
Low standard deviation → values are close to the mean.
High standard deviation → values are widely spread.
Technical explanation:
STDEV.P(number1, [number2], … )
Calculates standard deviation for an entire population (all data points are available).
STDEV.S(number1, [number2], … )
Calculates standard deviation for a sample (a subset of the data).
(Behind the scenes: Excel computes variance = average of squared deviations from mean, then takes the square root.)
Example:
=STDEV.P(A2:A100) → Standard deviation of all sales (population).
=STDEV.S(A2:A100) → Standard deviation when A2:A100 is just a sample.
4. Ranking Data (Who’s on Top?)
What it means (concept):
Ranking helps you order values in a dataset, such as finding the top 3 salespeople or bottom 5 products.
Technical explanation:
RANK(number, ref, [order])
number: The value you want to rank.
ref: The full range of numbers to compare against.
order: Optional. 0 = descending (highest = rank 1). 1 = ascending (lowest = rank 1).
Example:
=RANK(A2, $A$2:$A$100, 0) → Rank of A2 in the list (highest number = 1).
=RANK(A2, $A$2:$A$100, 1) → Rank of A2 in the list (lowest number = 1).
Putting It All Together (Mini Scenario)
Imagine you’re analyzing sales data:
Use AVERAGEIF() to get the average sales in the North region.
Use COUNTIFS() to find how many South region sales exceeded 1000.Use STDEV.S() to measure how consistent sales are across different salespeople.
Use RANK() to identify the top 5 performers.
In just a few formulas, you’ve summarized, filtered, and ranked a dataset that would otherwise be overwhelming.
Final Thought
Statistical functions are the bridge between raw data and analysis. They help you spot patterns, compare results, and explain what’s really happening in your dataset.
Think of them as your first real step into data analysis – and the best part? You don’t need to be a statistician to use them.
— Anastasia Nmesoma
Want to learn faster?
I put together the exact study pack I wish I had when I started learning Data Analytics. It includes:
My full beginner roadmap
Curated course links
Practice projects for each topic
KPIs companies actually use
Covers Statistics, Excel, SQL, Power BI, Tableau, Python, Data Warehousing, Documentation & more