Table of Contents
To add a median line to an Excel chart, follow these steps:
1. Select the chart to which you want to add the median line.
2. Click on the “Chart Elements” button (represented by a plus sign) located on the top right corner of the chart.
3. Check the box next to “Trendline” to add a trendline to the chart.
4. Click on the newly added trendline to select it.
5. Right-click on the trendline and select “Format Trendline” from the drop-down menu.
6. In the Format Trendline window, go to the “Line Options” tab.
7. Under “Trendline Options,” choose “Median” from the “Type” drop-down menu.
8. Click “Close” to apply the changes.
The median line will now be added to your Excel chart, representing the median value of the data in the chart.
Excel: Add Median Line to Chart
Occasionally you may want to add a line to a bar chart in Excel to represent the median value of the bars.
This tutorial provides a step-by-step example of how to create the following bar chart with a median line in Excel:
Let’s jump in!
Step 1: Enter the Data
First, let’s create the following dataset that shows the total sales of some item during each month in a year:
Step 2: Calculate the Median Value
Next, we’ll use the following formula to calculate the median sales per month:
=MEDIAN($B$2:$B$13)
We can type this formula into cell C2 and then copy and paste it to every remaining cell in column C:
Step 3: Create Bar Chart with Median Line
Next, highlight the cell range A1:C13, then click the Insert tab along the top ribbon, then click Clustered Column within the Charts group:
The following chart will be created:
In the new window that appears, click Combo and then click OK:
The chart will be converted into a bar chart with a median line:
The blue bars represent the sales each month and the orange line represents the median sales across all 12 months.
Step 4: Customize the Chart (Optional)
Feel free to add a title, customize the colors, customize the line style, and adjust the width of the bars and the median line to make the plot more aesthetically pleasing:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: