Combine multiple series into one stacked bar graph

I'm not sure I understand the question. What you show in rows 24 and 25 is a suitable arrangement for the chart labeled "desired result".

As a guess, where the values in row 25 appear to be hand entered, perhaps your question is more about looking for an automated way or tool to go from A2:E6 to something equivalent to A24:M25. If that is the case, I would look into the "unpivot" command https://support.microsoft.com/en-us/...5-f588221c7098 in Power Query/Get and Transform. While it will probably give you vertical columns (rather than horizontal rows), that should be just as good at creating the "desired result" chart.

Am I guessing right?

While tedious, the only way I know to do this it to format each data series as desired (tedious because it must be done one series at a time).

My guess, though, is that the question behind the question is how to automate the tedious process. If you don't want to do it manually, you can write a VBA macro that will loop through the data series and format according to the "series name" value, but I usually see that as unnecessary. If you build the chart correctly the first time, then each time you enter/paste/write new data to the source data, the formatting should stay the same, so once you've done it (for the largest data set you ever expect to encounter), you should not have to do it again.

If my guess is correct, then it is mostly a question of how you would best like to approach this -- do you want to build a solid template that you can reuse over and over with different data, or do you want to recreate the chart with each data set and use VBA (or other macro language) to handle the tedious parts?

Originally Posted by erice

I would like to have all the idle time shown as blue, all working time as orange, and blocked time as gray.

Like this:

Capture.JPG

Please Login or Register  to view this content.