Having 'Actual' or 'Forecast' series side by side with 'Budget' with similar height size

[dluhut]

Hello Excel Experts,

I am trying to create a chart where depending on the 'time', the chart will show whether it's 'Actual' or 'Forecast' and it's side by side with Budget.

I was able to create it, but because I am putting the 'whole year' together, the quarters become small/low compared to the whole year since it is chart to scale, as seen in the picture below.

Clustered and Stacked Almost Success.png

However, what I'm trying to accomplish is as shown in the picture below that I've drawn manually (not to scale).

Desired Clustered and Stacked Chart.png

With my approached (shown in the 1st attached image), I used 'Actual' and 'Forecast' as Stacked Column and 'spacer 1', 'spacer 2' and 'budget' as secondary axis with Clustered Column. But with this approached, I won't be able to 'scale up' my quarters bar charts.

So am wondering if what I'm trying to accomplished is doable.

[TMS]

You need to add a secondary axis.

See this link: https://support.office.com/en-gb/art...8-e1a2e14dd5a9

[dluhut]

You need to add a secondary axis.

See this link: https://support.office.com/en-gb/art...8-e1a2e14dd5a9

Yes I did add a secondary axis, that's why the 1st picture/image shows. And that's the closest result that I want. The problem is that if I were to add multiple years and quarters, it won't give to what I want that's shown as the 2nd picture/image.

From what I want to do, it seems that I need a 2nd (or even 3rd) secondary axis'

[TMS]

Did you change the default scale to reduce the height?

[dluhut]

Did you change the default scale to reduce the height?

Nope...it's automatic. Even if I were to change the scale, it still won't do what I am trying to accomplish which are:

1. for each quarter or Fiscal Year (FY), I'd like to have either 'Actual' or 'Forecast' shown.
2. For each quarter or FY of either 'Actual' or 'Forecast' to be side by side Budget.
3. Quarter charts to be on it's on scale rather than the scale of the 'Whole Year' (or Fiscal Year)

[TMS]

OK, can't help you.

[Richard Buttrey]

Yes I did add a secondary axis, that's why the 1st picture/image shows. And that's the closest result that I want. The problem is that if I were to add multiple years and quarters, it won't give to what I want that's shown as the 2nd picture/image.

From what I want to do, it seems that I need a 2nd (or even 3rd) secondary axis'

Maybe I'm missing some essential but you appear to be plotting two basic orders of magnitude. Quarterly series and annual series. Hence I can't see why you need more than two y axes. You can plot as many series as you want on each axis. As you add series for additional years you'll use the axis for the 'years' groups of data and the other axis for the quarterly series.

[dluhut]

Maybe I'm missing some essential but you appear to be plotting two basic orders of magnitude. Quarterly series and annual series. Hence I can't see why you need more than two y axes. You can plot as many series as you want on each axis. As you add series for additional years you'll use the axis for the 'years' groups of data and the other axis for the quarterly series.

Hi Richard,

The reason why I think I may need more than 1 secondary axis is because, my 1st secondary axis is currently being used with my 'spacer 1', 'spacer 2' and 'budget'. This allows me to have my 'actual' or 'forecast', which is on the main axis to be side by side. But, given this method, what happens is that, because the chart is drawn to scale, thus for the whole year, that particular bar charts will be long, whereas the quarters are short bars, which is shown in my opening post of the 1st image.

[Richard Buttrey]

Hi,

Would you upload your workbook so that we have something to play around with.

[dluhut]

Hi,

Would you upload your workbook so that we have something to play around with.

Hi Richard,

I did load a sample. Is the sample not what you're looking for?

[Richard Buttrey]

I was expecting to see the workbook with the first chart that is linked to your data.

[dluhut]

I was expecting to see the workbook with the first chart that is linked to your data.

Ohh...my bad, I've attached the sample workbook where it has the chart of the 1st image. Note that it's not the same as I re-create it. But the idea and the way to do it is the same.