You created a new worksheet on which you used Excel’s Consolidate function to summarize sales figures from several of your company’s business locations. The sales figures for each location were entered on individual tables, i.e., one table per worksheet, one location per table.
The reason you used the Consolidate is that the source tables are positioned differently on their worksheets and their contents vary somewhat, i.e., not all products appear in each table.
After generating the excel consolidation table, you realize that you did not include the worksheet with the data for Cologne. You therefore want to modify the excel consolidation table to include Cologne. At the same time, you would now like to see the average sales results in the consolidation instead of the sum of all sales, as it’s been until now.
In this learning module, you will be shown how to modify existing excel consolidation tables.
Modifying an excel consolidation table: HOW DOES IT WORK?
To edit a consolidation table you must first select it. In the present example, you have to select cell range A4 to D10 from the Quarter 1 worksheet.
Then, on the Data tab, in the Data Tools group, click the button. Depending on the width of the Excel window, the Consolidate button may have a different appearance, i.e., large icon, small icon, labeled button.
After clicking the button, the Consolidate dialog box will appear. In the Function area, it shows the function currently being used. In the All references: area, it lists all cell references that are currently defined.
Now you can change the type of the calculation by selecting one from the Function: selection list. In the current example, you should choose Average.
To include additional data in the excel consolidation table, click the Reference: box. In the Cologne worksheet, select the cell range A5 to D9. Then copy this entry into the All references: box by clicking Add.
Since no further changes are required, you can finish up by clicking the OK button.
The Sum consolidation has been replaced by an Averages consolidation, and the sales figures for Cologne are included, as well.