Add a Vertical Average Line to an Excel Pivot Chart
Question details
Add a vertical reference line that marks the average task duration on a chart built from PivotTable or task-duration distribution data.
- Product
- Microsoft Excel PivotChart
- Device & OS
- not provided
- Scenario
- The user wants to visually compare task-duration values against the calculated average on a chart.
- Observed behavior
- A standard trendline does not create a fixed vertical line at the average duration value.
Calculate the average task duration in a worksheet cell and identify the chart's minimum and maximum Y-axis values. If the PivotChart does not allow an additional series, create a regular chart from the PivotTable output and helper data.
Add an XY Scatter helper series for the average line
Use this method to draw a true vertical line at the average value rather than a statistical trendline.
A vertical reference line needs two points with the same X value. Set both X values to the average duration, then use the chart's lowest and highest Y values to define the line height.
In an empty worksheet cell, enter an average formula for the task-duration values, such as =AVERAGE(B2:B100), and keep this cell as the average X value.
In a small helper range, create two rows. Enter the average cell reference in both X-value cells, then enter the chart minimum Y value in the first Y-value cell and the chart maximum Y value in the second Y-value cell.
Select the chart, then go to Chart Design > Select Data > Add. Set the Series X values to the two average cells and the Series Y values to the minimum and maximum Y cells.
Go to Chart Design > Change Chart Type, select the helper series, and choose XY (Scatter) with Straight Lines. Use the secondary axis only if needed to align the scatter series with the existing chart axes.
Right-click the helper series and choose Format Data Series. Set Marker to None, then choose a visible line color, width, and dash style. Add a series name such as Average Duration if you want it shown in the legend.
Create a standard chart when the PivotChart cannot accept the helper series
Use this option when Excel restricts editing or adding a non-PivotTable series to the PivotChart.
Create an Average Reference Line in WPS Spreadsheets
WPS Spreadsheets can create a vertical average marker by adding a two-point helper series to your chart. Calculate the average, use it as both X values, and format the XY Scatter series as a line without markers.
- 1. Calculate the average: In WPS Spreadsheets, enter =AVERAGE(range) in an empty cell for the task-duration average.
- 2. Set up two line points: Create two helper rows with the average in both X cells and the chart's minimum and maximum values in the Y cells.
- 3. Add a chart series: Click the chart, open Chart Tools, and choose Select Data > Add to select the helper X and Y ranges.
- 4. Choose a scatter line style: Open Change Chart Type for the helper series and select XY Scatter with Straight Lines.
- 5. Remove markers: Select the helper series, open Format Data Series, and set the marker option to None while applying the desired line style.
Frequently Asked Questions
Can I add a vertical average line directly to an Excel PivotChart?
It depends on the PivotChart type and Excel version. If Select Data does not allow you to add the helper series, create a regular chart based on the PivotTable output and add the XY Scatter line there.
What values do I need for a vertical average line?
Use two points: both X values must equal the calculated average, while the Y values should be the minimum and maximum values displayed on the chart's Y-axis.
Why does an Excel trendline not show the average as a vertical line?
A trendline is calculated from the relationship between data points and slopes across the chart. It cannot act as a fixed vertical reference at one X value.
How do I remove dots from the average line series?
Right-click the helper series, select Format Data Series, open Marker options, and choose None. Keep the line enabled under Fill & Line.
Why is my scatter average line in the wrong position?
Check that both helper X cells reference the average value and that the scatter series uses the correct axis scale. If it uses a secondary axis, set its minimum and maximum bounds to match the relevant primary axis.
