Speeding Up Data Entry

From Apache OpenOffice Wiki
Jump to: navigation, search

Speeding Up Data Entry

Entering data into a spreadsheet can be very labor-intensive, but Calc provides several tools for removing some drudgery from input.

The most basic ability is to drop and drag the contents of one cell to another with a mouse. 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

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 (or hold down the  ⇧ Shift  key and click in the last cell you want to fill), and then choose Edit → Fill and the direction in which you want to copy: Up, Down, Left or Right.

Documentation caution.png 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.
Tip.png A shortcut way to fill cells is to grab the “handle” in the lower right-hand corner of the cell and drag it in the direction you want to fill. If the cell contains a number, the number will fill in series. If the cell contains text, the same text will fill in the direction you choose.

AOO41GS05 031.png
Figure 85: Using the Fill tool

Using a Fill Series

A more complex use of the Fill tool is to use a fill series. The default lists are for the full and abbreviated days of the week and the months of the year, but you can create your own lists as well.

To add a fill series to a spreadsheet, select the cells to fill, choose Edit → Fill → Series. In the Fill Series dialog (Figure 86), select AutoFill as the Series type, and enter as the Start value an item from any defined series. The selected cells then fill in the other items on the list sequentially, repeating from the top of the list when they reach the end of the list. The available lists can be seen at Tools → Options → OpenOffice Calc → Sort Lists.

You can also use Edit → Fill → Series to create a one-time fill series for numbers by entering the start and end values and the increment. For example, if you entered start and end values of 1 and 7 with an increment of 2, you would get the sequence of 1, 3, 5, 7. If you set a Start value but no End value, the series will fill all the selected cells. If you set both a Start and an End value the behavior depends on the number of selected cells, If there are at least enough cells selected to contain the series, the series will stop at the End value, possibly leaving some selected cells empty. If there are not enough cells selected, the series will stop at the last selected cell and not reach the End value.

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.

AOO41GS05 032.png
Figure 86: Specifying the start of a fill series (result is in Figure 87)
AOO41GS05 033.png
Figure 87: Result of fill series selection shown in Figure 86

Defining a Fill Series

To define your own fill series:

  1. Go to Tools → Options → OpenOffice Calc → Sort Lists. This dialog shows the previously-defined series in the Lists box on the left, and the contents of the highlighted list in the Entries box.
AOO41GS05 034.png
Figure 88: Predefined fill series
  1. Click  New . The Entries box is cleared.
  2. Type the series for the new list in the Entries box (one entry per line). Click  Add . The new list will now appear in the Lists box.
  3. Click  OK  at the bottom of the dialog to save the new list.
AOO41GS05 035.png
Figure 89: Defining a new fill series

Using Selection Lists

Selection lists 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 . A drop-down list appears of any cell in the same column that either has at least one text character or whose format is defined as Text. Click on the entry you require.

AOO41GS05 036.png
Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools