3

I am working with data in groups in Excel. I currently have a pivot table and chart set up with:

  • Rows: symptom name grouped into one of four international severity grades
  • Columns: four datasets - mild, severe, both or not specified.

I need to present these as a stacked bar chart, which I have been able to do as below:image of stacked bar chart as it currently looks

As you can see, they are grouped into their international severity grades nicely but are arranged randomly within their groups. I would like them to be sorted descending by the sum of mild + severe + both + not specified within their groups, but I can't find a way to do this.

The closest I have come so far is to have a calculated field that works out this field - but then this displays as a fifth column category on the graph and I can't seem to remove it.

Does anyone have a way to do this? I think I may be able to drag and drop them into the right positions, but this will take a while so if there is a better way I would be really grateful!

Rory
  • 575
  • 4
  • 9
  • 22

1 Answers1

0

You can add a Grand Total for rows to the pivot table, then sort the grand total column. This will not add a series to the chart, but the items display sorted by total.

enter image description here

enter image description here

enter image description here

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • That looks like exactly what I wanted, however in my spreadsheet I couldn't get Grand Total to show for Rows. My Pivot Table was arranged as: http://imgur.com/a/cB76K – Rory Jul 07 '17 at 10:06
  • Urghhh, Mac. Everything is different on the Mac and ten years behind modern Excel. In Excel for Windows there is a ribbon command. Pivot Table Design tab > Grand Totals > On for Rows Only. Or it can be set in the Pivot table options dialog on the Totals and Filters tab. I added two screenshots above. Now, I don't know if that exists on the Mac. It only recently received slicers, which were added to Windows Excel with version 2007. – teylyn Jul 07 '17 at 20:13
  • Thanks teylyn - the option is there and just doesn't do anything. Interestingly I had the same behaviour in excel for windows (which the document was created in). I'll retest in a few days and see if I get anywhere. If not will happily accept your answer as it seems to have worked for your test data – Rory Jul 11 '17 at 16:16