Excel link: YOUR OBJECTIVE
In several Excel workbooks (files), you created tables that list sales figures for your company’s first quarter. Each table, in each workbook, contains sales results for a single location. To be clear: there is one file for Berlin, another for Frankfurt, and a third for Munich. The tables are positioned differently on each of their respective worksheets, and not all include the same sorts of ice cream.
Now, in a fourth file, you want to prepare a consolidation table that summarizes the first quarter’s sales numbers for all locations. It is important that the data in this new workbook automatically be updated whenever the source data changes.
Because the tables are not in identical positions and because they contain varying sorts of ice cream, you have to use Excel’s Consolidation function to create the desired summary table.
In this learning module “excel link”, you will be shown how to use the Consolidate function to summarize data from multiple files and how to implement it so that whenever the source data changes, the consolidated table will automatically be updated.
HOW DOES IT WORK?
Before you can use Excel’s Consolidation function to summarize data in multiple files, all worksheets (i.e., their respective files) must be open. After opening those files, open the worksheet in the file that is to contain the new consolidated table. Now, in that table, select the first cell in which you want the first cell (i.e., upper-left corner) of the consolidation table to appear. In our current example, go to the Icecream_Sales_3_8_4_3.xlsx file and select cell A4.
After clicking the Consolidate button, the Consolidate dialog box will appear. It requires the following input:
Here you can select from a list of mathematical and statistical funtions for consolidation. By default the Sum function is preselected.
The Reference: box serves for identifying the cell ranges that are to be consolidated from various worksheets.
In this box, the cell ranges identified in the above Reference: box can be collected (added or deleted).
In this area, options controlling row and column headers can be activated. You can also specify that dynamic excel links to the source data should be used in the consolidation table.
In our present example, leave the Sum function in the Function: box unchanged. Then, to select the first consolidation range, click in the Reference: box. Now click the Berlin_3_8_4_3.xlsx file (corresponding icon at bottom of desktop). In the Berlin sales table, select the complete range B4 to E9. You will see the corresponding cell range appear in the Reference: box with this notation: [Berlin_3_8_4_3.xlsx]Berlin!$B$4:$E$9. Finally, click the Add button…
The selected cell range will be transferred into the All references: box.
Now you can select the second consolidation range in the Frankfurt_3_8_4_3.xlsx file. Before you can select the next cell range and copy it to the All references: box with the Add button, the existing entry in the Reference: box has first got to be activated (so that it will be overwritten).
Finally, follow a the same procedure for the Munich_3_8_4_3.xlsx file. All three cell range references are now listed in the All references: box.
Before inserting the consolidated table into the Quarter 1 worksheet, you should activate the Top row and the Left Column options, so that these will automatically appear in the consolidation table. Activate the Create links to source data option, too. This allows the consolidated table to automatically update whenever the source tables change. Now you can finish by clicking OK.
The consolidated worksheet now appears:
In order to correctly identify the data in the columns and rows to be consolidated, Excel requires that headings that are supposed to be identical, be in fact spelled identically. If one row heading were spelled Chocolate, for example, and another were spelled Chocolat, Excel would view them as different items.
If the option Create links to source data was activated when you created a consolidated worksheet, the detailed origins, including values, of each consolidated item can be displayed. To do this, use the upper part of the structuring bar which should be apparent at the left edge of the display. If you click the small button bearing the number 2 (as illustrated below), the source filenames for each value will be displayed in an additional column, and the source values will appear in additional (preceding) rows as well.