Consolidate data in Excel and merge multiple sheets into one worksheet

Combine multiple worksheets into one with Copy Sheets

Supposing you have a few spreadsheets that contain information about different products, and now you need to merge these sheets into one summary worksheet, like this:

With the Copy Sheets added to your ribbon, the 3 simple steps is all it takes to merge the selected sheets into one.

1. Start the Copy Sheets Wizard.

   On the Excel ribbon, go to the Ablebits tab, Merge group, click Copy Sheets, and choose one of the following options:

   • Copy sheets in each workbook to one sheet and put the resulting sheets to one workbook.
   • Merge the identically named sheets to one.
   • Copy the selected sheets to one workbook.
   • Combine data from the selected sheets to one sheet.

   Since we are looking to combine several sheets by copying their data, we pick the last option:
   

2. Select worksheets and, optionally, ranges to merge.

   The Copy Sheets wizard displays a list of all the sheets in all open workbooks. Select the worksheets you want to combine and click Next.

   If you don't want to copy the entire content of a certain worksheet, make use of the Collapse Dialog icon to select the desired range as shown in the screenshot below.

   In this example, we are merging the first three sheets:
   

   Tip. If the worksheets you want to merge reside in another workbook that is currently closed, click the Add files... button to browse for that workbook.

3. Choose how to merge sheets.

   In this step, you are to configure additional settings so that your worksheets will be combined exactly the way you want.

   How to paste the data:

   • Paste all - copy all the data (values and formulas). In most cases, it is the option to choose.
   • Paste values only - if you don't want formulas from the original sheets to be pasted into the summary worksheet, select this option.
   • Create links to source data - this will inset formulas linking the merged data to the source data. Select this option if you want the merged data to update automatically whenever any of the source data changes. It works similarly to the Create links to source data option of Excel Consolidate.

   How to arrange the data:

   • Place copied ranges one under another - arrange the copied ranges vertically.
   • Place copied ranges side by side - arrange the copied ranges horizontally.

   How to copy the data:

   • Preserve formatting - self-explanatory and very convenient.
   • Separate the copied ranges by a blank row - select this option if you want to add an empty row between data copied from different worksheets.
   • Copy tables with their headers. Check this option if you want the table headers to be included in the resulting sheet.

   The screenshot below shows the default settings that work just fine for us:
   

   Click the Copy button, and you will have the information from three different sheets merged into one summary worksheet like shown in the beginning of this example.

Example 1. Merge Excel sheets with a different order of columns

When you are dealing with the sheets created by different users, the order of columns is often different. How do you handle this? Will you be copying the sheets manually or moving columns in each sheet? Neither! Commit the job to our Combine Sheets wizard:

And the data will be combined perfectly by column headers:

Example 2. Merge specific columns from multiple sheets

If you have really large sheets with tons of different columns, you may want to merge only the most important ones to a summary table. Run the Combine Worksheets wizard and select the relevant columns. Yep, it's that easy!

As the result, only the data from the columns that you selected get into the summary sheet:

These examples have demonstrated only a couple of our merge tools, but there is much more to it! After experimenting a bit, you will see how useful all the features are. The fully functional evaluation version of the Ultimate Suite is available for download at the end of this post.