How to Calculate Median in Excel Pivot Table (2 Easy Ways)

The pivot table feature in Microsoft Excel can be used to sort, analyze, and share data, but pivot tables do not calculate median values, which can be important for analyzing growth in an organization. Below, you will learn how to calculate the median in a pivot table.

1- overview image of how to calculate median in excel pivot table

How to Activate Power Pivot in Excel

In order to use Power Pivot, you must first activate it.


  • Go to File>> then select Options.
  • A dialog box named Excel Options will pop up.
  • Click on Add-ins.
  • Then select COM Add-ins from the Manage drop-down and click on Go.

2- selecting COM Add-ins from the Manage drop-down to activate power pivot in Excel

  • Check the box before Microsoft Power Pivot for Excel and click OK.

3- activating power pivot in Excel

Thus you can activate the Power Pivot in Excel.

To show you 2 simple ways of calculating the median in Excel Pivot Table, here is a concise dataset consisting of Student Names, Student IDs, and marks.

4- dataset to calculate median in Excel pivot table

Method 1 – Calculate Median in Pivot Table by Creating a New Measure Function

Step 1: Creating Pivot Table from Dataset

  • Select the whole dataset.
  • Go to Insert tab>> select Pivot Table under Tables option.

5- selecting Pivot Table under Tables option

  • A dialog box will pop up.
  • Select the Location where you want to see your pivot table.
  • Check the box beside Add this data to the Data Model option.
  • Click OK.

5.5- creating pivot table from dataset

Step 2: Creating a New Measure Function

  • You will see there’s a different icon and we have the ability to create a measure.

6- different icon to calculate measure

  • Right-click on the icon and click on Add Measure option.

7- selecting add measure option to create a new measure function

  • A dialog box called Measure will pop up.
  • Add a Measure Name.
  • In the Formula box, enter the following formula.
  • Select the Category as Number and set Decimal Places as 2.
  • Click OK.

8- creating a new measure function to calculate the median in Excel pivot table

  • A new measure function will be created like the image below.

9- created new measure function to calculate median in Excel pivot table

Step 3: Calculating Median

  • Put Student ID in Rows and Median Marks in Values.

10- calculating median in Excel pivot table

  • You will get your pivot table along with your calculated median.

11- calculated median in Excel pivot table

Method 2 – Calculate Median in Pivot Table Using MEDIAN Function


  • Insert a new column named Median beside the Marks column.
  • Enter the following formula in cell E5.
  • AutoFill the rest of the column’s cells, and you will get the median the same as the previously shown method.

12- calculate median using MEDIAN function

  • Select any cell in the pivot table.
  • Click PivotTable Analyze>> then Change Data Source drop down>> select Change Data Source.

13- selecting change data source option

  • A dialog box named Change PivotTable Data Source will appear.
  • Specify the new source data in the Table/Range box.
  • Click OK.

14- specifying new data range in the Change PivotTable Data Source dialog box

  • In the PivotTable Fields pane, the Median field is now available.
  • Drag the Median field to the Values section.

15- dragging median field to the values section

  • Select Value Field Settings from the drop-down list under the Median field in the Values section.

16- selecting Value Field Settings

  • A dialog box named Value Field Settings will pop up.
  • In the Summarize Values By tab, select Average from the Summarize Values By list.
  • In the Custom Name box, rename the field name to “Median” (there is a space before Median).
  • Click OK.

17- editing value field settings dialog box

  • In the pivot table, you will now see the median of each row label.

18- calculated median in pivot table using MEDIAN function

You can use the MEDIAN function to calculate the median in the pivot table.

Note: Keep in mind that in our dataset we have no repeated values (both for Student ID and Marks). That’s why we have got the same median value for all Student IDs. If you have repeated values in your dataset, the median value will be different.

Read More: How to Create Pivot Table with Values as Text

Frequently Asked Questions

1. What is the formula to find the median from mean?

For Odd Numbers:

  • If the set has an odd number of elements, let n be the total number of elements.
  • The median is the value of the ((n+1)/2)th item in the given set.

For Even Numbers:

  • If the set has an even number of elements, let n be the total number of elements.
  • Find the two middle terms: (n/2)th and ((n/2) + 1)th terms.
  • Calculate the mean (average) of these two middle terms.
  • The median is obtained by taking the mean of the two middle terms: Median = ((n/2)th term + ((n/2) + 1)th term) / 2.

 2. What is the median formula for tables?

The median is the mean of the two middle numbers if the number of terms is even. Sort the numbers based on their size. The median is the middle term in an odd number of terms. When there are even terms, add the two middlemost terms and divide by 2.

3. What is the formula 3 median?

3 Median = 2 Mode + Mean

Hint: The relationship between mean, median, and mode is known as the empirical relationship.

Download Practice Workbook

You can download the practice workbook from here:

Get FREE Advanced Excel Exercises with Solutions!
Nujat Tasnim
Nujat Tasnim

Nujat Tasnim, BSc in Computer Science and Engineering from American International University-Bangladesh, has a year-long tenure with ExcelDemy. Initially a Content Developer at SOFTEKO, she transitioned to the Software Team as Junior Software Quality Assurance Engineer. Currently serving as a Junior SQA Engineer at SOFTEKO, she excels in SDLC, STLC, and requirement analysis and possesses profound insights into OOP-related software development processes. Nujat is also proficient in design software like Photoshop and Illustrator, contributing diverse skills to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF