Sorting data

Sorting beads

The power of spreadsheets comes from the ease with which we can manipulate data. The ability to sort alphabetic and numeric data by one or more criteria, in ascending or descending order, is a key feature.

Sorting data involves two main actions.

Action 1: Decide on the criterion(s)

In choosing one column by which to re-order the data, you are specifying a sort criterion: the data will be re-ordered, in ascending or descending order, according to the contents of the cells in this column. In Calc, you can sort by up to three criteria, with each criterion applied one after the other. For example, a sort might be specified to re-order first by "class number", second by "last name", and third by "first name", creating data sorted alphabetically within class.

Action 2: Identify and highlight the range of cells to be re-ordered

The cells in each row in a spreadsheet typically go together (e.g., different pieces of information about an individual participant) so when sorting it's important that all of the data in a particular row be re-ordered the same way (e.g., a person's last name in A2 stays with their first name in B2 and birthdate in C2). The key action in this second step is to highlight all of the data that needs to be kept together during the sort. Usually this is done by selecting the whole sheet (using the key combination Ctrl+A) or if some of the sheet contents should be left as is, selecting all of the relevant columns (e.g., when a summary table is positioned to the right of the data).

Do an alphabetic sort

Conference expense budget

Continuing with the conference budget example, let's sort the expenses alphabetically, so we can more easily find specific information. (It's not so hard to find things with only 4 rows of data, so pretend the list is much longer.)

Follow Along
Let's make a plan for the sort procedure: 1. What is the criterion?   The values in the Expense column 2. What data will be included in the sort   Rows 3-7, which include the column headings. Notice that we are not including rows 1 and 2, because we want these titles to stay where they are. Highlight rows 3-7, the data to be sorted Select Data > Sort.... Sort dialog, Options tab The Sort dialog displays. Click on the Options tab. Check the box for Range contains column labels. Sort dialog, Sort Criteria tab * Click the Sort Criteria tab. Under Sort by select Expense. Click the adjacent Ascending radio button. Click OK.

Results of alphabetic sort

The data in Column A, under Expense are now sorted in ascending order, that is A to Z order. The data under Unit Cost have also been reordered to match the order under Expenses. If you wished to sort the data from Z to A, you would have checked the Descending radio button.

Do a numeric sort

But what if we wanted to view the data in order of cost (so we can easily find the highest priced items).

Follow Along
The first step is always to make a plan for the sort procedure: 1. What is the criterion?   The values in the Unit Cost column, sorted highest to lowest. 2. What data will be included in the sort   Rows 4-7. Notice that we will NOT include the column headings this time. Highlight rows 4-7, the data to be sorted Select Data > Sort.... The Sort dialog displays. We don't have column labels, so we can go right to choosing the criteria in the "Sort Criteria" tab. Sort dialog, without column labels specified Under Sort by select Column C, because the Unit Cost data is in column C. Click the adjacent Descending radio button. Click OK.

Results of numeric sort

The data in Column C, under Unit Cost are now sorted in descending order, from highest to lowest. The labels under Expense have also been reordered to match.

Sort on multiple criteria

The sort routine in Calc can sort on up to three successive criteria, by specifying the different criteria in the three Sort by selection windows in the Sort dialog. This can be useful to organize a listing according to a number of factors (e.g., surname, first name, class...)

Order of sort criteria
The data shown below were sorted using three criteria, Gender, Name and Group. Sorted on multiple criteria Examine the data to determine in what order the criteria were specified.   The data were sorted by Group, then by Gender, then by Name. You can tell because all of the rows for each group are shown together, then within each group, all of the M's and F's are together, then within M (or F) the names are listed alphabetically.

Tip: When sorting, it is important that you select all of the data that goes together. If you omit any columns, the re-ordered data could become a meaningless jumble. The values in the omitted columns would be associated with the wrong rows. If rows are left out, the full set of data would not be sorted.

|