In the attached there's a load of trial and error nonsense on sheets
Sheet1 to
Sheet5. I've left them there only in case they might be of interest.
I eventually succeeded in getting close to what you want in the sheet called
Look.
On this sheet, the topmost chart is the result of simply selecting the whole of the green table and inserting a 2D column chart.
The lower chart is the same thing tweaked a bit.
Attachment 20165
You could rearrange the data as in the top left green table in the
Look sheet manually, but it's hard work, so in fact this table is the result of a transformation by
Power Query of your data on
Sheet1 cells A1:C10, which I've made into a proper Excel Table called
Table1 and this is what
Power Query starts with. You can see the bottom right extent of the table as a little symbol in the bottom right corner of cell C10. You can drag this symbol to change the size of the table, although you'll probably not need to.
So as long as all your data is within that table,
Power Query will transform it to the green table at the top left of the
Look sheet. You'll need to
refresh the query if you adjust the data on
Sheet1 which can be done by right-clicking the green table on the
Look sheet and choosing
Refresh.
The source table on
Sheet1 should retain its headers just as they are - the query uses them.
The final sheet,
Sheet6, is the result of following (most of) the instructions in
Pecoflyer's link. This is very similar but I can't easily change the colours as you'd like them, nor can I get the correct legend easily.