Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Consolidate and cumulative summing values in Excel

How to consolidate and sum up values for specific codes (ID number) from multiple tables in Excel

In this 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.

Consolidate in Excel
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).

How to consolidate and sum up values for specific codes (ID number) from multiple tables in Excel

Worksheet "FEB"

Consolidation and cumulative summing in Excel

Worksheet "MAR"

Conslodate and cumulative summing of multiple range data in Excel

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
  • Average
  • max
  • min
  • Product
  • cunt Numbers
  • etc...

Consolidate command is located on the ribbon on the 'Data' tab.

The process for creating a new common table command Consolidate

  1. Position the cursor on a new worksheet (in this case it is 'Cumulative')
  2. Click on the tab 'DATA'
  3. Click the command 'Consolidate'
  4. Include options: 'Top row' and 'Left column'
  5. Select the desired function
  6. Click Button for search workbook
  7. Click the worksheet 'JAN'
  8. Select the range of cells A1: A11
  9. Click again on the Button to search the workbook to return to the dialog window "Consolidate"
  10. Click the 'Add' button
  11. Repeat these steps (6-10) for any other worksheet
  12. And finally click on the 'OK' button
The process step by step for creating a new common table command Consolidate

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 regardless of the position of the other worksheets that we used as a base data source.

See the image below.
How to consolidate data from multiple tables and sum up them or find average and cumulative values in Excel