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.

______________________

--
Bruce Byfield (nanday)

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

What youre saying is completely true.

nutrients health's picture

What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say. Im sure youll reach so many people with what youve got to say.

Manipul

cris2per's picture

I know in java you were able to create an array of type object using a cheap trick and do cool things with that, however in C you wont be able to use a 2d array. The problem lies with you having two types of data, chars and int. l arginine

must agree.. you've got some

tsl99's picture

must agree.. you've got some valid points.Bisping Akiyama Fight Video i hope to read from you soon.

Manipulating Lists Vs Functions

Adventure Games's picture

>> The makers of free spreadsheets often overlook manipulating lists in favor of focusing on the development of functions.

I like this article. In response to the statement above, I would say that is so true. I use Excel a lot and I do find some of its list manipulating abilities lacking.

spreadsheets often overlook

Chris's picture

spreadsheets often overlook manipulating lists in favor of focusing on the development of functions... Do we have to have abilities?

Traducción al español

Anonymous's picture

Thanks you for your another

sinau's picture

Thanks you for your another translation
http://ondecity.com

Not the only one

Nicola Larosa's picture

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

Gnumeric has outline groups, filtering (including autofilters), sorting, and good Open Document Format support to boot.

menu bar sum

Anonymous's picture

Why is it so hard for OO to let you enter the menubar sum (the epsilon character on the menu bar) using a keyboard shortcut. Any keyboard shortcut! People keep requesting the ability to do this, and it never seems to happen. Excel has had alt+= for years.

Alt vs Ctrl

Gerald's picture

I am not a developer for OO, but I believe that Alt is used along with another character to access menus and buttons that contain an underline under the shortcut character. Whereas, Ctrl serves the purpose of shortcuts to commands. Perhaps Excel has an underline under the "=" character in the Sigma character on the menu bar?

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix