You can use the “Consolidation» in Excel to merge sheets located in one or more workbooks into one. The pictures below show three different documents.
Before you start: If the worksheets are identical, it’s much easier to create internal links (if all are in the same workbook) or external links (if there are multiple workbooks) to bring the data together.
As you can see, the sheets are not identical. However, the beauty of the toolConsolidationin that it can easily add, count, average, etc. data from different sheets and documents. It’s much easier than using formulas.
- Open three documents.
- Open an empty book. Click the button Consolidate (Consolidation) tab Data (Data).
- Select function Sum (Sum) to summarize the data.
- Place cursor on next line, select range A1: E4 in document district1.xls And click the Add (Add).
- Repeat step 4 for documents district2.xls и district3.xls.
- Check the boxes next to Top Row (Top line captions), Left Column (Left column values) and Create links to source data (Create links to source data).
Note: If you don’t enable options Top Row (Top line captions) and Left Column (Left column values), Excel will sum all cells with the same arrangement. For example: cell V2 (distric1.xls) + cell V2 (district2.xls) + cell V2 (district3.xls). Since our sheets are not identical, we need Excel to add cells with the same labels.
If you check the box next to Create links to source data (Create Links to Source Data), Excel will create links to the source data. When the source data changes, the consolidated data will be updated.
- Press OK.
Result: