Excel: How to 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:

Next, right click anywhere on the chart and then click Change Chart Type:

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:

How to Fit a Curve in Excel
How to Make a Frequency Polygon in Excel
How to Add a Horizontal Line to Scatterplot in Excel

One Reply to “Excel: How to Add Median Line to Chart”

  1. I found this very useful to demonstrate where the midpoint is when analysing survey results for work- thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *