Sorting data

From WikiEducator
Jump to: navigation, search

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.)

Icon activity.jpg Follow Along
Let's make a plan for the sort procedure:

1. What is the criterion?

2. What data will be included in the sort

  • 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).

Icon activity.jpg Follow Along
The first step is always to make a plan for the sort procedure:

1. What is the criterion?

2. What data will be included in the sort

  • 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...)

Icon activity.jpg 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.

Icon present.gif
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.