Oct 4, 2023

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.

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:

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.

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.**

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.

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

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.

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!

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:

You can view our sample file without signing up for Gigasheet, as Gigasheet makes sharing data easy.

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!

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.

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!

No Code

No Database

No Training