Page 157 - Computer - 10(A&B)

Step 8: Specify where you want to display the result by selecting a target range from the Copy results to box. If the
target range is not named, select it using Copy results to select worksheet Average.

Step 9: Select Link to source data to insert the formulas. This generates the results in the target range instead
of the actual results. If you link the data, any values modified in the source range are automatically
updated in the target range. The corresponding cell references in the target range are inserted in
consecutive rows, which are automatically ordered and then hidden from view. Only the final result,
based on the selected function, is displayed.
Step 10: Under Consolidate by setting, select either Row labels or Column labels, if the cells of the source data
range are not to be consolidated corresponding to the identical position of the cell in the range, but
instead according to a matching row label or column label. To consolidate by row labels or column
labels, the labels must be contained in the selected source ranges. The text in the labels must be identical,
so that rows or columns can be accurately matched.

Step 11: Click the OK button to consolidate the ranges.

You will see grouping icons down the left of the screen which you can use to display and hide the data. Next to
rows, there are plus signs. This signifies that cells are part of a group that is currently collapsed. Clicking on the
plus sign will expand the group and there is a line connecting these rows to the left.
The data from the consolidation ranges and target range are saved when you save the worksheet. If you later
open a worksheet in which consolidation has been defined, this data will again be available.
If you are continually working with the same range, then you probably want to give range a name. To do so, you
can use Define Range option. The Define Range option is available under the Data menu.

Creating Subtotals
Calc’s built-in Subtotal option allows us to add subtotals and grand totals automatically to a list of data. This
option helps to summarize data by creating groups automatically and use common functions like SUM, COUNT,
and AVERAGE. For example, the Subtotal option could help to calculate the cost of office supplies by type from
a large inventory order. It will create a hierarchy of groups, known as an outline, that helps organising the
worksheet.

39