Data not sorted correctly in Chart and Select Data Source greyed out

[LarisaG]

Hello all,

I'm writing this because I am stuck and cannot figure out where 2 issues come from of some Charts I've been working on and how I can fix them.
I am using M365.
I've been checking for various causes and solutions online in the last few days and unfortunately, I could not figure it out on my own.

The first issue - refers to the fact that the data I have sorted in my Pivot table is not sorted in my Chart and I cannot seem to find a way to sort it there.
As per the below example, I have some data for Week 21 and Week 22. as you can see, in the Pivot Table, Week 21 is on the top of the table, followed by Week 22.
The Chart, however, displays the data starting with Week 22, then continuing with Week 21. If I switch the data in the Pivot table, it also switches in the Chart, but I cannot seem to get them in synch and I am not sure why:

The second issue - refers to the fact that I cannot use any buttons in the Select Data Source of any chart, I try to right click. This happens in any Excel file I work with, I have tried older edited files and new fresh excel files. The options appear greyed out when I try to create a pivot table and then a chart based on the pivot table, but also when I try to create a chart directly based on the data, without a pivot table.

Moreover, I've noticed that in the Excel version I have, the Chart filter button is also missing (compared to all the pages I've checked online in an attempt to sort it out).

Missing filter button in all my Excel files:

Any help, tips or suggestions are highly appreciated!

Thank you!!!

 

[Marius44]

Hello
For the first problem: axis in reverse order

For the other problems: sorry but I have not 365

Hello,
Mario

 

[Jon Peltier]

1. Axis in reverse order, as Marius44 says.

2. In a pivot chart, you can only plot whatever is in the pivot table. You have to filter the various pivot fields to show what you want.

 

[LarisaG]

1. Axis in reverse order, as Marius44 says.

2. In a pivot chart, you can only plot whatever is in the pivot table. You have to filter the various pivot fields to show what you want.

Hello Jon,

Thanks for the reply! I have managed to solve the first issue with the axis in reverse order.
I am not sure I understand your reply for the second issue, with the buttons being greyed out in the Select Data source field. Does this mean that the buttons in that window cannot be used anymore and the data in the chart can be changed only based on the filters I have on the pivot table?

I have more example more data in a pivot table, but the chart based on the pivot table, I would like to have less data there.
I couldn't figure out why the filter button from the charts is missing and the Select Data source buttons are greyed out, so I did a sort of a workaround: I created 2 pivot tables, one containing the Sum of Numbers with 2 columns (so I could add symbols and show the monthly difference using symbols) and another regular pivot without the difference calculated so I can use the chart.

I don't think there is a better way to do this, right?

 

[LarisaG]

Hello
For the first problem: axis in reverse order

For the other problems: sorry but I have not 365

Hello,
Mario

Thank you Marius! The Axis in reverse order helped fix the first problem.
I was most probably using incorrect keywords when searching for a solution for this and thus I couldn't figure it out. Or I was trying an approach that was incorrect.

I appreciate your help and quick reply!

 

[Jon Peltier]

... I did a sort of a workaround: I created 2 pivot tables, one containing the Sum of Numbers with 2 columns (so I could add symbols and show the monthly difference using symbols) and another regular pivot without the difference calculated so I can use the chart.

Since the main pivot table and the pivot chart show different views of the data, you do in fact need two pivot tables. This is not really a problem once you realize that's what you need to do.

 

[LarisaG]

Since the main pivot table and the pivot chart show different views of the data, you do in fact need two pivot tables. This is not really a problem once you realize that's what you need to do.

Hello Jon,

Thank you! I understand now, so the way I've done it is the only possible way.
I was confused by the fact that the filter button is missing on my chart and the buttons in the Select Data Source window are greyed out. I was under the impression those could help me show different views in a single pivot table with a single chart.

Thanks again for clarifying it!