The flexibility and power of a pivot table, or "PivotTable" if you prefer the trademarked Microsoft Excel name, make it the preferred tool for data analysis. You can practically do anything with a pivot able!
Well, there is one thing that a PivotTable in Excel cannot do: calculate the median of your data values. You likely tried to hit Value Field Settings to change from the default Sum and were surprised that Median is not an option.
Why is median important? Well unlike the average, the median is not going to be skewed by outlier values and can be more useful depending on the purpose of your analysis.
Consider the following simple example: 5, 5, 50, 5, 5. The median of the numbers is 5, but the average of 14 is elevated by the outlier value of 50.
Well, the good news is that there is a workaround in Excel that we will explore below. However, there is an easier way to calculate medians of pivot groups using Gigasheet, the big data spreadsheet.
Sample Data for Calculating Median
In this blog, we are going to explore a set of British Airways Passenger Reviews from 2016 - 2023, where passengers left a star rating of 1 to 5. Feel free to read the reviews as there are some real gems in there:
✅ Trip Verified | The worst experience in all my years of travel. Bag was destroyed, flight delayed 3 hours and couldn't get water on board. Then when attempting to claim on their website the entire site failed on a dropdown menu that wouldn't work. You can't contact them. Plane was filthy and someone else's belongings and left over food was still in our seat.
However, we are going to focus on analyzing the median star ratings across different fields, such as country and seat type (Economy or Business).
Naturally we would turn to Pivot Tables for this analysis! But how will we calculate the median?
Our goal is to create an output in Excel like this:
Excel Workaround: Steps to Calculate Median in an Excel Pivot Table
1. Insert a PivotTable the way you normally would.
Select the data, hit Insert > PivotTable > From Table / Range and then OK.
Even though Excel cannot directly calculate the median in the PivotTable, we can use the PivotTable to group the values.
2. Set up the pivot table using Average as a placeholder calculation.
We are going to aggregate star ratings based on country, so let's organize the data for analysis. Drag Country to the Rows group and Stars to the Sum of Values box. Use Value Field Settings to change the calculation to Average.
3. Copy and paste the Row Labels into the column next to the PivotTable.
This is where things start to get a little funky and we are implementing a workaround. Make sure to use Paste > Paste as Values! The Row Labels will now be duplicated outside of our pivot table.
4. Use a Formula consisting of IF and MEDIAN to calculate the median for each Row Label.
This formula is going to work indivually on the pasted Row Labels, and looking them up in our original data table named "British Airways Passenger Revie", and then calculating the median on the matches. Here is the actual formula:
=MEDIAN(IF('British Airways Passenger Revie'!C:C=C4,'British Airways Passenger Revie'!F:F))
Let's break down that down, starting with the IF() statement:
IF the Country column (C:C) in the table of reviews equals to the value C4, Then return the value from the Stars column (F:F)for that row.)
Since we selected the entire column C:C and F:F, it will perform that evaluation on every row within the table of reviews, building an Array of values, which is a list of numbers.
The value in the first row, C4, is "Vietnam". Our if statement will find every row where the Country column (C:C) equals "Vietnam" and build a list of values from the Stars column (F:F).
Once the formula has the array, the MEDIAN is calculated and returned into the cell.
5. Finally, apply the formula to every Row Label beside your pivot table.
You can click and drag the formula down to the empty cells, or use copy and paste to duplicate the formula to all cells beside the PivotTable.
And that worked, but it required a complicated formula and now any changes to the pivot table will overwrite the calculations. There’s got to be an easier way!
The Easy Way to Calculate Median in a Pivot Table
Until Microsoft decides to add Median to the Value Field Settings menu, there is not a way to calculate median within an Excel PivotTable. However, it can easily be done in other spreadsheets! In Gigasheet, it’s only 3 simple steps:
1. Create a free account and upload your file to Gigasheet.
You can view our sample file without signing up for Gigasheet, as Gigasheet makes sharing data easy.
2. Group the data
Unlike Excel, there is no need to set up a pivot table in Gigasheet! To group data by country simply hit the Group button and select the column Country.
The raw data has been replaced by aggregations. Our review data is loaded ready for analysis!
3. Choose Median and be amazed!
Simply select an aggregation at the bottom of the Stars column, and boom, we have our answer.
Aggregations are that easy! Not only does Gigasheet offer MEDIAN, but there are additional options for immediate use such as MIN, MAX, and UNIQUE to name a few.
Oh, and Gigasheet does offer a traditional Pivot Mode, in case you were experiencing separation anxiety. Simply hit the Pivot Mode slider and you are presented with familiar pivot table options such as draggable columns, Row Groups, Sum or Values, and Column Groups.
With Column Groups, we can do something interesting like show the median by country AND seat class. Simply drag seat type down into the Column Group box.
That would be difficult to do using the Excel workaround. We would definitely have overwritten our formula!
Its safe to say that Gigasheet offers the easy way to calculate median in a Pivot Table.
Final Thoughts
Median is the aggregation that is really helpful when the average is being skewed by outlier values. While Excel does not allow you to calculate Median in a PivotTable, there is a workaround that is serviceable, but is not ideal. If you want to calculate median in a Pivot Table the easy way, try Gigasheet for free!
The ease of a spreadsheet with the power of a data warehouse.
