Simron has an Excel workbook that has the details of various employees and their yearly income generation for the company. She has created a new worksheet in the workbook in which she wants to summarise the total income generation by all the employees. In such a situation, one can use the Data Consolidation option.
What is Data Consolidation?
Data consolidation basically means summarizing the data. In the example above, Simron has details about various employees in various sheets. She can use this option to summarize these sheets in a new one so that a user like her boss, can simply go through this consolidated report and easily understand the performance of the company and its employees.
Where is the ‘Consolidate’ option present?
It can be found in the ‘Data’ menu, in the ‘Data Tools’ group, towards the right-hand side.
How Does Data Consolidation Work?
To consolidate data, Simron should select the sheet in which she wants to summarize the data and select the ‘Data’ menu. From the ‘Data Tools’ group, she should select the ‘Consolidation’ option. The following dialogue box will appear.
She should now click on the arrow beside ‘Reference’ textbox and select the range of cells from each sheet. After this, she should click on the ‘Add’ tab. She should repeat this with all the sheets.
The ‘function’ is a dropdown list that looks a follows.
This has various functions. The result of data consolidated can be varied according to the function she choses. By default, the function set is ‘Sum’ and the data in the ‘All references’ are added together.
She can check the ‘Top rows’ or ‘Left Column’ options under the ‘Use labels in’ heading if she wants Excel to use these headers for the data consolidation. She can check the ‘Create links to source data’ textbox if she wants the viewer of the data consolidation to be able to view the source of each of these summaries with just a click of the mouse.
Once she has finished adding all the cells to be consolidated to the ‘All references’ textbox, she should click on the ‘OK’ button. The result will appear in the new sheet.