2

I have an Excel XY chart, with the following data:

Day #1      #2      #3      #4      #5      #6      #7      #8
1   0.30%   0.71%   -0.18%  0.07%   -0.44%  0.63%   0.28%   0.32%
2   -1.24%  -0.03%  -0.52%  -0.23%  -0.59%  0.25%   0.12%   0.11%
3   0.87%   1.19%   0.16%   0.49%   0.18%   1.03%   0.96%   0.70%
4   -0.59%  0.75%   0.13%   0.70%   0.30%   1.33%   1.20%   1.06%
7   1.09%   1.33%   0.32%   1.50%   0.22%   1.38%   1.09%   0.99%
8   -0.58%  0.65%   -0.08%  0.70%   -0.12%  1.13%   0.80%   0.73%
9   1.09%   1.49%   0.51%   1.04%   0.42%   1.57%   1.25%   1.07%
10  0.32%   1.51%   0.74%   1.26%   0.70%   1.90%   1.70%   1.52%
11  -0.12%  1.36%   0.59%   3.11%   0.49%   1.58%   1.45%   1.35%
14  -0.12%  1.24%   0.56%   5.52%   0.49%   1.66%   1.56%   1.33%
15  0.55%   1.58%   0.89%   1.39%   0.79%   1.73%   1.72%   1.58%
16  -0.29%  0.75%   -0.13%  0.04%   -2.08%  -1.54%  -1.93%  -2.66%
17  -0.05%  1.41%   0.74%   8.19%   0.66%   1.66%   1.59%   1.46%
18  0.03%   1.59%   0.93%   5.16%   0.96%   1.98%   1.86%   1.67%
24  0.26%   1.90%   1.33%   1.83%   1.07%   2.35%   2.21%   2.01%
25                              
26                              
27                              
28                              
29                              
30  

What I would like to do is the following:

  • Part of the graph below -1.5%: shaded red (50% transparency)
  • Part of the graph between -1.5% and -0.075%: shaded orange (50% transparency)
  • Part of the graph between -0.075% and +0.075%: shaded green (50% transparency)
  • Part of the graph above 1.5%: shaded red (50% transparency)

I have tried various approaches suggested on what I could find on Google and the closest I have come is this (from Excel Charts With Horizontal Bands): enter image description here

Close but no cigar... Does anybody have any other suggestion?

  • @MátéJuhász Apologies, I should have said. #1, I can't get the first red band to go from -3% to -1.5%, it always starts at -1.5%, which means all subsequent bands are also wrong, and #2 I have this great big gap on the x-axis in the bands that I can't seem to be able to fill. – am304 Jun 26 '17 at 12:15
  • It's OK, I managed to solve it by myself. Will post the solution. – am304 Jun 26 '17 at 15:11
1

After some trial and error, I have managed to solve this by myself.

I followed the steps described in Excel Charts With Horizontal Bands with the following subtle, yet important, differences:

  • I changed the primary vertical axes (the one on which the stacked columns are plotted) to start from 0 and have the same span as the secondary vertical axes (the one on which the XY data is plotted). This gets rid of the problem I had with the negative values and the bands not being correct.
  • Instead of deleting the primary vertical axes (which undoes the previous scaling step), I just made it invisible by setting tick marks and labels to 'None'.
  • I changed the secondary vertical axes to have the horizontal axes cross at a specified value corresponding to the minimum value of the vertical axes (-3% in my case). This moves the horizontal axes to the bottom of the plot.
  • I set the gap width of the stacked columns to 0, which gets rid of the great big gap I had initially.
  • I deleted the primary axes titles (both horizontal & vertical), and added titles for the secondary axes (both horizontal & vertical). The titles were on the wrong side of the graph compared to the labels and tick marks, because the main XY data was moved from primary to secondary axes.
  • I deleted the legend entries for the stacked columns
  • I added the major gridlines for the secondary vertical axes.

Hopefully, this will help somebody with the same problem!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.