MS-Office Excel releases with several features for customizing tables and charts full of important data. The program also offers efficient ways to combine and summarize data from multiple files, also known as worksheets. Common methods to consolidate in MS-Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature. Once you have done the consolidation, your information appears in a master worksheet as a reference whenever you need to generate reports. So, I would say, this is another awesome tool in MS-Excel.
Let me explain little more about preparation for before doing consolidation in MS-Excel 2016:
Verify the data in each worksheet appear in list format.
Before you go for a consolidation, make sure you have removed any blank columns and rows, and that each column is labeled with similar information.
In the current Workbook, Add and layout each column range to a separate worksheet. However, do not add the ranges to the master worksheet that you plan to consolidate.
As I mentioned about combine and summarize data in the above first paragraph, when you are needed to summarize data from separate ranges, consolidating the result in a single output range. For example, if you have a worksheet of expenses figure each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet.
Let me explain with asking a question and answering for your convenience:
How to get total of summarized value from different worksheets (2 different worksheets in your current workbook) using consolidate tool in MS-Excel 2016?
Keep the cursor in a cell that you want to get summarized result in your master worksheet =>
Select Data Menu =>
Click Consolidate =>
In the consolidate dialog box, select Sum in the Function box and then Keep the cursor in the Reference box =>
Select First worksheet and then in the worksheet, select a cell or cells range (where the values are) to get the data range for consolidate =>
Click Add button =>
Make sure to keep the cursor again in the Reference box =>
Select Second worksheet and then in the worksheet, select a cell or cells range (where the values are) to get the data range for consolidate =>
Click Add button =>
For more understanding, look at the screenshot please: