Add line for today's date to Pivot Chart

**jamitucker** · 10-03-2011, 03:32 PM

I created a pivot chart showing an employee's % of time spent on the vertical axis and month on the horizontal axis. The data is broken down by "type" of activity, such as run, change, etc.

I want to add a vertical line to the chart that indicates today's date. I am trying to show actual time spent (which will show to the left of the line) versus forecasted time (to the right of today's date). Any ideas? I've attached the file. Many thanks!

**Jon_Peltier** · 10-12-2011, 11:37 AM

The usual response is to add a two-point XY series to the chart, with the same X value and high and low Y values, formatted as a line without markers.

But you can't plot any data in a pivot chart that's not in the pivot table (and you can't omit any data from the pivot table, though you can format it to hide it). You could make a regular chart from a pivot table, and add the vertical line series to the new chart.

**jamitucker** · 10-17-2011, 05:02 PM

Thank you Jon! I ended up creating a bar chart with the "today" line being a vertical line between 0 and the max of the pivot table data. I then set the horizontal axis to be the same date scale as the pivot chart by converting Jan 1 to a number and Dec 31 to a number, and making these the minimum and maximum.. Then I made the pivot chart have 'no fill' in the plot area. I also removed all the lines from the "today" bar chart (other than the "today" line). Then I formatted the pivot chart to be on top of the bar chart, so I will only have to change the bar chart annually to re-calibrate the maximum and minimum.

Thanks for the tip on not being able to plot any additional data outside of the pivot table onto to the pivot chart. My workaround using your advice worked!

**grossi** · 11-18-2012, 07:51 AM

Originally Posted by jamitucker

I created a pivot chart showing an employee's % of time spent on the vertical axis and month on the horizontal axis. The data is broken down by "type" of activity, such as run, change, etc.

I want to add a vertical line to the chart that indicates today's date. I am trying to show actual time spent (which will show to the left of the line) versus forecasted time (to the right of today's date). Any ideas? I've attached the file. Many thanks!

Dear collegue,

I have the same problem: create vertical line into a pivot chart that change by date the value. Have you resolved this problem?
Can you sendme a template?

Very very thanks

Giovanni

**protonLeah** · 11-18-2012, 05:18 PM

@grossi
Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.