How to Find the Median (Average) in Excel

Using the MEDIAN Function in Microsoft Excel

3D Rendering, Robots crossing zebra crossing
Westend61 / Getty Images

Mathematically, there are a number of ways to measure central tendency or, as it's more commonly called, the average for a set of values. The average being the center or middle of a group of numbers in a statistical distribution.

In the case of the median, it's the middle number in a group of numbers. Half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median for the range"  2, 3, 4, 5, 6" is 4.

To make it easier to measure central tendency, Excel has a number of ​functions that will calculate the more commonly used average values:

  • The MEDIAN function: Finds the median or middle value in a list of numbers
  • The AVERAGE function: Finds the arithmetic mean for a list of numbers
  • The MODE function: Finds the mode or most commonly occurring value in a list of numbers

How the MEDIAN Function Works

The MEDIAN function sorts through the provided arguments to find the value that falls arithmetically in the middle of the group. 

If an odd number of arguments are supplied, the function identifies the middle value in the range as the median value.

If an even number of arguments are supplied, the function takes the arithmetic mean or average of the middle two values as the median value.

Note: The values supplied as arguments do not need to be sorted in any particular order in order for the function to work. You can see that in play in the fourth row in the example image below.

MEDIAN Function Syntax

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

This is the syntax for the MEDIAN function:

=MEDIAN(Number1, Number2, Number3, ...)
  • =MEDIAN: This is how all MEDIAN formulas need to start, so this can't be changed.
  • Number1: Required data to be averaged by the function
  • Number2Optional additional data values to be included in the average. The maximum number of entries allowed is 255, each of which needs to be separated by a comma.

This argument can contain:

Options for entering the function and its arguments:

  • Typing the complete function, like =MEDIAN(A2: F2), into a worksheet cell
  • Entering the function and arguments using the function's dialog box

MEDIAN Function Example

Finding the Middle Value with the MEDIAN Function in Excel
Finding the Middle Value with the MEDIAN Function. © Ted French

These steps detail how to enter the MEDIAN function and arguments using the dialog box for the first example displayed in this image:

  1. Click on cell G2. This is the location where the results will be displayed.
  2. Navigate to the Formulas > More Functions > Statistical menu item to select MEDIAN from the list.
  3. In the first text box in the dialog box, highlight cells A2 to F2 in the worksheet to automatically insert that range.
  4. Click OK to complete the function and return to the worksheet.
  5. The answer 20 should appear in cell G2
  6. If you click on cell G2, the complete function, =MEDIAN(A2: F2), appears in the formula bar above the worksheet.

Why is the median value 20? For the first example in the image, since there's an odd number of arguments (five), the median value is calculated by finding the middle number. It's 20 here because there are two numbers larger (49 and 65) and two numbers smaller (4 and 12).

Blank Cells vs Zero

When it comes to finding the median in Excel, there's a difference between blank or empty cells and those containing a zero value.

As shown in the examples above, blank cells are ignored by the MEDIAN function but not those containing a zero value.

  • The median changes between the first and second examples because a zero was added to cell A3 whereas cell A2 is blank.
  • The addition of a zero to cell A3 changes the number of arguments passed to the function in cell G3 from five to six - an even number. As a result, the median is calculated by adding the two middle values (12 and 20) together and then dividing by two to find their average (16).

By default, Excel displays a zero (0) in cells with a zero value - as shown in the example above. This option can be turned off and, if done, such cells are left blank, but the zero value for that cell is still included as an argument for the function when calculating the median.

Here's how to toggle this option on and off:

  1. Navigate to the File > Options menu (or Excel Options in older versions of Excel).
  2. Go into the Advanced category from the left pane of the options.
  3. On the right side, scroll down until you find the "Display options for this worksheet" section.
  4. To hide zero values in cells, clear the Show a zero in cells that have zero value check box. To display zeros, put a check in the box.
  5. Save any changes with the OK button.