I'm trying to create a "nested" 2D stacked bar chart in excel but I only got until stacking the columns. Unable to show "work %" inside "Used %". Is there a way to accomplish this in excel? I seen a similar example but that was in BI and not in excel.
Update: Please note that "Work" is a subset of "Used". Total =100 & Used = 50, then Used % = 50% Used = 50 & Work = 50 , then Work % = 50% (of Used). Hope this clarifies.
Name Type Total Used Work Used % Work %
Mike Sport1 100 50 25 50 50.00
Mike Sport2 175 75 50 42.86 66.67
Mike Sport3 50 40 10 80 25.00
Mike Sport4 200 110 40 55 36.36
Rita Sport1 75 25 10 33.33 40.00
Rita Sport2 150 100 80 66.67 80.00
Rita Sport3 100 75 35 75 46.67
Rita Sport4 125 100 80 80 80.00
I'm suggesting an approach further down, but your question needs a lot of clarification. The clarification would normally go into a series of me writing comments and you responding by editing your question and providing more details.
I don't feel like going through this rigmarole right now, so, please read my questions for clarification, try to understand what I'm after and then go and edit your question to provide the required detail.
My questions to you:
What is the information you want to derive from the data? What question are you posing to the data that the chart should answer?
Your data table does not make sense. The "total" column totals --what exacty--? How come "used" is 50 and "work" is 25 but Used% is 50 and Work% is also 50?
Please edit your question and either amend the data or explain how it all hangs together.
What data do you want to chart? The scribbles on your scan are less than conclusive.
My suggestion:
These questions aside, you can create a stacked column chart with the data you want to display. You may need to create a helper table as the data source for the chart, just so everything is in the correct place for easy selection and rendering.
Whether or not something is a percentage or part of a bigger unit can be shown by formatting the colors of the data points, and with data labels.
You can add data labels to any series and then edit the labels to receive their values from spreadsheet cells. The whole thing can be automated with VBA.
If you don't want to write that kind of VBA yourself (which is understandable) you can download the free XY Chart Labeler tool by Rob Bovey here. It is ill-named and can actually add labels to any chart type.
I'm aware that this is not a complete answer. I expect you to edit your question with the missing information, so I can subsequently edit my answer to supply a real answer.