Manipulating lists in OpenOffice.org Calc

When asked to explain the purpose of spreadsheets, most people think of calculations first. And it's true that spreadsheets like Calcs have hundreds of different functions for performing calculations. However, probably the most common tasks in spreadsheets is manipulating lists.

Calc and other spreadsheets have few advantages over a word processor when you are just making a list. In fact, unless you have a spreadsheet template set up so that cells have word wrap and hyphenation, you might be better off using a word processor if all you have is half a dozen items on the list. However, as the number of list items creeps up into the hundreds and beyond into the lower fringes of database territory, the different ways that you can manipulate lists in spreadsheets starts to give them a distinct advantages. In Calc, searching, the printing of selected items, and, to a lesser extent, privacy can all be made easier through the use of outline group controls, filters, and customized sorts.

Online group controls

Sheets, rows, and columns can all be hidden or shown through the sub-menus of the Format menu. While elements are hidden, they are neither visible or printed, but can still be selected for copying if you select the elements around them. For example, if column B is hidden, it is copied when you select columns A and C. When you need them again, you can reverse the process, and show the elements again.

Individual cells can be hidden or shown via Format > Cell > Cell Protection. However, if you are continually hiding and showing the same cells, you can the processes easier by creating outline groups, which add a set of controls for hiding and showing the cells in the group that are quick to use and always available.

If the contents of cells falls into a regular pattern, such as four cells followed by a total, then you can use Data > Outline > Autoutline to have Calc add outline controls based on the pattern. Otherwise, you can set outline groups manually by selecting the cells for grouping, then choosing Data > Outline > Group from the Calc menus. The Group window gives you a choice of whether to group the selected cells by rows and columns.

When you close the window, the outline group controls are visible between either the row or column headers and the edges of the editing window. The controls resemble the tree-structure of a file-manager in appearance, and can be hidden by selecting Data > Outline > Hide Details. They are strictly for on-line use, and do not print.
The basic outline controls have plus or minus signs at the start of the group to show or hide hidden cells. However, if one or more outline group is nested in another, the controls have numbered buttons for hiding different levels of group.

Should you no longer need a group, place the mouse cursor in any cell in it and select Data > Outline > Ungroup. To remove all groups on a sheet, select Data > Outline > Remove.

Filtering which cells are visible

A filter is a list of conditions that each entry has to meet in order to be displayed. You can set three types of filters from the Data > Filter sub-menu:

• Automatic filters add a drop-down list to the top row of a column that contains commonly used filters. They are quick and convenient, and, because the condition includes every unique entry in the selected cells, are almost as useful with text as with numbers. In addition to each unique entry, automatic filters include the option to display all entries, the ten highest numerical values, and all cells that are empty or not-empty, as well as a standard filter. Their drawback is that they are somewhat limited. In particular, they do not allow regular expressions, so you cannot display contents that is similar, but not identical by using automatic filters.
• Standard filters are more complex than automatic filters. You can set as many as three conditions as a filter, combining them with the Boolean operators AND and OR. Standard filters are mostly useful for numbers, although a few of the conditional operators, such as = and < > can also be useful for text. Other conditional operators for standard filters include options to display the largest or smallest values, or a percentage of them. Useful in themselves, standard filters take on added values when used to further refine automatic filters. They are useful mainly for numbers.
• Advanced filters are structure similarly to standard filters. The differences are that advanced filters are not limited to three conditions, and their criteria are not entered in a dialog window. Instead, advanced filters are entered in a blank area of a sheet, then referenced by the advanced filter tool to apply them. If you are not planning to use filters very often, you may choose to delete the criteria after using them. But if you are using filters regularly, then label the area for advanced filters so that you can find it more easily.

Sorting records

A sort arranges the visible cells on the sheet. In Calc, you can sort by up to three criteria, with each criteria applied one after the other. Sorts are handy when you are searching for a particular list item, and become even more powerful after you have filtered data.

In addition, a sort is often useful when you add new information. When a list is long, it is usually easier to add new information at the bottom of the sheet, rather than adding rows in the proper places. Once you have added information, you can then sort it to update the sheet.

You can apply a sort by highlighting the cells it applies to, then selecting Data > Sort. The selected cells can be sorted by the order of information in up to three columns or rows, in either ascending (A-Z, 1-9) or descending order (Z-A, 9-1).

On the Options tab of the Sort window, you can choose the following options:

• Case sensitive: If two entries are otherwise identical, one with an upper case letter is placed before one with a lower case letter in the same position.
• Range contains labels: Does not include the column heading in the sort.
  Include formats: A cell's formatting is moved with its contents. If formatting is used to distinguish different types of cells, then use this option.
• Copy sort results to: Sets a spreadsheet address to which to copy the sort results. If a range is specified that does not have the necessary number of cells, then cells are added. If a range contains cells that already have content, then the sort fails.
• Custom sort order: Select the box, then choose one of the sort orders defined in Tools > Options > Spreadsheet > Sort Lists from the drop-down list. As you can see from those already defined, a sort order is a standard type of list, such as the days of the week or the months in a year. they can also be used for automatically filling cells.
• Direction: Sets whether rows or columns are sorted. The default is to sort by columns unless the selected cells are in a single column

Conclusion

The makers of free spreadsheets often overlook manipulating lists in favor of focusing on the development of functions. This focus is understandable, given the complexity and challenge of implementing functions. However, it also means that projects like Gnumeric continue to lack some simple features that many users rely on heavily. The majority of users probably use less than a few dozen functions -- but almost all of them are apt to write lists in spreadsheets. After all, task assignments, project planning, inventories, and grades are givens in the home and the work place. No doubt other free spreadsheets will get around to list manipulation some day, but, for now, Calc remains the only major spreadsheet to provide this basic functionality.

Bruce Byfield is a computer journalist who writes regularly for the Linux Journal and Newsforge websites.