Table of Contents
A pivot table is a powerful tool in Google Sheets that allows users to summarize and analyze data in a table format. One of the useful features of a pivot table is the ability to calculate the median of a data set. To do so, users can simply click on the “Add” button in the “Values” section of the pivot table editor and select “Median” from the drop-down menu. This will automatically calculate the median for each category in the pivot table. This feature is particularly helpful for finding the middle value in a large data set and gaining insights into the distribution of the data.
Google Sheets: Calculate Median in a Pivot Table
The following step-by-step example shows how to create a pivot table in Google Sheets that displays the median value of a certain variable.
Step 1: Enter the Data
First, let’s enter the following data that shows the total revenue generated by certain products in certain regions for some company:
Step 2: Create the Pivot Table
To create a pivot table that summarizes this dataset, click the Insert tab and then click Pivot table:
In the window that appears, type Sheet1!A1:C17 as the Data range and choose to insert the pivot table in the Existing sheet in cell E2:
Once you click Create, an empty pivot table will automatically be inserted.
In the Pivot table editor that appears on the right side of the screen, click Add next to Rows and choose Region.
Then click Add next to Values and choose Revenue:
By default, the pivot table will attempt to summarize the Revenue values by using SUM.
To instead display the MEDIAN of Revenue, click the dropdown arrow under Summarize by and click MEDIAN:
The pivot table will update to show the median value of Revenue for each Region:
For example, we can see:
- The median value in the Revenue column for the East Region is 9.
- The median value in the Revenue column for the North Region is 7.5.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets: