Take a look at:

enter image description here

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?

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.