Stacked columns - showing % out of total

It's not clear to me exactly what you want. As a first guess, have you tried a 100% stacked column chart?

I must not be understanding what you are trying to do. You are correct that a simple stacked column chart will show Sourcing with a lot fewer total hours. But a 100% stacked column chart should not have shown that. It should have showed Sourcing, R&D, and Design all with the same size column (from 0 to 100%). You should then be able to see that Sourcing has 40% of their hours going to "other" where Design has 20% going to "other". Did you get something different when you tried a 100% stacked column chart? If that was not what you were looking for, can you help us understand better what you are trying to achieve?

In case the confusion is about the difference between a stacked column and a 100% stacked column:
simple Stacked column: https://exceljet.net/chart-type/stacked-column-chart
100% stacked column: https://exceljet.net/chart-type/100-...d-column-chart

Apologize for explaining badly. I think this attachement will show it a bit clearer:

I'd like to have 3 staples with different heights (2) depending on how much time each department has reported (out of total time per month).
Within these 3 staples I'd like the category to show, as in an 100% stacked column. So an stacked column within an staple column.

Basically (1) inside of (2). A normal stacked chart where the staples length are weighted for reported time / required reported total time.

I am still not sure what you are trying to do.

In your first example, you had a stacked column chart based on the absolute numbers in spreadsheet columns B:D. This chart shows the relative heights of each category very well. R&D is clearly much larger than sourcing. However, the sourcing column is so much shorter than the others, that you cannot readily see how each "stack" of the sourcing column contributes to the total.

I then suggested a 100% stacked column chart, which shows all three categories with the same size column. which you included in your latest example. This chart does a good job of showing how each "stack" contributes to the total for each category, but fails to show that each category is a vastly different absolute size (R&D is the same size as sourcing).

In your latest example, you also show a regular column chart. This chart is based on the % of month column in the spreadsheet. This chart shows that each category is a different size, but something in going from the absolute numbers to this % of month changes the sizes of the columns. R&D is still larger than sourcing, but the difference in column height is much smaller. The purist (who wants the chart to be read accurately) wonders which column chart is the most accurate -- the one like the first example that really shows how much larger R&D is than sourcing, or this new one that exaggerates the size of the sourcing column and compresses the R&D column.

Not knowing exactly what you want, I might suggest a couple of things to try next to see if they move us closer to the final desired result.
1) Is this a case where one chart will not be able to accurately show everything you want it to show? Perhaps the best approach would be to use a regular column chart based either on spreadsheet column E or G (depending on which number more accurately reflects the real data). Then have a second 100% stacked column chart to show how each stack contributes to the total for each category. I like this approach because it might be the best compromise that can accurately show both the relative heights of each column, while also allowing the reader to see how each spreadsheet column contributes to the total height.

2) If the way your latest example based on the % of month column shrinks R&D and exaggerates sourcing is still accurate, perhaps turning this into a stacked column chart with an appropriate transformation of the absolute values in spreadsheet column B:D will work. In J22, I entered the formula =B22/$E22*$G22 (note the mix of relative and absolute references), then copied that formula into J22:L24. Then built a stacked column chart based on these columns. The exaggerated size of the sourcing column in this chart makes it easier to see how each stack contributes to the total. Whether it is enough to really compare how each stack contributes to each category's total is uncertain.

Maybe one of those ideas will lead closer to what you want to show.

Perhaps this is what you mean.

The total stacks are split into 3. The data labels for those sections is then shown as a percentage of the individual stack.

You will need to use formula to calculate the 2 sets of values. You can then use data labels values from cells to show the percentage share

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Thanks mod. Will do. Should have read this forums rules more closely.

EDIT: Actually, as it turns out, I found a simple solution to my problem of inserting a hidden column that calculates the difference and using that instead of the "100% total". Not sure if that's *correct*, but it works for me and my purposes. Maybe that will help OP too.