How to Find the Median in Excel for Office 365

Finding the median of a dataset in Excel is a fundamental task for anyone working with numbers, whether for business analysis, school projects, or personal finance. The median is the middle value in a set of numbers, giving you a better sense of central tendency than the average in some cases, especially when there are outliers.

In this guide, you’ll learn how to calculate the median in Excel for Office 365, with examples, tips, and tricks for accurate results.

📊 What Is the Median?

The median is the number that separates a dataset into two equal halves:

50% of the numbers are below the median.
50% of the numbers are above the median.

Unlike the average, the median is less affected by extreme values, making it especially useful for uneven datasets.

🔹 Step 1: Open Excel and Enter Your Data

Launch Microsoft Excel (Office 365 version).
Enter your data in a single column (e.g., A1:A10) or single row (e.g., B1:J1).

Example dataset in column A:

A
12
18
25
30
45

🔹 Step 2: Use the MEDIAN Function

Excel has a built-in function called MEDIAN to calculate the middle value.

Syntax:

number1, number2… – These are the numbers or cell ranges you want to include in the calculation.

🔹 Example 1: Median of a Column

If your data is in A1:A5, do this:

Click on an empty cell where you want the median to appear.
Enter the formula:

Press Enter.

Result: 25 (the middle value of the dataset)

🔹 Example 2: Median of a Row

If your numbers are in B1:J1, use:

Excel will automatically calculate the middle value for the row.

🔹 Example 3: Median of Non-Contiguous Cells

You can also calculate the median of cells that aren’t next to each other.

Excel will find the median of these specific values only.

🔹 Step 3: Handling Empty or Non-Numeric Cells

The MEDIAN function in Excel ignores empty cells or text automatically, so you don’t have to delete them manually.

For example, if your dataset is:

A
10
20

40
Text

Using =MEDIAN(A1:A5) will still correctly return 20.

🔹 Step 4: Median of Filtered Data (Optional)

If you’re using Excel tables or filters and want the median of only visible rows, you’ll need a more advanced formula:

Press Ctrl + Shift + Enter to enter it as an array formula in older versions of Excel. In Office 365, it works normally as a dynamic array formula.

This ensures that hidden or filtered-out rows are not included in your calculation.

🔹 Step 5: Tips for Using Median in Office 365 Excel

Use Table Names for Easy Reference:
If your data is in a table named Sales, use:

=MEDIAN(Sales[Amount])
Combine MEDIAN with IF:
To find the median of numbers meeting a certain condition:

=MEDIAN(IF(B2:B20>50, B2:B20))

Remember to use Ctrl + Shift + Enter if necessary in older versions.
Visualize Median:
Use conditional formatting or charts to highlight the median for quick analysis.

🧠 FAQ – Median in Excel

Q1. Can Excel calculate median for large datasets?
Yes, Excel can handle tens of thousands of rows without issues using the MEDIAN function.

Q2. What if there’s an even number of values?
Excel will automatically average the two middle numbers to return the median.

Q3. Can I find the median across multiple sheets?
Yes, use:

Q4. How is MEDIAN different from AVERAGE?

MEDIAN: middle value, less sensitive to outliers.
AVERAGE: arithmetic mean, can be skewed by extreme numbers.

🏁 Final Thoughts

Finding the median in Excel for Office 365 is simple with the MEDIAN function. Whether you have a single column, multiple rows, or non-contiguous data, Excel handles it efficiently. Using these steps, you can: