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

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 theCombine Sheetsbutton 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 theCombinebutton Done! All data are copied into one master spreadsheet.

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 theCombine Sheetsbutton SelectCombine data from multiple worksheets into one worksheet
  2. Check the boxMy table has headersif 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 theCombinebutton 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.

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 theCombine Sheetsbutton SelectCombine data from same name worksheets into one worksheet.
  2. Check the boxMy table has headersif 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 theCombinebutton 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.

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 XLToolsWorkbook Organizer. It helps copy and otherwise manage multiple worksheets at at a time.

Any questions or suggestions?
Amit KumarHello Maria, Thanks for the link, I found the way here itself on the site. Let me know if you've option/ functions that can help me work on data shifting within different workbooks or sheets, with different column terminology. Means, column Freelancer Name in one sheet can be Contract Name in another. Kindly let me know your thoughts, Thanks!!! BR, Amit Jha
March 25, 2020 at 14:22
Maria BalobanovaHi Amit, looks like theSQL QueriesorAutomationfeatures would be more helpful here, but we'll need to take a look at your specific tasks. Or maybe try the native Excel VLOOKUP function.
March 25, 2020 at 17:56
BinduHi, Is it possible to combine data from two workbooks only when, In 1st workbook, I have Sheet1 & Sheet 2 data, & in 2nd workbook, I have same sheet1 and sheet 2 data, Required result: When I combine 1 & 2 worksheets, A data should get an update in A sheet and B data should get an update in the B sheet itself.
December 27, 2019 at 11:29
Maria BalobanovaHi Bindu, thank you for the question. Looks like you want to merge and consolidate data in worksheets. However, our Combine Sheets tool is meant for a different task - essentially it will copy data from multiple worksheets into one. I have emailed you - we'll need more details to understand what exactly your task is, and maybe find a solution.
December 30, 2019 at 14:27
How can we help?