Assemble data from multiple worksheets into one master worksheet in seconds

Say, you have multiple regional sales reports. If you want to calculate and create charts on the entire data set, you need to consolidate all data in one worksheet. Navigating multiple workbooks, endlessly copying hundreds of rows, or writing VBA can be very time consuming tasks.

With the Combine Sheets add-in, you can assemble data into one master spreadsheet in a matter of seconds:

Combine Sheets is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

With the Combine Sheets add-in, you can consolidate and merge data from multiple worksheets – across different workbooks – into a single worksheet in just 3 steps:

1. Click the Combine Sheets button on XLTools ribbon Select the type of operation:

   • Combine data from multiple worksheets into one worksheet
   • Combine data from same name worksheets into one worksheet
2. Select the sheets you want to combine. The tree view displays all worksheets in all open workbooks.
3. Click the Combine button Done! All data are copied into one master spreadsheet.

Say, you have a number of worksheets and each contains a sales report for a different product category. The add-in helps you copy all these individual reports and combine data in one master worksheet.

1. Click the Combine Sheets button Select Combine data from multiple worksheets into one worksheet
2. Check the box My table has headers if it does.

   

   Tip:this way, data are combined under a single header. It is useful, when layout of worksheets to merge is consistent, e.g. when reports follow the same template. If table headers do not match, then each data set will be appended with its own header.
3. Select the sheets you want to combine by checking relevant boxes in the tree view.
4. Click the Combine button Done, all data from the selected sheets are assembled in a new master worksheet in a new workbook.

Note:don’t forget to save this master workbook to your computer.

Say, you have a series of workbooks and each contains a regional sales report. These reports are broken down into worksheets by product categories – so, tabs across regional reports have identical names. The add-in helps you copy data from across these reports into one master worksheet.

1. Click the Combine Sheets button Select Combine data from same name worksheets into one worksheet.
2. Check the box My table has headers if it does.

   

   Tip:this way, data are combined under a single header. It is useful, when layout of worksheets to merge is consistent, e.g. when reports follow the same template. If table headers do not match, then each data set will be appended with its own header.
3. Select the sheets you want to combine by checking relevant boxes in the tree view.

   

   Tip:instead of combining worksheets of the same name by groups (one name group after another), simply select them all at once. The add-in will automatically assemble data by same tab names and place it on separate respective worksheets in a master workbook.
4. Click the Combine button Done, all data from the selected same name worksheets are assembled in a new master workbook.

Note:don’t forget to save the master workbook to your computer.

Combining sheets means that data are essentially extracted and copied from multiple source worksheets into a new worksheet.

• Data are copied in full – the whole range until the last used cell on a source worksheet.
• Copied data sets are appended consecutively, one range below the last row of the previous range.
• XLTools Combine Data add-in keeps cell and table formatting, cell references, functions or formulas, merged cells, etc.
• Source data are not changed and remain intact.