0

I have a PivotChart that is a stacked bar chart in Excel 2016.

I want to control how the Ward numbers are displayed in the chart.

  1. I want to keep the PivotTable Ward number columns in alphabetical order.

  2. And I want to change the Ward number legend items so that they're in alphabetical order too.

I say “alphabetical order”, but I’m flexible on how “Other” is ordered. I really just want the wards to be in ascending numeric order.

enter image description here

I've looked at the Pivot Chart's properties, but I haven't found a way to set the legend order as ascending. The only way I've found to reorder the legend items is to manually move the Ward columns in the PivotTable:

enter image description here

That would work for the legend, but it would mean the PivotTable columns would be in descending order, which isn't what I want.

Question:

Is there a way to make both the PivotTable columns —and— the legend items be in alphabetical order?

For what it's worth, I have a different PivotChart, a line chart, that does what I want. I'm just not sure how to do the same thing in a bar chart. Screenshot.

User1974
  • 137
  • 1
  • 18
  • 1
    In the Pivot Table, you can change the order in the Pivot Table Fields Dialog box by drag and dropping each field in the order you want – cybernetic.nomad Dec 16 '22 at 16:19
  • @cybernetic.nomad Thanks. Just to clarify, that technique you mentioned is a helpful tip/easier way to do the reordering, but not an answer to the question, yes? – User1974 Dec 17 '22 at 07:26
  • I agree with cybernetic.nomad. The chart itself does not have settings to sort the legend. So you can manually adjust the order of the fields. – Emily Dec 19 '22 at 09:19
  • @Emily Yes, but if I understand correctly, that means the Wards (fields) will be in the wrong order in the PivotTable (descending order), which isn't what I was hoping for. Does that match your experience? – User1974 Dec 19 '22 at 21:01
  • 1
    @User1974 Yes, this action will change the order for Wards in pivot table. But you can press Alt+D, then press P to create a new pivot table and [unshare a data cache between PivotTable reports](https://support.microsoft.com/en-us/office/unshare-a-data-cache-between-pivottable-reports-87188806-0c24-4d17-b2f7-9e3a4a05542b?). Then hide the one that you do not wan to show. [Image](https://i.stack.imgur.com/wUC6E.png) – Emily Dec 20 '22 at 09:12

0 Answers0