How to Calculate Median of Filtered Rows in Excel

To calculate the median of filtered rows in Excel, you can use the SUBTOTAL function. The SUBTOTAL function can perform various calculations, including median, while considering only the visible (unfiltered) cells in a range.

Here's how to calculate the median of filtered rows using the SUBTOTAL function:

1. Click on an empty cell where you want to display the median of the filtered data.
2. Type the following formula: =SUBTOTAL(101, range) where range represents the range of cells containing the filtered data.
   • The first argument, 101, tells the SUBTOTAL function to calculate the median while ignoring hidden rows (filtered out rows).
   • The second argument is the range of cells containing the filtered data.

For example, if you have filtered data in cells A2:A10, your formula would look like this: =SUBTOTAL(101, A2:A10)

3. Press Enter to get the median of the visible cells in the specified range.

Example

Let's say you have the following dataset in cells A1:B7:

| Product | Price |
|---------|-------|
| A       | 10    |
| B       | 20    |
| C       | 30    |
| D       | 40    |
| E       | 50    |
| F       | 60    |

You have applied a filter to the "Product" column and filtered out "C" and "E". Now, you want to calculate the median of the visible "Price" values.

1. Click on an empty cell, say B8.
2. Type the formula =SUBTOTAL(101, B2:B7) and press Enter.
3. The result in B8 will show the median of the visible cells in the range B2:B7, which is 35. This is the median of the visible "Price" values (10, 20, 40, and 60).