Stacked chart, show sub-tier of data within a bar? Workbook and pic included

[get_nick]

Hi Excel Forum.

What I have:
Columns with 4 sets of data creating a larger total than I want.

What I want:
Columns with 4 sets of data, but 2 are a portion of a bigger set. Ie. $.75 of the $3.90 of the larger stack and not an additional $.75. Right now it's adding the two.

Analysis - Excel.png

[JeteMc]

This solution (attached) will reduce the columns to the correct height, Hyperconverged Total Market + Converged Total Market, however it will change the numbers in the data labels. To do this I changed the formulas in the ranges AH2:AM2 and AH4:AM4 to subtract the subtotals, i.e.

Please Login or Register  to view this content.

I did adjust the legend to reflect the changes in the data labels. I am pretty sure that this was not exactly what you were looking for, perhaps it will give some of the more expert chart designers here a spark of inspiration however.

Copy of Excel sample.xlsx

Hope this helps.

[get_nick]

thanks for the reply, but it changes the value of the blue column. I just want to overlap the $.75 into the $3.9.

[JeteMc]

I have played around a little with overlaying a stacked column chart that has only the only the Hyperconverged Total Market and Converged Total Market data. By eliminating the elements of the chart, except for the data labels, I think that it gives the appearance of being a single chart with the data labeling that you were seeking.

Copy of Excel sample.xlsx

Hope this helps.

[get_nick]

that looks great. Do you think the chart would look better if i just created a second stack for the TAM and placed it next to the Total Market?

I just need to figure out how to raise the secondary access to bring it closer to where the stacks meet.
2015-07-24 09_41_18-Sample File - Excel.png

[JeteMc]

I just need to figure out how to raise the secondary access to bring it closer to where the stacks meet.

You could do that by putting a column under the TAM's that matches the height of Total Market CI - TAM CI (as in AH4:AM4 in the file in post #4).
Change the fill of that column to "No Fill".

Do you think the chart would look better if i just created a second stack for the TAM and placed it next to the Total Market?

I think that the chart in the file in post #4 is easier for me to understand, however I don't know what your audience is accustomed to seeing.

that looks great.

Thank You for the feedback, please remember to mark the thread 'Solved', and if you have received helpful information from anyone consider adding to their reputation.