The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after - consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.
Today we will tackle a problem that many Excel users are struggling with daily - how to merge multiple Excel sheets into one without copying and pasting. The tutorial covers two most common scenarios: consolidating numeric data (sum, count, average, etc.) and merging sheets (i.e. copying data from multiple worksheets into one).
The quickest way to consolidate data in Excel (located in one workbook or multiple workbooks) is by using the built-in Excel Consolidate feature.
Let's consider the following example. Supposing you have a number of reports from your company regional offices and you want to consolidate those figures into a master worksheet so that you have one summary report with sales totals of all the products.
As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns:
To consolidate the data in a single worksheet, perform the following steps:
- Arrange the source data properly. For the Excel Consolidate feature to work correctly, make sure that:
- Each range (data set) you want to consolidate resides on a separate worksheet. Don't put any data on the sheet where you plan to output the consolidated data.
- Each sheet has the same layout, and each column has a header and contains similar data.
- There are no blank rows or columns within any list.
- Run Excel Consolidate. In the master worksheet, click the upper-left cell where you want the consolidated data to appear, go to the Data tab and click Consolidate.
Tip. It's is advisable to consolidate data into an empty sheet. If your master worksheets already has some data, make sure there is enough space (blank rows and columns) to contain the merged data.
- Configure the consolidation settings. The Consolidate dialog windows appears and you do the following:
- In the Function box, select one of the summary functions you want to use to consolidate your data (Count, Average, Max, Min, etc.). In this example, we select Sum.
- In the Reference box, clicking the Collapse Dialog icon and select the range on the first worksheet. Then click the Add button to have that range added to the All references Repeat this step for all the ranges you want to consolidate.
If one or some of the sheets reside in another workbook, click the Browse bottom to locate the workbook.
- Configure the update settings. In the same Consolidate dialog window, select any of the following options:
- Check the Top row and/or Left column boxes under Use labels if you want the row and/or column labels of the source ranges to be copied to the consolidation.
- Select the Create links to source data check box if you want the consolidated data to update automatically whenever the source data changes. In this case, Excel will create links to your source worksheets as well as an outline like in the following screenshot.
If you expand some group (by clicking the plus outline symbol), and then click on the cell with a certain value, a link to the source data will display in the formula bar.
As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. However, it does have a few limitations. In particular, it works for numeric values only and it always summarizes those numbers in one way or another (sum, count, average, etc.)
If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go. To combine just a couple of sheets, you may not need anything else but the good old copy/paste. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, you may want to employ one of the following techniques to automate the merge.
Overall, there are 3 ways to merge Excel worksheets into one without copying and pasting:
We are going to begin with the last option, as the most user-friendly and easy-to-use one.
Upon reading the title of this section, some of you may be confused. When discussing the Excel Consolidate feature, we pointed out that it cannot combine sheets by simply copying their data. Right, the build-in Excel consolidation option cannot do this, but Ablebits Consolidate Worksheet Wizard can :)
Supposing you have a few spreadsheets which contain some information about different products, and now you need to merge these sheets into one summary worksheet, like this:
Combine multiple worksheets into one in 5 quick steps
Assuming that you have the Consolidate Worksheets Wizard installed, the following five simple steps is all it takes to merge Excel sheets into one.
- Start the Consolidate Worksheets Wizard. On the Excel ribbon, go to the Ablebits tab, Merge group and click the Consolidate Worksheets button.
- Select worksheets and ranges to merge. Once you run the Consolidate Worksheets wizard, it will display a list of sheets in all open workbooks. Usually it is sufficient to select a worksheet in the tree for the wizard to fetch the range automatically. If you want to select a different range, make use of the standard Collapse Dialog icon .
Tip. If the worksheets you want to merge reside in some other workbook(s) that is currently closed, click the Add files... button to browse for that workbook.
- Select the consolidation type. Since we want to combine several sheets by copying their data, select the option Copy data from the selected ranges to one worksheet and click Next.
- Choose how you want to merge sheets. In this step, you are to configure the consolidation settings, and the most essential ones are as follows:Pasting options:
- Paste all - tells the wizard to copy values as well as formulas. In most cases, this is the option to choose.
- Paste values only - if you don't want formulas from the source sheets to be pasted into the summary worksheet, select this option.
- Paste link to data. - links the merged data to the source data. Select it 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 check of Excel Consolidate.
- My tables have headers. This option comes in very handy when you are merging worksheets with a different order of columns. For more detail, please see Example 1.
- Copy data only for the matching columns in my first table in the list - select it if you want to merge only certain columns in the worksheets. For more detail, please see Example 2.
- Preserve formatting - self-explanatory and very convenient.
- Separate the copied ranges by a blank row - select this option if you want to add empty rows in between the merged ranges.
- Select the destination for the merged data. Choose whether you want to output the combined data in a new worksheet, new workbook or a certain location in any existing sheet.
Click the Finish button and you will have a result similar to this:
Additional options for merging sheets in Excel
The Consolidate Worksheets Wizard provides 2 special options to handle the following scenarios.
When you are combining the sheets created by different users, the order of columns is often different. For the wizard to identify the columns correctly, make sure you have selected the option My tables have headers.
As the result, your Excel worksheets will be merged as demonstrated in the following screenshot.
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. A quick solution is to make a copy of one of the sheets and delete all irrelevant columns keeping only those you want to merge. And then, run the Consolidate Worksheets Wizard, and select the option Copy data only for the matching columns in my first table in the list.
As the result, the Consolidate Worksheets Wizard will find identically named columns in other sheets that you've selected and copy data only from those columns.
These examples have demonstrated only 2 options included in the Consolidate Worksheets Wizard, but there is much more to it. After experimenting a bit, you will see how useful all the features are. The fully functional trial version is available for download here. And if you find the add-in useful, we will gladly offer you the 15% off coupon code that we've created especially for our blog readers: AB14-BlogSpo.
If you are a power Excel user and feel comfortable with macros and VBA, you can combine multiple Excel sheets into one by using some VBA script, for example this one.
Please keep in mind that for the VBA code to work correctly, all of the source worksheets must have the same structure, the same column headings and same column order.
Power Query is a very powerful technology to combine and refine data in Excel. At that, it's rather complex and requires a long learning curve. The following tutorial explains the common uses in detail: Combine data from multiple data sources (Power Query).
If you are looking for a quick way to match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard. The latter is a visual user-friendly tool that lets you compare two Excel spreadsheets by a common column(s) and pull matching data from the lookup table. The following screenshot demonstrates one of possible results.
A fully functional 15-day trial version of the Merge Tables wizard is available here. And the 15% off coupon code I've provided for the Consolidate Worksheets Wizard works for this add-in as well :)
This is how you consolidate data and merge sheets in Excel. I hope you will find the information in this short tutorial helpful. Anyway, I thank you for reading and look forward to seeing you on this blog next week!