How to make a dynamic and colorful stacked bar chart based on pivot?

Question

I have a very simple table that starts from A1 :

  ID  CLASS  PRIORITY CATEGORY
 id1 class1      High        A
 id2 class1       Low        A
 id3 class2      High        A
 id4 class3    Medium        B
 id5 class2       Low        B
 id6 class1       Low        A
 id7 class1    Medium        C
 id8 class3    Medium        C
 id9 class3    Medium        C
id10 class1       Low        C

I need to make a pivot table (I was able to do that) + stacked vertical bars (I can't figure out how to it). There is only three PRIORITY, so we have only three colors. The labels should be centered horizontally and vertically in each stack. Also, there is a filter on the CATEGORY. It means that if we filter by a category, the chart should be updated automatically.

I made it manually (see the right part of my image below) :

Do you guys can show me how to do it please ?

My Excel versions is this :

Microsoft® Excel® pour Microsoft 365 MSO (Version 2302 Build 16.0.16130.20806) 64 bits

Answer 1

Move PRIORITY to Column Labels and to arrange the column elements:

Rightclick in PivotTable any labael(Med,Low,High). Sort->MoreSortOptions select Manual and close window. On Pivot, clickdown frame of a label and drag to the position.

Since i work on E2010 maybe in 365 there is a simpler solution (references from the above image):

• First select an empty range where in the top left cell type =K17
• Copy this cell to range 4 rows x 5 columns (this makes a conn. to the original pivot table.)
• Select this new range, and Insert-> Chart -> Column -> 2-D Stacked Column
• Right Click Plot Area -> Select Data ->Switch Row Column
• Select on the chart bar a Grand Total and right click.
• Click Add Data Labels
• Again right click and Format Data Series
• Click in the left pane Fill, and on the right pane No Fill. Then click Close.

The totals are on the top of the columns.