How to Consolidate and Cumulative Summing Values
How to consolidate and sum up values for specific codes (ID number) from multiple tables in Excel
In this Excel example tutorial I will show how we can more tables containing the same code or ID numbers and different values Consolidate and sum up to a single worksheet. The goal is that for every employee we add up values for a particular ID code.
Thus, the Conditions are the name of employee and ID code of some products.
The situation is as shown below.
The workbook contains worksheets with the name of months in the year.
The last worksheet in the workbook, the worksheet named as "Cumulative" where we want to group all codes for each employee name to display the sum of values for the code.
In the picture below you see the final table obtained using the command Consolidate in Excel. Note that an employee of John for the ID code 'A10' have overall value 220 (1+2+10+100+107). So very quickly in a few steps we are Consolidate command, grouped and added up all the values from the worksheet "JAN", "FEB", "MAR" for the code 'A10' for the employee John. This also applies to other employees.
The situation on other worksheets is the following.
On the worksheet "JAN" there is a range of data which are the basis of the employees in the title of the column and the code (ID number) in the first column. For each code and the respective employees have a certain value. You notice that I marked in red values and code for worker "John", to the end that you pay attention to these source data and you understand what do we get Consolidate in Excel. I also on other worksheets indicate important information (red font).
To consolidate and added up data from multiple range of cells in Excel, we will use the command CONSOLIDATE. Using the command Consolidate except that we can add up values we can also get other values using some of the available functions, such as the following:
- Count Numbers
Consolidate command is located on the ribbon on the 'Data' tab.
The Process for Creating a New Common Table Consolidate
- Position the cursor on a new worksheet (in this case it is 'Cumulative')
- Click on the tab 'DATA'
- Click the command 'Consolidate'
- Include options: 'Top row' and 'Left column'
- Select the desired function
- Click Button for search workbook
- Click the worksheet 'JAN'
- Select the range of cells A1: A11
- Click again on the Button to search the workbook to return to the dialog window "Consolidate"
- Click the 'Add' button
- Repeat these steps (6-10) for any other worksheet
- And finally click on the 'OK' button
And finally you have created a new range of data with values (depending on which function you chose).
At the end of this operation, you can design the look of the respective range of data and convert it into a table via the Insert tab using the command Table.
And at the end of the cumulative summing and consolidation of data you see the sum of all values for the code (ID number) 'A10' for employees "John". Excel is added up all the values of the respective codes (Consolidate Data) regardless of the position of the other worksheets that we used as a base data source.
See the image below.