Consolidating Data

Apache OpenOffice 4.1 Calc Guide

Chapter 9: Data Analysis

< Previous Section

Next Section >

• Introduction
• Consolidating Data
• Creating Subtotals
• Using “What If” Scenarios
  • Creating Scenarios
  • Settings
  • Changing Scenarios
    • Changing Scenario Properties
    • Changing Scenario Cell Values
  • Working with Scenarios Using the Navigator
  • Tracking Values in Scenarios
• Using Other “What If” Tools
  • Multiple Operations in Columns or Rows
    • Calculating with One Formula and One Variable
    • Calculating with Several Formulas Simultaneously
  • Multiple Operations Across Rows and Columns
    • Calculating with Two Variables
• Working Backwards Using Goal Seek
  • Goal Seek Example
• Using the Solver
  • Solver Example

< Previous Page

Next Page >

Consolidating Data

Data → Consolidate provides a way to combine data from two or more ranges of cells into a new range while simultaneously 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 a single item.

1. Open the document containing the cell ranges to be consolidated.
2. Choose Data → Consolidate to open the Consolidate dialog. Figure 243 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. Then, 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 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.

Figure 243: Defining the data to be consolidated

7. Specify where to display the result by selecting a target range from the Copy results to drop-down-list.
   If the target range is not named, click in the field next to Copy results to and enter the reference of the target range, or select the range using the mouse or position the cursor in the top left cell of the target range. Copy results to uses only the first cell of a target range instead of working with the entire range, as does Source data range.
8. Select a function from the Function list to specify how the values of the consolidation ranges will be calculated. The default setting is Sum, which adds the corresponding cell values of the Source data range and gives the result in the target range.
9. At this point, you can click  More  in the Consolidate dialog to access the following additional settings:
   
   • Select Link to source data to insert formulas that generate results in the target range rather than displaying the actual results. If you link the data, any values subsequently 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.

   • Under Consolidate by, select either Row labels or Column labels if the cells of the source data range will not be consolidated by the position of the cell in the range but rather according to a matching row label or column label. Moreover, to consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical so rows or columns can be accurately matched. If the row or column label of one source data range does not match any existing in other source data ranges, it is added to the target range as a new row or column. An example of using Row labels is shown below.

10. Click  OK  to consolidate the ranges.
11. If you are continually working with the same range, you may want to use Data → Define Range to give it a name. The consolidation ranges and target range are saved as part of the document. If you later open a document in which consolidation has been defined, this data is still available.

Figure 244 shows a simple example of using Data → Consolidate with the choices from Figure 243. The two Consolidation ranges are next to each other for illustration, but they might well be on separate sheets. Having chosen Consolidate by Row labels in the dialog, the sum of the Values is calculated for each Name.

Figure 244: A simple example of Consolidate