Calc Guide

Introduction
Once you are familiar with functions and formulas, the next step is to learn how to
use Calc's automated processes to quickly perform useful analysis of your data.
Calc includes several tools to help you manipulate the information in your
spreadsheets, ranging from features for copying and reusing data, to creating
subtotals automatically, to varying information to help you find the answers you need.
These tools are divided between the Tools and Data menus.
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 it is easier to
select the cells with the mouse. Click the Shrink/Maximize icon beside a field to
temporarily reduce the size of the tool’s window, so you can see the spreadsheet
underneath and select the cells required.
Sometimes, you may have to experiment to find out which data goes into which field,
but then you can set 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.
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 save time in making
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 (also known as pivot tables), but
they are a topic that is sufficiently complex that it requires a separate chapter. (See
Chapter 8.)
Consolidating data
Data > Consolidate provides a way to combine data from two or more ranges of
cells into a new range while running one of several functions (such as Sum or
Average) on the data. During consolidation, the contents of cells from several sheets
can be combined into one place.
1) Open the document containing the cell ranges to be consolidated.
2) Choose Data > Consolidate to open the Consolidate dialog. Figure 240 shows
this dialog after making the changes described below.
3) The Source data range list contains any existing named ranges (created
using Data > Define Range) so you can quickly select one to consolidate with
other areas.
4) If the source range is not named, click in the field to the right of the drop-down
list and either type a reference for the first source data range or use the mouse
to select the range on the sheet. (You may need to move the Consolidate dialog
or click on the Shrink icon to reach the required cells.)
5) Click Add. The selected range is added to the Consolidation ranges list.
6) Select additional ranges and click Add after each selection.
Chapter 9 Data Analysis 241