As mentioned in the introduction of the section Consolidating data, the Data consolidation command allows you to consolidate data from one or more cell ranges, for example, in order to calculate their total sums.
Apart from consolidating data by position (see previous section), data can also be consolidated by its labels. This works as follows:
The data to be evaluated can be stored in any number of "source ranges" – all of them have one thing in common: a label has been added to each value (for example, into the cell left of the value).
When you start a consolidation with such source ranges and enable the option Labels in left column, PlanMaker calculates the sum of all values that have the same label on their left.
It doesn't matter in which order and how often each label appears within the source ranges. The size of the individual source ranges and the order of the data within them is irrelevant. PlanMaker solely uses the labels to determine which values to sum up.
Example
A fruit shop offers different fruit varieties. Each individual sale during the course of a day is recorded in a table. Now it should be calculated how much of each fruit variety was sold in total.
The individual fruit varieties appear in a completely random order, of course, but this does not interfere the consolidate command at all:
Simply choose the Data consolidation command. Add the cell range with the individual sales as source range (here B3:C11) Note: This must contain the numbers and their labels! Then select any target range for the result (in Figure B14:C16) and confirm.
Result: In the target range, the totals of the sales of the individual fruit varieties appear.
Performing a consolidation by labels
To consolidate data by its labels, proceed as follows:
| 1. | Enter the data into one or more cell ranges. Size and structure of these cell ranges don't matter. However, each of the values to be consolidated should have a label – either in the column left of them or in the row on top of them. |
| The cell ranges can be located in just one worksheet altogether, or be spread over multiple worksheets or even multiple files. |
| 2. | Choose the ribbon command Data | group Analyze | Data consolidation  . |
| 3. | Click into the edit field below Source ranges. There, enter the address of the first cell range containing the data to evaluate. (See also notes at the end of the previous section.) |
| Tip: Alternatively, with the dialog box still open, simply click into the table and select the cell range with your mouse. |
Important: Each source range must contain both the values themselves and their labels. The labels must be placed in the leftmost column or in the top row.
| 4. | Click on the Add button. |
| 5. | To add additional source ranges, repeat the steps 3. through 4. |
| 6. | At Target range, enter the address of the cell range where you want the results of the consolidation to be inserted. |
| Tip: It is sufficient to specify just the address of the cell in the top left corner of the target range. PlanMaker will then determine its size automatically. |
| Tip: You can simply click on the desired cell in the table to transfer its address into the dialog box. |
| 7. | At Function, choose the arithmetic function to be used for the consolidation. |
| 8. | Under Options, specify the position of the labels in the source ranges: |
| Labels in left column: The labels are stored in the leftmost column of each source range. (In that case, the corresponding values must be stored directly at the right of the labels.) |
| Labels in top row: The labels are stored in the top row of each source range. (In that case, the corresponding values must be stored directly below the labels.) |
| You can also check both options, in case you want to evaluate source ranges that have labels in their leftmost column and in their top row. |
| When you enable the Sort labels option, the results of the consolidation in the target range will be sorted according to the labels. |
| 9. | Click on Apply to start the consolidation. |
The data from the source ranges is now consolidated using the chosen arithmetic function. The result is inserted in the target range.
Note: The result of a consolidation is inserted into the table as fixed numbers. These numbers will not be updated when you modify the values in any of the source ranges.
Accordingly, this command's main purpose is to evaluate the current state of data, not regarding any changes made to them later (useful e.g. for monthly reports). See also Editing and updating consolidations.