How do I force sorting in a pivot table or chart?

G_Luv wrote:

I want a pivot table because it's the correct tool. I have hundreds of rows of data in my original table. Pivot tables allow me to quickly create summary data. The alternative is to manually build a bunch of count formulas, but then I'm back to square one.

I couldn't get GETPIVOTDATA to work. But here's a simple example showing how you can use SUMIFS to produce the same results as the Pivot Table. Here I have 208 rows of data. The order of the rows could be much messier than shown. But I only needed to enter the formula once, in B2, and then I filled or copied it right and down.

The responses I entered manually in Row 1 in whatever order wanted, and the years I entered in column A.

Not as convenient as a Pivot Table, but not that much work, and without much work it gives you the desired degree of control over the appearance of the table (e.g. the order of the columns).

SG

G_Luv wrote:

That works. But I have a lot of data/charts to make.

Unfortunately, I will likely use Excel for this, since it has the functionality I want, even if I don't like it as well.

Go ahead and use Excel. It's a great app. I use it myself. However, as in the other thread, I think you will find that, like Numbers, Excel does not offer manually sorting of columns in Pivot Tables. (There may be a way with Pivot Charts but I couldn't get that to work).

If you want more control over exactly how your tables and charts appear, then use the COUNTIFS approach. It's easy to set up one summary table using that method and "clone" it to make variations. Click in it, then click the concentric circle "bulls-eye" to its upper left, and option-drag a copy onto the canvas.

SG

P.S. If you want to give feedback on Numbers then go to Numbers > Provide Numbers Feedback in the menu. This is a user-to-user forum.

Not sure I follow exactly what you are trying to do, but you can easily manually sort the order the series appear in a chart.

For example if you have a series out of order something like this:

Then in the chart click the series that represents Strongly Agree and change the Order for that series to 6 to make it appear last.

SG

[Edited by Moderator]

G_Luv wrote:

Pivot tables appear to only have automatic sorting.

Your question appears to be not only about Pivot Tables. It is also about charts.

And in charts you can (easily) have manually sorting, as shown.

If you need to display summary data in a table form where you control the order of the columns, then you can of course use COUNTIFS. Or perhaps get what you want using a Pivot Table and GETPIVOTDATA.

COUNTIFS - Apple Support

GETPIVOTDATA - Apple Support

SG

Hi G_Luv,

G_Luv wrote:

For example, Excel allows you to custom drag a column to override the automatic sorting. If Numbers can't do that...well...I stand by my "useless" comment.

Numbers does not have automatic sorting of columns. However, Numbers does allow drag and drop of table columns to rearrange the order of columns. Press and hold a column letter until it appears to "lift", then drag.

I haven't tested drag and drop in a pivot table, because I am wondering why you need a pivot table instead of a plain table to create a graph.

More information on your overall aim may lead to a solution.

Regards,

Ian.

I think you need to give an example of what you'd prefer the chart to look like. It seems logical to me (I'm not a statistician :) ).

It sounds to me like you're wanting to sort the responses within each year, but that doesn't make much sense (at least to me) since that means the first bar in every year could be different- e.g. in 2021 'Strongly Agree' was least popular, therefore first, but in 2022 it was the most popular option, so now it would be sorted last. To my neophyte eyes that means I'm having to hunt through the chart bars and the legend, for each year to understand what's going on. Is that what you want?