I am trying to figure out a way to create this graph automatically on Excel and link it to PowerPoint so that when the data in Excel gets refreshed, the PowerPoint chart gets updated as well. Currently, the underlying chart in this photo is a 100% stacked column chart with gap width set to 0 so that there's no space between the bars for different years. However, I have had to manually place the rectangles on top of the stacked column chart and adjust the height and width so that it's spatially accurate (for example, 16% is 16% of the area of the rectangle below). As you can imagine, it's a very long and frustrating process to update the rectangles manually when the underlying data gets updated. Is there a way to automate the creation of such a chart in excel?
I was thinking of creating a Marimekko chart in Excel (which requires manipulations for the data to be in the format desired but can be done) for, let's say, FY18 and rotating it by 90 degrees clockwise. But I'm not sure how to rotate the chart without saving it as an image first. Also, by this method, I'll have to create Marimekko charts for each of the financial years (18, 19, 20 and 21) individually and then place them side by side which might reduce some labor as compared to the method I am currently using but will not be immensely helpful.
Any thoughts on a solution to this problem will be much appreciated. I have been wrapping my head around this for a while now but haven't been able to make much headway.