Consolodating sheets in excel
Note: if you don't check Top row and Left column, Excel sums all cells that have the same position. Click in the Reference box, select the range A1: E4 in the district1 workbook, and click Add. Repeat step 4 for the district2 and district3 workbook. Check Top row, Left column and Create links to source data.
You can see that, even though we left the final data set out of the All References box in step 8, Excel now displays that reference in the All References box. As you can see, cells F3: H3 and B4: E4 are blank instead of zero.And Kutools for Excel is a very handy tool, why do not free install it to have a try? You can use Excel's Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. If you check Create links to source data, Excel creates a link to your source data (your consolidated data will be updated if your source data changes) and creates an outline. see screenshot: Note: For selecting same ranges in all checked worksheets, you click the Same range button. Click Next to select calculation from Function section, and check Top row and Left column options. Click Finish, then click Yes or No to save the scenario or not. You can click Add in Combine Worksheets--Step 2 of 3 dialog to add workbooks and ranges to directly consolidate without name range. Check Create links to source data option in Combine Worksheets--Step 3 of 3 dialog to auto update consolidated results as source data changing.
With Combine utility, you can quickly merge same name sheets, same ranges across multiple sheets and so on.
Consolidate easily handles differing numbers of rows or columns.