1. Home
  2. Sorting Rows and Columns

Sorting Rows and Columns

Sorting information in a spreadsheet lets you see data the way you want and find values quickly. For example, you might want to arrange data in alphabetical order, or sort a column of values from lowest to highest. You can also sort a selection or rows and place them in one block at the top or bottom of your spreadsheet for easy identification.

Column Lwt2 before sorting Column Lwt2 after sorting in Ascending order, lowest to highest

Note: Missing values are considered to be ‘less than’ other values, so will be moved to the beginning of columns sorted in ascending order and the end of columns sorted in descending order.

Sorting columns by ascending or descending order

Sorting a numeric column reorders the values from lowest to highest (or vice versa); sorting a text column reorders the cells alphabetically.

  1. From the menu select Spread | Sort.
  2. Double-click a column name to select it as the sort column then select either Ascending or Descending.

  3. Click OK to perform the sort.

Sorting multiple columns

A multi-column sort lets you order your data based on a series of columns. The columns are sorted by the first column you specify, then rows that have equal values in the first column are sorted according to your second column, and so on. You can specify up to 5 columns to use in your sort.

Multi-column sort is best explained by working through an example. We’ll sort the spreadsheet below using a 2-column sort.

  1. From the menu select Spread | Sort.
  2. Double-click a column name to select it as the first sort column. In our example we’ll choose Drench.
  3. Define the sort order. We’ll choose Descending order, which will sort values in all our selected columns from highest to lowest.
  4. If this is a factor column choose how you want the column sorted. We’ll select Labels as our factor column because it contains text.
  5. Select Multicolumn. This adds the text Key:1 to Drench in the column list, identifying this column as our first sorting column.

  6. Select your second column. We’ll choose Lwt1. Genstat adds the text Key:2 to indicate that this is our second sorting column.
  7. Click OK.

The column Drench is sorted into descending alphabetical order. Next the cells in column Lwt1 are sorted into descending order within the column groups Twice, Once and Control.

Sorting a cell range or selection of rows

You can sort a cell range within a column or a selection of rows within a column. You can also place the sorted rows in a block at the bottom or top of your spreadsheet for easy identification.

To select individual rows
  1. Hold down the Ctrl key and click each required row with the mouse as shown below.

To select a cell range
  1. Within a column, click the first cell you want to select then hold down the Shift key and click the final cell in your range.
Performing a sort
  1. From the menu select Spread | Sort.

  2. Double-click a column name to select it as the sort column.
  3. Select the Order you want to sort the column (Ascending or Descending).
  4. If the column is a factor select a Sort Factors by option (see Sort options below).
  5. Choose whether to sort Selected or Unselected rows.
  6. If you want to place the sorted rows at the bottom of the sheet select the checkbox. If this is unselected your sorted rows will be placed at the top of the spreadsheet.

Sort options

Multicolumn Lets you use more than one key when sorting. Keys are indicated as “Key 1”, “Key 2“, and so on next to the column names when selected. The data are sorted into the order defined by the first key. If any values in this column are equal their order is defined by the corresponding values in the second key, and so on. Up to 5 columns can be used as multi-column sort keys.
Ignore case (Applies only to text columns or factor columns with labels)
If a sort is case sensitive, items are sorted a-z then A-Z for descending sorts. If a sort ignores case, uppercase and lowercase letters are treated the same. For example, the sort will not distinguish between ‘female’, ‘Female’ and ‘FEMALE’,
Ignore blanks (Applies only to text columns or factor columns with labels)
Specifies whether leading or trailing blanks should be ignored when sorting text columns.
Order Specifies whether the data is sorted into Ascending (a-z) or Descending (z-a) order. Missing values are considered to be “less than” other values, so will be moved to the beginning of columns sorted in ascending order and the end of columns sorted in descending order
Sort factors by Lets you choose which representation of the factor values to use when sorting. See Understanding Factors and Levels for an explanation of each method of displaying a factor (levels, labels and ordinals).
Rows to sort If a selection of rows has been made, these options allow you to control which rows to sort.

  • All – All rows in the spreadsheet are sorted.
  • Selected – Only selected rows in the spreadsheet are sorted.
  • Unselected – Only unselected rows in the spreadsheet are sorted.
Place sorted rows at bottom of list If selected, the sorted rows will be placed under the unsorted rows at the bottom of the spreadsheet, otherwise the sorted rows will be inserted at the top of the spreadsheet, before the unsorted rows
Updated on April 2, 2019

Was this article helpful?