Combine Sheets add-in: 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:

  • Consolidate data across different workbooks into one worksheet
  • Merge data from same name worksheets and combine by tab name
  • Merge data from identically structured sheets under a single header
  • Preserve formatting in the resulting worksheet

 

Add "Combine Data" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

Combine data from sheets ribbon button

 

Learn your way around:

 

How to consolidate data from multiple worksheets into one in 3 steps

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:

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.

XLTools Combine Sheets options

 

How to merge multiple worksheets into one master worksheet

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.

Combine data from multiple worksheets into one

 

How to merge multiple worksheets with the same name into one master worksheet

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.

Combine data from worksheets of the same name into one

 

How data are copied into the resulting master worksheet

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.

 

How to combine multiple worksheets into one workbook

You can combine multiple worksheets into a single workbook with XLTools Workbook Organizer. It helps copy and otherwise manage multiple worksheets at at a time.

Any questions or suggestions? Please leave your comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *