How to Automatically Sort Rows and Columns

Sorting data in a table or visualization can be done in various ways, depending on how you want the sort to performed. For drag-and-drop tables, you can sort via the column's right-click menu, but this sort order will be fixed and used on all tables that use the same input variable(s). If you want the rows or columns sorted based on data in each individual table/visualization and/or you want the sort to update based on updated data, you should use the Sort Rows and/or Sort Columns rule, which can also be applied to a variety of tables and visualizations. Finally, you can also sort a table based on another table or item in your document, see How to Select and Sort Rows and Columns Based on Another Item.

This article covers using the Sort Rows or Sort Columns rule to go from an unsorted table...

...to a table or visualization that has been sorted automatically by row according to a specified column (male) using a rule:

Requirements

A table or visualization.
The column used to sort cannot be a statistic in the Statistics > Right (e.g., Average or Row Sample Size).

Note: You must apply the rule directly to the input table if you see a table icon in the Data > Data Source > Data field in the object inspector of a visualization.

You will also see a warning in the Rules section of the object inspector if you have applied a rule directly to a non-compatible visualization, and you will need to apply it to the input table instead. For example:

Method

Select your table or visualization.
Go to Data > Rules in the object inspector.
Click Plus (+) > Sort/Reorder > Sort Rows or Sort Columns.
Select the Sort method for ascending or descending.
Choose the Column/Row selection method. This can be based on the largest, last, or a specified column/row.
OPTIONAL: If you selected Choose a column in Column selection or Choose a row in Row selection, you will be prompted to select the column/row to determine the sort order.
The Exclude from sort (in addition to NET/SUM) option ensures that any specified rows/columns remain at the bottom of the table. In this example, Don't care has been entered to prevent it from being sorted. This text is not case-sensitive.
OPTIONAL: The Never exclude from sort field can be used when the rule automatically sorts a category that shouldn't be sorted. For example, if you have the word Others in your row/column label, adding Others to this field will ensure it is not mistaken for an Other/Specify option and left at the bottom or right of the table. This text is not case-sensitive.
Press OK.

Sort Rows.png

Note the following:

The output will be sorted using the first statistic shown if multiple statistics are applied.
If rows/columns contain exactly equal values, the sort ordering is performed to include alphabetical order in addition to values.
By default, if there are multiple columns in the output, the rule will sort according to the largest column (according to the Column Sample Size in the Statistics - Below or Row Sample Size in Statistics - Right). Using this option:
- This script will have no effect when Column Sample Size/Row Sample Size is not available from the Statistics > Below/Statistics > Right.
- If two or more columns have the same value for Column/Row Sample Size then the output will be sorted according to the right-most column/bottom row with that value of Column/Row Sample Size.
Rows/columns that look like they are Don't Know, Other/Specify, All of these, or None of these responses will be left at the bottom or right of the output, as will NET and SUM rows.
Values of NaN are treated as the largest possible negative number for sorting, which means that they will always be placed below other rows/to the right of other columns when sorting from highest to lowest (except those rows/columns that have been excluded from the sort).
When the table has spans in the rows/columns, the rows/columns of the table will be sorted within the spans.
When the underlying data updates, the output sorting will automatically update according to the rule conditions.
When the rule is applied to multiple outputs being sorted by column/row name, the exact same column/row name must be in all outputs the rule is applied to. If you want to change the sorting settings for a particular output, you will need to create a "fresh" version of the rule from the Automate menu to adjust the settings for that output.