Overlapping bar chart (forecast vs. actuals)

[D-smoke]

Hi,
I'm trying to create a chart to display working hours for a number of resources (names). What I can't seem to figure out is how to overlap the bars so that each resource (name) has two bars - one forecast and one actuals bar. Whatever I try it's just a mess. The actuals bar should be thinner or transparent so that you can always see both bars for each name.

Attached is the raw data and a picture example of roughly what I'm trying to create.

Any help is highly appreciated
Thanks guys!Forecast - Actuals chart.xlsx

[Kyle123]

Why not cluster the bars, so have actual and forecast next to each other? It's much easier to read

[Kyle123]

What's the picture for? Is that what you want?

If it is, you need to transform your data, it's nowhere near how it needs to be arranged

[D-smoke]

The picture is just to illustrate roughly what I'm looking for. I actually don't need the lines or for it to be a pivot chart.

The reason I want it to overlap is that there are so many names (resources) so it looks really messy.
It could work with a scatter point for the actuals for instance (I couldn't get that to work though... )

[Kyle123]

You've got too much raw information. Can you summarise by Quarter for example?

Then have a graph for each resource below?

[D-smoke]

Unfortunately not. But all the blanks can of course be ignored. In reality I think there are about 12 names (resources) that needs to show.

[Kyle123]

There's too much information, you'll need 288 columns just for 2 years data and 12 categories - you can't possibly get anything meaningful out of that?

[Kyle123]

What about a summary graph and then a graph for each resource?

[D-smoke]

Forecast - Actuals chart v2.xlsx

Ok, I see your point. Attached now is what I only need to display. As you can see the set up is a bit different (a poor attempt to structure the data different so that the chart would look better - it did not work...)

[D-smoke]

How would you suggest the data to be arranged?

[Kyle123]

Well have you decided how you would like to summarise it? Or are you really wanting 288 columns?

[D-smoke]

Forecast - Actuals chart v3.xlsx

I've attached a 3rd file to show how I would like to have it. The problem is that the scatter points should be aligned with each "correct" bar (Name 1 scatter point should be over/aligned with the Name 1 bar).

Is this fixable?

[Kyle123]

I'm still not entirely clear what you're trying to do, but i think you just need to plot the points on the Primary Axis, not the Secondary - that will line them up with the bars

[D-smoke]

I tried that but then the points are not in line with each bar either.

Sorry if I am unclear. I just want to display in one chart how much (number of hours/month) each person is forecasted to work and how much the have actually worked each month.

[Kyle123]

But you have too much data to show that on a simple graph. Why not work it out as a percentage of worked by person and plot that? Or variance to forecast?

[D-smoke]

Ok, that could perhaps work! How would I do that? Can you show me an example perhaps?

[Kyle123]

Have a play with something like this - I generated some more data as I think yours was made up and made for odd graphs