OpenOffice/Calc 3/Working with and in cells/Sorting data



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

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

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