Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Astonishing that this functionality is not present in such an ancient application

Is there a known workaround?

I'm on about the part where you can change the aggregation type for a value field: It has sum, min, max, avg etc but not median

share|improve this question
IF memory serves, Excel's aggregate functions are closely related to SQL's aggregate functions-and you won't find a Median function there either. A SQL work-around may give some insight into creating an Excel Pivot table version. – dav Aug 24 '12 at 17:10
You may be able to leverage Powerpivot to do what you need, see this article:…. – dav Aug 24 '12 at 17:14

For simple examples you can use array formulas instead of a PivotTable.

If you have the source data in rows 10:1000, category designations of the source data in column A, the source data values in column C, and the category being considered in G3, the following array formula will find the median:

  • {=MEDIAN(IF($A$10:$A$1000=G3,$C$100:$C$1000))}

Commit the entry with Ctrl+Shift+Enter, and copy down for the categories in G4, G5 etc

share|improve this answer
Looks like there is typo for value where instead of $C$10, $C$100 is there. – jhamu Jun 9 at 23:23
Thanks jhamu - now corrected. – Levi Jul 3 at 19:35

Unfortunately, there is nothing built in to excel's pivot table function that will do this. You could try this add-on though. It claims to be able to do it, but I've never used it.

You could do the median work with the data and then include it in pivot table data, but at that point.. you know.. what's the point of the table..

share|improve this answer

there's a way to do median (or any formula) if your pivot table is simple (does not have sub totals in the table). other than that, there's no other way to gracefully include median calcs in your pivot table.

too bad can't include attachments... i'll do a snapshot of my example:

i used the offset function described here: also, download the workbook and go to the pivot table tab - that's where i did my example

this is the end result - see image here:

enter image description here

NOTE: you cannot put the median/offset formula cell in the same column that you are grabbing median value from. ALSO, the offset allows you to grab median value from the value of the column less the grand total row.

unfortunately, there's no graceful way to do median (or any other) calc when you're working with a more complex pivot table (i.e., that has subtotals in the pivot table).

share|improve this answer

You can actually use the iserror function to exclude the total rows from medians. For example, if the Total row labels are in row A and the data you want the median of is in row I:

share|improve this answer

I have found a turnaround if you want to quickly obtain some specific median values in your pivot table: Not perfect, but it could help in some situations..

  • Double click in your pivot table on the aggregated value you want the median calculated
  • Excel open a new sheets with all the rows behind this aggregated value
  • Select the desired range of values and click on "Data Analysis" in the Excel's Data tab (you may need to activate it first)
  • Choose "Descriptive Statistics"
  • Adjust carefully your parameters (See Help of this function)
  • Excel will calculate a whole bunch of Descriptive Statistics in a new sheet by default
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .