To consolidate data from different sheets into a one master sheet, execute the following steps:
1- Make sure that source data is arranged correctly. If you want the Excel’s consolidate feature to work accurately then ensure that each sheet has a similar layout, in which each column should contain similar data along with the header like we have three different sheets below:
2- Open a blank sheet, go to the Data tab, from the Data Tools section, select the Consolidation.
3- The consolidate box will appear, do following tasks:
- Select the SUM function from the Function option.
- Click the Reference box, select the range A1
:E4in the branch1 workbook, and click Add.
- Repeat the above task for branch2 and branch3 workbook.
Check Top rows, Left column and Create links to source data options.
4- Click OK. Results:
Relevant Lesson: How to consolidate a specific column from multiple workbooks?