by 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, etc.) and merging sheets (i.e. copying data from multiple worksheets into one).
Consolidate data from multiple worksheets in a single worksheet
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 worksheet 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 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.
How to merge Excel sheets into one
Overall, there are four ways to merge Excel worksheets into one without copying and pasting:
- Combine sheets with Ultimate Suite
- VB scripts
- Power Query for Excel
- Merge two Excel sheets into one by the key column(s)
How to combine Excel spreadsheets with Ultimate Suite
The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel.
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.
- 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:
- 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.
- 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.
Other ways to combine sheets in Excel
Apart from the Copy Sheets wizard, the Ultimate Suite for Excel provides a few more merging tools to handle more specific scenarios.
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.
Merge sheets in Excel using VBA code
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.
Combine data from multiple worksheets with Power Query
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).
How to merge two Excel sheets into one by the key column(s)
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.
The Merge Tables wizard is also included with the Ultimate Suite for Excel.
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!
Available downloads
Ultimate Suite 14-day fully-functional version (.exe file)
106 comments
My husband is trying to merge two work sheets. However when he uses consolidate so information goes missing. How is this resolved?
Good Morning,
I have two excel workbooks that I am trying to merge together. One is an old one, one is a new one. Both have multiple tabs. Is it possible to merge the data so that it goes into each tab? For instance:
Tab1 (Old) is named: Bay Valley Foods
Tab1 (new) is named: Bay Valley Foods
Tab2 (Old) is named: Ralcorp
Tab2 (New) is named: Ralcorp
I am trying to combine Tab1 (new) into Tab1(old) and Tab2 (new) into Tab2 (Old).
As this is a work computer, I am also not allowed to download anything including the merge table wizard. So I would need to know if this is possible without that.
Hi,
I'd like to automate the process of merging data from two separate sheets. Is there a macro or a VBA script that will allow me to use the Merge Two Tables option to quickly merge data? When I try to record a macro for the Merge Two Tables feature it just shows up blank. Thank you!
my question is how to combine multiple column data into the single cell? below given data is from multiple columns and i am trying to consolidate all in single cell in sheet2.
For Example:
Description "From Sheet1" - from sheet1
[SNOW:XXXXXXXX (From Shee1) SOLMAN:XXXXX (From Sheet1)]
Please advise
trying to combine 14 sheets together....only 8 columns each, only 40 rows each. Only combines 13 together. No matter which one i start with it always leaves one out????
in Combine multiple worksheets into one with Copy Sheets if i a have the same range for all my work sheets can i apply it for them all in the same time or do i have to reselect it for every work sheet
Hi
How can you do these on Google sheets?
Hi Melody,
Please have a look at the way our Combine Sheets for Google Sheets works. It is designed to bring data from multiple sheets into one (considering all headers your tables may have, if necessary). Perhaps, you'll be able to make use of it.
Hi, is it possible to add each sheet name into the consolidated Sheet?
I have a requirement. I have data in sheet 1, Sheet 2 and sheet 3 in a excel. I have around 100 records in each sheets. I want to combine all this data in another sheet, say sheet 4. The order of the record in sheet 4 should be like 1 record from sheet 1, next record should be from sheet 2 and next should be fro sheet 3 and again go to sheet 1 and so on... Note all the column names are the same in all the sheet. Any short cut techniques other than copy paste
Once you install the plugin, then under the plugin/addon use "copy sheets --> Data from the selected sheets --> select row you want to copy from multiple sheets" then set your preferences to create the new sheet(in your case sheet4). It should work, but you have to give the row range for all the sheets not sure if there any program to run this method. Let me know how it goes, even I'm working to get the data in similar way, will share you if I have anything
Hi,
I'm using your add-in for the first time. Thanks for it, save lot of time.
But I have an issue when using it, for the first time when I try to consolidate the data(specific row range) from multiple sheets(6 sheets) into one master sheet it worked. But for the second time it won't allow me to specify the data range, is it not letting me because I use trial version? let me know
Thanks,
Yeshwanth
How can i Marge all worksheet into a single worksheet within the workbook..
Headers are same
Hi Paramveer,
If my understanding of the task is correct, you can use our Combine Sheets tool for this. Here's is a step-by-step example: How to combine Excel worksheets by column headers
Say you have an employee name list with salaries as well. Some employees have now received a raise and their updated salaries are in a new table along with their names. How would I merge the new salaries to replace the old ones?
Hello, Dean,
Thank you for your question.
If we understand your task correctly, please have a look at our Merge Tables Wizard. It allows you to update the data in the main table with the data from the lookup table based on the key column(s).
Hi,
I need to creates a macro that would merge data in two sheets to one. The number of columns and the column headers are the same.
The only catch here is every time we run the macro we will have different number of rows in each of these sheets so we cannot hard code the row numbers in to the VBA code as it will keep changing. I want a general Macro which would paste the data of one sheet after the other sheet into a new sheet.
I have 7 worksheets.
Each worksheet has several matching fields but they also have several different fields.
IS there a way to merge them so the similar fields are in the correct column and the different fields are in their own column?
The different fields will have blank cells were the data doesn't pertain. Also the number of rows increases each day in all worksheets.
I have 14 different spreadsheets and each spreadsheet has some of the same data but they also have different data for some of the columns and rows. For instance one sheet will go to column N while another one will go to column AB. I want to create a master spreadsheet that combines the data of all 14 spreadsheets into one. We support different groups and the main objective is to try and see what groups have what. It will be a very big workbook. Does the consolidate data tool work for this type of functionality?
How do I copy all 3 different reports to its own worksheet within a single excel file
Hello, Joseph.
Thank you for contacting us and for your question.
Please have a look at our Combine Worksheets Wizard for Excel. It allows you to combine data from multiple sheets into one at a glance.
Sub MergeSheet()
'Declaring the Variables
Dim LastRow, ShtCnt As Integer
Dim ShtName As String
Dim NewSht As Worksheet
'Assinging a Sheet Name by UserInput
ShtName:
ShtName = InputBox("Enter the Sheet Name you want to create", "Merge Sheet", "Master Sheet")
'Count of Total Worksheet in the present workbook
ShtCnt = Sheets.Count
'Using For Loop check if the worksheet exists
For i = 1 To ShtCnt
If Sheets(i).Name = ShtName Then
MsgBox "Sheet already Exists", , "Merge Sheet"
GoTo ShtName
End If
Next i
'Create a New Sheet
Worksheets.Add.Name = ShtName
'Assigning NewSht as Current Sheet
Set NewSht = ActiveSheet
'Moving Worksheet to the beginning of this workbook
NewSht.Move before:=Worksheets(1)
'Copying all the data to the New Sheet Using For Loop
For i = 2 To ShtCnt + 1
'If i=2 Then copy all the data from the second sheet including header.
If i = 2 Then
Sheets(i).UsedRange.Copy NewSht.Cells(1, 1)
Else
'If i is grater than 2 then copy all the data excluding Header(1st Row).
Sheets(i).UsedRange.Offset(1, 0).Resize(Sheets(i).UsedRange.Rows.Count - 1, Sheets(i).UsedRange.Columns.Count).Copy NewSht.Cells(LastRow + 1, 1)
End If
LastRow = NewSht.Cells.SpecialCells(xlCellTypeLastCell).Row
Next i
'Displaying the Message after copying data successfully
MsgBox "Data has been copied to " & ShtName, , "Merge Sheet"
End Sub
hi
thank you a lot
kiss you
LOVE U
Thank you so much!!! Worked perfectly.
ya hoo Great
Done Thanks
I have 50 excell sheet. With same structure and column. I want to combine them all into one sheet. It should simply copy sheet one after other using vba.
Hello, Mohammed.
If we understand your task correctly, our Combine Worksheets Wizard can help you combine multiple sheets into one without copying and pasting your data.
Hello Sir/Madam,
Kindly help me to fetch/merge data from various sheets {input from various location to same sheet(Left column and top row header are same)} data in to one sheet. For example I have a sheet having 15 columns and 2000 rows data for 15 locations. Each location entered their data into 15 columns. Now I want to merge all the data into my main sheet (15 columns and 2000 rows). How I can do it fast without copying individually. Please give me solution.
Thanking you,
Kamlakar Chavan
I want to take work sheet from several different files and combine them , but when I Browse and get the file and try to add it "states consolidation reference not valid"