How to Add an Average Line in Excel Chart

Adding an average line to your Excel chart is a simple way to give your data instant context. It transforms a standard chart cluttered with numbers into a clear story, showing you which data points are above or below the norm at a glance. We'll look at a few easy methods for adding and customizing an average line to your Excel reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Add an Average Line in Excel?

An average line (or a trendline) acts as a visual benchmark. Instead of just looking at raw numbers, you can immediately see how each data point performs in relation to the overall average. This small addition makes your charts much more insightful. For example, if you review your numbers monthly, it highlights high-performing and low-performing months, giving you a better idea of how to pivot your marketing strategy based on what you see.

Here’s why it’s so effective:

• Performance at a glance: Instantly identify which periods or categories are outperforming or underperforming the average. Looking at a sales chart? You can immediately see which months beat the monthly average.
• Outlier spotting: Abnormally high or low points - outliers - jump out when contrasted with the average line.
• Clear communication: Comparing data to a simple average is one of the easiest ways of making data easy for anyone to digest and understand.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Getting Started: Prepare Your Data by Calculating the Average

Before you add an average line to your chart in Excel, you’ll first need to calculate the average. We also recommend that you include an “Average” column next to your source data to make it easier to add an average line to your chart. For this, we'll use the AVERAGE formula.

Let’s say you have a table with monthly sales data in Excel:

In our example source data, the months are in column A and the sales figures are in column B.

1. Pick a cell for the formula: Select a cell where it makes sense to calculate the average (for this example, C2 is perfect).
2. Enter the formula: Type =AVERAGE(B2:B13) into a cell. To make the calculation consistent across cells, make your cell references absolute by adding dollar signs ($), like this: =AVERAGE($B$2:$B$13).
3. Drag or autofill the formula: Click on the small square (the fill handle) at the bottom right corner of cell C2 and drag it down to C13. Each cell in the “Average” column will now show the same value: the overall sales average.

Your Excel table should now look something like this:

How to Add an Average Line to Your Chart in Two Easy Steps

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method One: Adding a Trendline Through Chart Elements

For some charts - like bar, column, and line charts - Excel includes a built-in feature for displaying the average. This is the fastest way to showcase your average without needing to calculate it beforehand. Adding this to your chart is as easy as two clicks once you’ve created your source table.

How To: A Simple Step-by-Step for Your Excel Chart

1. Create your chart: Highlight your source data (for example, columns with Months and Sales data from A1 to B13). Insert your preferred chart type, such as a Column chart.
2. Once you have your basic chart ready, click inside your chart to access the Chart elements. Click on the 'Chart Elements' icon (a green plus icon) on the right-hand side of your chart. Check the box in front of ‘Trendline’ to include an average line. Hover over Trendline, click the arrow that appears next to it, and select More Options… to customize your chart further.
3. In the Format Trendline menu, you can further customize the visual style. Select a trendline type such as 'Average' to show the overall average across all data points. This feature calculates the average sales within your source data without needing to create a helper column beforehand. You've now successfully added an average line to your Excel chart!

This method is intuitive and fast but comes with limitations: it only works with certain chart types like line, bar, or column charts. Creative chart options can help you present data more effectively in reports, like changing your average line to a different style. Here’s an alternative method for creating an average line on an Excel chart for your dashboard visuals.

Method Two: Adding a New Data Series

This second method may involve more clicks but offers greater control over your charts. Let’s practice using the source spreadsheet for your monthly average calculations, adding an additional 'Average' column next to your source sales data.

1. Select Your Chart

1. Go back and select the chart you created before, or start with a new spreadsheet workbook. Either way, create a chart using the same steps and source data. Remember, your source data should include an additional 'Average' column.
2. Select Data Source: Inside your chart, right-click and choose 'Select Data'. In the Select Data Source menu box, locate and click 'Add'. Fill in your values: for 'Series name', input your 'Helper column header', for 'Series values', input your average sales data range.
3. Right-click on the chart's series, choose 'Change Series Chart Type', and assign the new series to display as a line chart.

This gives you more control and flexibility, allowing for various visuals in your chart.

Adding Style: Customizing for Better Visual Experience

Your data visualizations do not need to be standard. Make them engaging by adjusting the style of your average line. Right-click on your chart's line, access the format menu, and adjust colors and line styles as needed to make your chart dynamic.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Create a Dynamic Average from an Excel Table

If you need to update your work or report regularly, creating a table in Excel that automatically updates can save time. Highlight your source worksheet, find 'Insert Table', and create a table that updates as you add new data.

Final Thoughts

Adding an average line in a chart on Excel is a feature that anyone can use. Whether you opt for the built-in chart elements or make the visualization dynamic, it is simple enough to incorporate into your work. For more insights, consider using Graphed for your chart visualizations. We help teams gain better insights and improve ROI. For any questions, reach out to us at Graphed.