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

Before you begin, add Combine Sheets to Excel

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

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

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?
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 the SQL Queries or Automation features 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?