In this article, we will show how to calculate the median in a pivot table in Excel. First, we will show how to activate the power pivot in Excel. Then we will demonstrate the two methods in detail.

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. Here, in this article, we will show the way to calculate the median in a pivot table.

Hope you find this article informative and useful. So, let’s get into the main discussion.

**Table of Contents**hide

## How to Activate Power Pivot in Excel

In order to use Power Pivot, you must activate it once. Here are the steps to follow:

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

- Now, check the box before
**Microsoft Power Pivot for Excel**and click**OK**.

Thus you can activate the power pivot in Excel.

## Calculate Median in Excel Pivot Table: 2 Simple Ways

In this part of the article, we will show you 2 simple ways of calculating the median in Excel Pivot Table. For that, we have taken a concise dataset consisting of Student Names, Student IDs, and Marks.

### 1. Calculate Median in Pivot Table by Creating a New Measure Function

First, we will create a pivot table from the dataset. Follow the below steps accordingly.

#### Step-01: Creating Pivot Table from Dataset

- Select the whole dataset.
- Then go to
**Insert**tab>> select**Pivot Table**under**Tables**option.

- A dialog box will pop up.
- Select the
*Location*where you want to see your pivot table. - Then check the box beside
**Add this data to the Data Model**option. - Finally, click
**OK**.

#### Step-02: Creating a New Measure Function

- As a result, you will see that here’s a different icon and we have the ability to create a measure.

- Now, right-click on the icon and click on
**Add Measure**option.

- A dialog box called
**Measure**will be popped up. - Add a
**Measure Name**. We have given Median Marks. - In the
**Formula**box, enter the following formula.

`=MEDIAN([Marks])`

- Select the
**Category**as*Number*and set**Decimal Places**as*2*. - Finally, click
**OK**.

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

#### Step-3: Calculating Median

Now as we have created a new measure function called *Median Marks*, we can easily calculate the median. Let’s do this:

- Now let’s put
*Student ID*in**Rows**and*Median Marks*in**Values**.

- And thus you will get your pivot table along with your calculated median.

### 2. Calculate Median in Pivot Table Using MEDIAN Function

You can calculate the median using **the MEDIAN** **function**. Suppose we have already created a pivot table. Now follow the below steps:

- Insert a new column named
**Median**beside the*Marks*column. - Enter the following formula in cell
**E5**.

`=MEDIAN(IF($C$5:$C$14=C5,$D$5:$D$14))`

- Next,
**AutoFill**the rest of the column’s cells, and you will get the median the same as the previously shown method.

- Now, select any cell in the pivot table.
- Click
**PivotTable Analyze**>> then**Change Data Source**drop down>> select**Change Data Source**.

- A dialog box named
**Change PivotTable Data Source**will appear. - Now specify the new source data in the
**Table/Range**box - Then click
**OK**.

- In the
**PivotTable Fields**pane, the**Median**field is now available. - Now, drag the
**Median**field to the**Values**section.

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

- 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). - Finally, click
**OK**.

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

Thus you can use the **MEDIAN** function to calculate the median in the pivot table.

*Keep in mind that in our dataset we have no repeated values (both for*

**Note:***Student ID*and

*Marks*). That’s why we have got the same median value for all

*Student ID*s. 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.

## Key Takeaways from the Article

- In this article, we have discussed 2 methods of calculating the median in an Excel pivot table.
- First, we have shown how to activate the power pivot in Excel.
- Showed 2 simple ways of calculating the median in an Excel pivot table.
- Showed a step-by-step procedure of all methods.
- Provide solutions to frequently asked questions by readers.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

In this article, we tried to provide a concise and thorough overview of how to calculate the median in an Excel pivot table. Don’t forget to download the practice file. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. To learn more, please visit ExcelDemy, a one-stop Excel solution provider.