How to create a stacked column chart where each category has unrelated subcategories

Question

I have the following set of data

I want to create a column chart with 3 stacked columns(one for each category). The sum of amounts for each category equals 100. The issue I'm having is that the subcategories can be different for each category.

Please help me how to create the required chart.

Answer 1

Try a different layout.

Instead of setting your data up this way:

It looks like you might want to set up the data this way:

• Your spreadsheet App (Excel, Google Sheets, etc.) will not complain if there is 'missing data' from some of the columns.

• Note that you can graph this data by Rows or by Columns. If the first graph doesn't look right, try the opposite way.

• When you have 'missing data' like this, a bar graph (column chart) is usually better than a line graph (line chart).