Consolidating data from multiple workbooks in excel

Posted by / 02-Apr-2016 15:43

One of our customers recently received 47 workbooks and needed to get data from each sheet named "data" in these files.

With the Consolidation Assistant, she was able to first create a new workbook that pulled the data sheets (and renamed them so she knew where they came from) into one workbook. She then used the consolidate sheets feature to consoldate all 47 sheets into one worksheet.

Consolidate data from multiple worksheets with Consolidate function Consolidate data from multiple worksheets with advanced Combine utility(1.)Select Select Combine columns under To combine selected cells according to following options;(2.)Specify a separator for the combined data, here I select Space;(3.))Specify the cell you want to place your combined result;(4.)Specify how you want to deal with the combined cells. Repeat step 3 to add all ranges you need to the All references list. See screenshot: This method is unconvenient, because you need to select ranges one by one, and if you need to consolidate data across workbooks, you need to name data range. If you have installed Kutools for Excel, you may find the Combine utility can help you quickly accomplish a consolidate report.

Now we will be moving to new worksheet, give it a suitable name by right-clicking on its name, and click button but to make it more easier for you, we have defined name ranges.

So press F3 on keyboard to open name ranges in Paste Name dialog, rather than selecting sheets manually, which could be hectic if you are dealing with huge spreadsheet.

For instance, you have three sheets data as below screenshots shown, now you want to consolidate the average, max or min value of each product in each month, how can you do?

Some of you may think that calculate them by yourself is quick, but pay attention, here I just take three sheets as instance, and how about calculate manually if there are hundreds of sheets?

consolidating data from multiple workbooks in excel-44consolidating data from multiple workbooks in excel-40consolidating data from multiple workbooks in excel-64

As you can see in the screenshots below that we have included three worksheets containing students records in each exam.

One thought on “consolidating data from multiple workbooks in excel”