Plotting data in excel grouped by type

Question

Take a look at:

I made this manually. Basically, I plotted a normal bar graph, where all bars were blue color.

I want to get all bars of same type to be represented with unique color (Type 1 - blue, Type 2 - orange, Type 3 - green).

I want for every bar to have displayed item label on x-axis.

Also, legend on the side should map adequate color to type of item.

How do I do this using Excel?

Answer 1

You can (almost!) achieve this using a Pivot chart.

Before you can use that, you need to change two things about your table:

• Duplicate the data in your first column (Type of item) so that is is present for each an every row.
• Add an extra column (I called it "Order") with =ROW([@Type]) as formula. We need this to preserve the original order of the Type and Item fields.

Next, follow these steps:

• Insert a Pivot chart (ribbon "Insert", drop down PivotTable, select "PivotChart")
• Select your table as input
• In the Pivot Table layout, drag "Type" to "Legend Fields", "Data" to "Values" and both "Order" and "Item" to "Axis Fields".
• Change the chart type to a stacked bar chart.
• In "Format data series" you can decrease "Gap width" as needed to widen the bars.

The result should look like this:

It is close to what you want (I hope it's close enough), but it has one drawbacks: an extra row of data is shown ("Order"), which is needed to keep the data in order. You can cut it away when pasting as a picture in Powerpoint etc. or you could overlay a white rectangle over it to hide the numbers.