A consequence of organizing your data into different workbooks and worksheets is that you need ways to manage, combine, and summarize data from more than one Excel document.
You can always copy data from one worksheet to another, but if the original value were to change, that change would not be reflected in the cell range to which you copied the data.
If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshots), if you don’t want to save this scenario, please click No.
When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.
Create a new workbook that you want to put the consolidated data, then click Data Consolidate, see screenshot: 2.
In the Consolidate dialog box, select Sum (as we want to show sum of values for consolidation ) from Function option, then click button from Reference to select the range that you want to summarize, and then click Add button to add the range reference to the All references list box. Using the same way to add other ranges to the All references list box.
And then click Finish button, and you will be asked if you want to save this scenario.
You can also remove any worksheets you and your colleagues won’t need by right-clicking the tab of an unneeded worksheet and, on the shortcut menu that appears, clicking Delete.
TIP You can also save your Excel 2016 workbook either as an Excel 97–2003 template (.xlt) or as a macro-enabled Excel 2016 workbook template (.xltm).
If you want to know more about this feature, please click Consolidate and calculate values across multiple workbooks into one worksheet.
Excel 2016 gives you a wide range of tools with which to format, summarize, and present your data.