Trying to add vertical average line to a pivot table chart

aquilam 

Adding a vertical line for the average number of days in a pivot table chart can be achieved using Excel's chart formatting options. Here's how you can do it:

1. Create the Pivot Table and Chart:

• Ensure your data table includes columns for tasks and the number of days taken for each task.
• Create a pivot table summarizing the data, with "Days Taken" in the Values area and any relevant categories (e.g., task types) in the Rows or Columns area.
• Right-click anywhere in the pivot table and choose "Insert Chart." Select a suitable chart type (e.g., Column or Bar chart).

2. Add the Average Line:

• Click anywhere on the chart to activate it.
• Go to the "Chart Design" tab in the ribbon.
• In the "Chart Layouts" group, click the "Trendline" dropdown menu.
• Select "More Trendline Options..." from the bottom of the menu.
• In the "Format Trendline" pane, choose "Linear" as the trendline type.
• Under "Set Intercept," check the box next to "Specify value" and enter "0" in the box. This ensures the line intersects the y-axis at 0.
• Click "Close" to apply the trendline.

3. Format the Average Line:

• Right-click the trendline and choose "Format Trendline."
• In the "Format Trendline" pane, adjust the line style, color, and thickness as desired.
• You can also add data labels to the trendline by selecting the "Data Labels" tab and customizing their appearance.

Additional Tips:

• You can customize the chart title, axis labels, and legend to enhance clarity.
• If you want to display the average value numerically, add a text box to the chart and use the formula =GETPIVOTDATA("Days Taken", PivotTable1, "[All]") (replace "PivotTable1" with your actual pivot table name).

By following these steps, you'll have a pivot table chart with a vertical line representing the average number of days taken to complete tasks, providing valuable insights into your data.