Spreadsheets are labor-intensive documents. Usually, their contents is entered carefully, one sheet at a time, at an input rate far below a text document. However, like most spreadsheets, OpenOffice.org has several tools for removing some of the drudgery from input.

The most basic ability is to drop and drag the contents of one cell to another with a mouse. However, Calc also includes several other tools for automating input, especially of repetitive material. They include the Fill tool, selection lists, and the ability to input information into multiple sheets of the same document.

Using the Fill tool on cells

In Draw, OpenOffice.org's graphics program, fill tools automatically fill an object with a color or gradient, saving you the effort of filling it in by hand. In much the same way, Calc's Fill tool does instantly basic work that would take much longer for you to enter manually.

At its simplest, the Fill tool is a way to duplicate existing content. Start by selecting the cell to copy, then drag the mouse in any direction to select the cells to fill and/or overwrite, followed by Edit -> Fill and the direction in which you want to copy: Up, Down, Left or Right. Choices that are not available are grayed out, but you can still choose the opposite direction from what you intend, which could cause you to overwrite cells accidentally unless you are careful.

A more complex use of the Fill tool is to use the fill series defined in Tools -> Options -> OpenOffice.org Calc -> Fill Lists. The default lists are for the full and abbreviated days of the week and the year, but you can create your own lists as well. To add a fill series to a spreadsheet, select the cells to fill, and, choosing Edit -> Fill -> Series, enter an item in any series you have defined. The selected cells will then add the other items on the list sequentially, beginning from the top when they reach the end of the list.

You can also use Edit -> Fill -> Series to create a one-time fill series for numbers by entering the starting and end values as well as the increment. For instance, if you entered the starting and end values of 1 to 7 with an increment of 2, then you would get the sequence of 1, 3, 5, 7.

In all these cases, the fill tool creates only a momentary connection between the cells. Once they are filled, the cells have no further connection with one another.

Using selection lists

Selection lists resemble autosort lists. They are available only for text, and are limited to using only text that has already been entered in the same column.

To use a selection list, select a blank cell and press Ctrl + D (in earlier versions of Calc, you could also make a selection from the right-click menu, but that item has been removed from recent versions). A drop-down list of appears of any cell in the same column that either has at least one text character or whose format is defined as Text.

Methods of sharing content between sheets

At times, you might want to enter the same information in the same cell on multiple sheets. For example, you might want to set up standard listings for a group of individuals or organizations. Instead of entering the listings over and over, you can enter it in all the sheets at once by selecting all the sheets, then entering the information in the current one. Be aware, however, that this technique will overwrite any information that is already in the cells on the other sheets -- and without any warning. For this reason, when you are finished, be sure to unselect all the tabs, so that each sheet can be edited without affecting any others.

Alternatively, you can use the Fill tool to copy contents automatically to another sheet. To do so, select the sells to copy, or, if you want to copy the entire sheet, press Ctrl + A or clicking the box in the upper left corner of the spreadsheet. Then select the tab of the sheet to which to copy, followed by Edit -> Fill -> Sheets. The default is to copy all formatting, but you can select the formatting to include from the dialog window. You can also choose whether to add, subtract, multiply or divide values in the first sheet by those in the sheets to which you are copying. Other options are to skip empty cells in the copying, transpose or switch the position of columns and rows on the target sheet in a kind of simplified datapilot creation, or create a link so that changes on the original sheet are mirrored on the target sheets.

Conclusion

If you choose, you can enter information on your spreadsheets entirely by hand -- just as some people stubbornly refuse to use styles and persist in using manual overrides of formatting instead. And, in some cases, you may have no particular advantage in using the input automating tools.

Still, none of these tools are difficult to learn, and the Fill tool even includes the ability to create custom lists that you can re-use. Take a few minutes to learn them, and the next time you have some repetitious information to input, you can save yourself both time and boredom.

Bruce Byfield is a computer journalist who writes regularly for Datamation, Linux.com, and the Linux Journal web site.