Excel Pivot Chart vertical dynamic line

[kitpuos]

Hello,

This is my first post in this forum. I am using MS365 latest on Windows 10.

I have created a Pivot Table and Pivot chart from a sample data set.

All the data in the sheet is dynamic except Strike and date column.

Currently I am struggling to add spot value (which is dynamic as well and changes with time) as a vertical line on the Pivot chart. 

Please find the sample data below:

sample_data 

Any help to resolve this will be greatly appreciated. Stuck on it for weeks.

[#2]

Hi,

Not sure I understand. If it's to be a vertical line, at which of the Strike entries in the horizontal axis should it be placed?

Also, where does this Spot value come from? Is it calculated from the raw data?

Regards

[#3]

@kitpuos 

To add a vertical line to a pivot chart in Excel that represents a dynamic value (that's how I understood it, from the text), you can use a combination chart.

Here’s how:

1. First, make sure your pivot table includes the dynamic value you want to plot as a vertical line.
2. Next, create a pivot chart from your pivot table.
3. In the pivot chart, right-click on the data series representing the dynamic value and select “Change Series Chart Type”.
4. In the “Change Chart Type” dialog box, select “Combo” from the list of chart types on the left.
5. For the dynamic value data series, select “Scatter with Straight Lines” as the chart type and check the “Secondary Axis” box.
6. Click “OK” to apply the changes.

Now your pivot chart should include a vertical line representing the dynamic value.

As the value changes in your pivot table, the line will update automatically in the chart.

I hope this helps!

Here is a link with information on how you can get a quick and precise solution proposal.

Welcome to your Excel discussion space!

NikolinoDE

I know I don't know anything (Socrates)

[#4]

@JosWoolley Thanks for your response.

The horizontal axis will be the strikes against which vertical line will be drawn.

The Spot price(PT_PC!H4)  will be dynamic as well. I have updated sheet with the to-be chart as well so that it explains what i want to achieve. 

[#5]

Thanks @NikolinoDE for your response. I had tried that as well with these steps earlier.
The problem here is when you switch to Pivot combo chart, It doesn't have the “Scatter with Straight Lines” options at all. That's why I took shelter of this forum.

[#6]

@kitpuos440 

If I understood correctly...

You have followed the instructions to create a combination chart and change the chart type of the dynamic value data series to “Scatter with Straight Lines” but you are unable to find this option in the “Change Chart Type” dialog box.

It seems that you are unable to find the “Scatter with Straight Lines” option when trying to change the chart type of the dynamic value data series in a pivot chart. I also don't know if this is possible as desired, there are other specialists in this forum who know a lot better.

The only thing I can offer here…

One possible workaround is to insert an XY Scatter chart in a blank cell a few rows or columns away from the pivot table.

Right-click on the chart and choose “Edit Data”.

Add a new series and select the ranges with the series name, X values, and Y values.

This chart should update automatically if the pivot table data is refreshed.