Once you are comfortable with inputting functions and formulas, the next step is to learn how to automate the processes. Calc includes over half a dozen tools to help you manipulate functions and formulas, ranging from features for copying and reusing data to creating subtotals automatically to ones for varying information to help you find the answers that you need. These tools are divided between the Tools and Data menus, according to no apparent logic.

If you are a newcomer to spreadsheets, these tools can be overwhelming at first. However, they become simpler if you remember that they all depend on input from either a cell or a range of cells that contain the data with which you are working. You can always enter the cells or range manually, but in many cases, you can also use the Shrink/Maximize icon beside a field to change temporarily the size of the tool's window while you select the cells with the mouse. Sometimes, you may have to experiment with which data goes into one field, but, once you have found out, the rest is simply setting a selection of options, many of which can be ignored in any given case. Just keep the basic purpose of each tool in mind, and you should have little trouble with Calc's function tools.

Copying and reusing data

Data -> Consolidate provides a graphical interface for copying data from one range of cells to another, then running one of a dozen functions on the data. Most of the available functions are statistical (such as AVERAGE, MIN, MAX, STDEV0, and the tool is most useful when you are working with the same data over and over.

If you are continually working with the same range, then you probably want to use Data -> Create Range to give it a name. Otherwise, set the source and target ranges to undefined, and either enter the range manually or select it with the mouse. Once you have set both, select the function to apply in the target, then click the Add button. To help you keep track of the sources, Calc lists them in the Consolidation range pane of the Consolidate window.

Creating subtotals

SUBTOTAL is actually a function listed under the Mathematical category when you use the Function Wizard (Insert -> Function. Because of its usefulness, the function has a graphical interface accessible from Data -> Subtotal.

As the name suggests, SUBTOTAL exists to create tallies. Specifically, it tallies data arranged in a database array -- that is a group of cells with labels for columns and/or rows. Using the Subtotals window, you can select database arrays, then choose a statistical function to apply to them. For efficiency, you can choose up to three groups of database arrays to which to apply a function. When you click the OK button in the window, Calc adds subtotals and grand totals to the selected arrays, using the Result and Result2 cell styles for them.

If you use more than one group, then you can also arrange the subtotals according to choices made on the window's option tab, including ascending and descending order or using one of the pre-defined custom sorts defined in Tools -> Options for Calc.

Defining cell content and adding help

Data -> Validity defines the type of contents that can be entered in a cell. Depending on how Validity is set up, the tool can also define the range of contents that can be entered and provide help messages that explain the content rules you have set up for the cell and what users should do when they enter invalid content. You can also set the cell to refuse invalid content, accept it with a warning, or -- if you are especially well-organized -- start a macro when an error is entered.

Validity is usually used when you are designing a spreadsheet for other people to use. However, you can also use validity in your own work as a guide to entering data that is either complex or rarely used.

But Validity is most useful for cells functions. If cells are set to accept invalid content with a warning, rather than refusing it, you can use Tools > Detective > Mark Invalid Data to find the cells with invalid data.

Note that a validity rule is considered part of a cell's format. If you select Format or Delete All from the Delete Contents window, then it is removed. If you want to copy a validity rule with the rest of the cell, use Edit > Paste Special > Paste Formats or Paste All.

Varying cell contents

Tools > Scenarios let you enter variable contents - scenarios -- in the same cell. Each scenario is named, and can be edited and formatted separately, and chosen from a drop down list. When you print the spreadsheet, only the contents of the currently active scenario is printed.

By adding a scenario, you can quickly change the arguments of a formula and view the new results. For example, if you wanted to calculate different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. If you had another formula that calculated your yearly income and included the result of the interest rate formula as an argument, it would also be updated. If all your sources of incomes used scenarios, you could efficiently build a complex model of your possible income.

Once scenarios are added to a spreadsheet, you can jump to a particular scenario by selecting the Scenario button on Calc's Navigator, then selecting a scenario from the list. You can also color code scenarios to make them easier to distinguish from one another.

Providing alternate versions of formulas

Like scenarios, Data -> Multiple Operations is a way of providing alternate versions of a formula. Unlike a scenario, the Multiple Operations tool does not present the alternate versions in the same cells or with a drop-down list. Instead, the Multiple Operations tool creates a formula array: a separate set of cells that give all the alternative results for the formulas used. Although the tool is not listed among the functions, it is really a function that acts on other functions, allowing you to calculate different results without having to enter and run them separately.

To use the Multiple Operations tool, you need two arrays of cells. The first array contains the original or default values and the formulas applied to them.The formulas must be in a range.

The second array is the formula array. It is created by entering variable – alternative values – for one or two of the original values. Once the variable values are created, you use the Multiple Operations tool to specify the formula(s) you are using, as well as the original values altered by the variables. The result is the outcome of the formula(s) using each variable.

The Multiple Operations tool can use any number of formulas, but only one or two variables. With one variable, you add different values for the variables as column or row labels. You then add a column to the right of the column labels or a row below the row labels (whichever you are using) for the results of each formula using the variable values. With two variables, the values for one variable are used as column labels, and the values for the other variable as row labels.

Setting up multiple operations can be confusing at first. For one thing, when using two variables, you need to select them carefully, so that they form a meaningful table. Not every pair of variables is useful to add to the same formula array. Yet, even when working with a single variable, a new or tired user can easily make mistakes or forget the relation between cells in the original array and cells in the formula array. In these situations, Tools > Detective can help to clarify the relations.

You can also make formula arrays easier to work with if you apply some simple design logic Place the original and the formula array close together on the same sheet, and use labels for the rows and columns in both. These small exercises in organizational design will make working with the formula array much less painful, particularly when you are correcting mistakes or adjusting results.

Working backwards

Tools -> Goal Seek reverses the usual order for a formula. Usually, you run a formula to get the result when certain arguments are entered. By contrast, with Goal Seek, you work with a completed formula to see what values you need in an argument to get the results that you want.

To take a simple example, imagine that the Chief Financial Officer of a company is developing sales projections for each quarter of the forthcoming year. She knows what the company's total income must be for the year to satisfy stockholders. She also has a good idea of the company's income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? To answer, the CFO enters the projected earnings for each of the other three quarters and the projection for the entire year. Then she runs a goal seek on the cell for Q4 sales, and receives her answer.

Other uses of goal seek may be more complicated, but the method remains the same. To run a Goal Seek, at least one of the values for an argument must be a referenced cell or range. Only one argument can be altered in a single goal seek. After you get the result of a goal seek, you can replace the original value in the referenced cell with the result, or record the result elsewhere for later use, possibly as a scenario.

Optimizing content

Tools -> Solver is not yet listed in the OpenOffice.org online help, but it amounts to a more elaborate form of Goal Seek. The difference is that Solver is specifically designed to minimize or maximize the result according to a set of rules that you define. Each of these rules sets up whether an argument in the formula should be greater than, lesser than, or equal to the figure you enter. If you want the argument to remain unchanged, you simply enter a rule that the cell that contains it should be equal to its current entry. For arguments that you would like to change, you need to add two rules to define a range of possible values. Once you have finished setting up the rules, you can adjust the argument and the results by clicking the Solve button.

Conclusion

You don't need to learn them, especially if your spreadsheet use is simple, but as your manipulation of data becomes more sophisticated, they can shave minutes off your calculations, especially as you start to deal with hypothetical situations. Just as importantly, they can allow you to preserve your work and to share it with other people -- or yourself at a later session.

One function tool not mentioned here are datapilots (AKA pivot tables). But they are a topic that is sufficiently interesting and complex to require an article to themselves.

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