Plot data set as an chart in Excel, and then add a second series showing the data's multiple standard deviation bands aligned to data values

Question

I am working with a time series set of data which is the Rate of change between successive price changes in a historical section of a stock price. I have 59 observations. I have also calculated their standard deviation. I want to create an Excel chart that will plot all 59 observations, then create a secondary plot on the same chart that will show where each of the 4 or more standard deviations will divide theses 59 values.

I have tried creating a column chart with two data series, but Excel will not align the standard deviation data series to the ROC series by value. Instead it aligns them on both on the left side of chart by series count. I created an example of what I want the chart to look like in Photoshop.

How can I do this?

Photoshopped example:

Answer 1

The following approach could work for what you are trying to achieve. Given that the data was posted as an image and not a markdown table, I made an example up with Apple stock close prices.

The main issue you are facing is that you need too match the standard deviations to your rows, because the values will be aligned along the x-axis. Thus, it is not sufficient to indicate only the standard deviations. Let's assume I have a sorted ROC series numbered from 1 to 40 and the computed standard deviations. I also added 0 for -1/1 because otherwise it will not be correctly matched, if you try to do in an automated way.

The data is stored in A1:B41 and the standard deviations are computed in range J1:K11. Then we compute in column C the value that is closest to the standard deviations but below a given standard deviation. In column C3 I used the following formula for this: =LOOKUP(B2,$K$1:$K$11,$J$1:$J$11). Based on this, we add additional columns that get a value in case they match a given standard deviation, i.e., if the standard deviation range indicates 1 then we fill only the relevant cells for the ones in this column. The same procedure applies to the others. In this case I mapped these manually and simply put the value -0.02, which indicates where we will have the line then. But this can be put to any value in principle.

The final data preparation looks as follows:

Now, highlight id and ROC columns and add the column chart (same as you already having). Once you have this, select range D1:G41, copy it and then select the chart and insert the relevant series. To specify the chart design, select the chart > Chart Design > Change Chart Type > Combo. Select here for ROC Clustered Column and for all sd series select Line. There is no need to put it on the secondary axis. The resulting chart looks as follows:

The resulting chart is basically of the same type as you created with photoshop. Another approach could be to just highlight the ends of the given standard deviation ranges and then add lines with markers. Based on this you could include error bands to indicate where a given area starts and ends.

For completeness, here the data as a markdown:

id	ROC
1	-0.02
2	-0.01
3	-0.01
4	-0.01
5	-0.01
6	-0.01
7	-0.01
8	0.00
9	0.00
10	0.00
11	0.00
12	0.00
13	0.00
14	0.00
15	0.00
16	0.00
17	0.00
18	0.00
19	0.00
20	0.00
21	0.00
22	0.00
23	0.00
24	0.00
25	0.00
26	0.00
27	0.00
28	0.00
29	0.00
30	0.00
31	0.00
32	0.00
33	0.00
34	0.01
35	0.01
36	0.01
37	0.01
38	0.01
39	0.01
40	0.02