Stacked bar chart over time (on a 2nd axis?)

SHW2022

New Member
Joined
Mar 3, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have created a line chart in Excel that shows me a company's share price over a 10 year period. I then have two further lines that show me a "Target" price and a "Maximum" price.
What I would like to do is create a horizontal stacked bar chart (ideally overlaid on my original chart so I have just one chart but if not possible then happy to have two) that corresponds to whether or not I am below target, between target and max, or above max.
So for example for the first three months my data is below target so I want the first "stack" of my chart to be red, when I cross over target and am now between target and max I want my stack to be amber. I've attached a really crude drawing to this post.

What I have done so far is to create an IF statement to show me if I'm above/between/below and return a 1/2/3 accordingly (could be any value). I think then to create my "stack" sizes I need to count this array until it changes to a different number and then count from there until it changes again but I'm just not sure how to do this and/or if there is an easier way. I tried doing it manually but I have over 4,000 data points so was very long-winded!

Really quick example array:

1
1
1
1
2
3
3
2
1
1

So my stack sizes would be: 4, 1, 2, 1, 2 - how do I get these numbers without counting manually?

I hope that makes sense but happy to clarify anything!
 

Attachments

  • 20220421_143752.jpg
    20220421_143752.jpg
    70.9 KB · Views: 5

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Max1616

Active Member
Joined
Nov 25, 2015
Messages
496
Office Version
  1. 2016
Platform
  1. Windows
Are you looking for something like this?
1650564616117.png


If so, you'll need a data set set up like this:
1650564638708.png


Your selected data should look like this (Should just select the corresponding column for each series):
1650564675077.png


Then click the graph and go to the design tab on the ribbon, and click "Change Chart Type"
On the next screen, go to the bottom section and choose "Clustered Column" for the Over/Between/Under series. And check the "Secondary Axis" box:
1650564842288.png

Exit this screen.

Next you need to right click one of the bars on your graph, and click "Format Data Series". And then slide the series overlap to 100% and the Gap Width to 0%:
1650564943957.png


Finally, you will update the color of each series to the color you'd like it to show (Under = Red, Between = Amber, Over = Green).
1650565149839.png

I hope this helps!
 
Solution

Max1616

Active Member
Joined
Nov 25, 2015
Messages
496
Office Version
  1. 2016
Platform
  1. Windows
I forgot to mention! You'll also need to change the secondary Axis maximum to 10 (The smaller this number, the larger your Over/Under bars will be).
1650565319959.png
 

SHW2022

New Member
Joined
Mar 3, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
This looks absolutely perfect!! Thank you so much. I'm not back at my computer now until Monday so will let you know how I get on then.
 

SHW2022

New Member
Joined
Mar 3, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I just ran this now and it was exactly what I was looking for, thank you so much!!
 

Forum statistics

Threads
1,171,879
Messages
5,878,026
Members
433,311
Latest member
novalion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top