Forum Discussion
Trying to add vertical average line to a pivot table chart
I've created a chart of the number of days it takes to complete a task, they range from 0 to 202 days.
I can create a pivot table for the task and the number of instances for each time it takes a certain number of days (e.g. if it took 6 days for two of the tasks, the x-value is 6, and the y-value would 2).
What I'm trying to add is a vertical line across the chart for the average number of days taken to complete tasks.
The pivot table itself is going to be part of the background for a dashboard display that references a table that tracks all current and closed tasks.
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.
