As mentioned in the introduction of the section Consolidating data, the command Data consolidation allows you to consolidate data from one or more cell ranges, for example, in order to calculate their total sums.
In the simplest case, data is consolidated by its position. This works as follows:
You have entered the data into, for example, three "source ranges" which should be identical in size and structure. In each of the ranges, the individual data is thus in the same (relative) position.
When consolidating, the first cell (top left) in the first range is then added to the first cell in the second and third ranges. The result appears as the first cell in the "target range". The same is then done with all other cells in the source ranges.
Example
The daily revenues of two fruit shops are to be consolidated by means of a sum function, divided according to fruit varieties.
For this purpose, the revenues of the two shops were entered into a worksheet. In the figure, this data is shown in the cell ranges C3:C5 and C8:C10.
The command Data consolidation was then chosen and these two ranges were added as source ranges. The target range was set to C13:C15 (selected in the figure) and consolidation was started.
Result: In the target range, the sums of the daily revenues appear (again per fruit variety, just as in the source ranges).
Performing a consolidation by position
To consolidate data by position, proceed as follows:
| 1. | Enter the data to be consolidated into individual cell ranges of exactly the same size and structure. The order of the individual pieces of data must be identical in each range. |
| The individual cell ranges can all be in the same worksheet, in multiple worksheets or even in several different documents. |
| 2. | Choose the ribbon command Data | group Analyze | Data consolidation  . |
| 3. | Click into the Source ranges input field. There, enter the address of the first cell range with the data to be consolidated. (See also notes below.) |
| Tip: Alternatively, you can also click directly into the worksheet while the dialog box is open and simply select the cell range in the worksheet with the mouse to transfer it to the dialog box. |
| 4. | Click on the Add button. |
| 5. | To add additional source ranges, repeat steps 3. and 4. |
| 6. | For Target range, enter the address of the cell range in which you want the result of the consolidation to be inserted. |
| Tip: It is sufficient to specify the address of the cell in the top left corner of the target range. PlanMaker will then determine its size automatically. |
| Tip: You can also simply click on the desired cell directly in the worksheet to transfer its address to the dialog box. |
| 7. | For Function, select the arithmetic function to be used for the consolidation. |
| 9. | Click on Apply to start the consolidation. |
The data from the source ranges is now consolidated using the selected arithmetic function, and the result is inserted in the target range.
Note: The result of a consolidation is inserted into the worksheet as fixed numbers. These numbers will not be updated if you change the values in any of the source ranges.
This command is thus primarily useful if you want to evaluate the current state of data and no longer want to take changes into account (useful, for example, for monthly reports). For more information, see also Editing and updating consolidations.
Notes on specifying the source ranges
If you click in the Source ranges input field in the dialog box described above, a source range can be added using the following options:
▪Source range from the current worksheet
| To add a cell range that is located in the current worksheet to the source ranges, simply enter its address or name. |
| Tip: Alternatively, you can also click directly into the worksheet while the dialog box is open and simply select the cell range in the worksheet with the mouse. |
▪Source range from a different worksheet
| To add a cell range that is located in a different worksheet, enter its address preceded as usual by the other worksheet's name and an exclamation mark. |
| Tip: You can also simply select the cell range with your mouse. First, click on the desired worksheet in the worksheet register. |
▪Source range from a different document
| To add a cell range that is located in a different document, enter its address as described in External cell references). |
| Example: 'C:\My Folder\[My Workbook.pmdx]Sheet3'!D2:G5 |
| Tip: You don't have to manually enter the first part of the address (folder and file name). Simply click on the Browse button in the dialog box, and the program will then display a file dialog box in which you can select the desired file. |
Don't forget to click on the Add button after entering the address for each source range.