Consolidate data in Excel and merge multiple sheets into one worksheet

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).

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:
The source worksheets to be consolidated into a summary sheet.

To consolidate the data in a single worksheet, perform the following steps:

  1. 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.
  2. 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.
    Run the Excel Consolidate feature.

    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.
  3. 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 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.
    Click the Browse bottom to locate the workbook.

  4. 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.

    Selecting the 'Create links to source data check box' will force the consolidated data to update automatically and create outline.

    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.
    A link to the source data displays 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:

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:
Combine multiple Excel spreadsheets into one.

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:
    Combining the selected sheets into one

  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:
    Select the worksheets to merge.

    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:
    Select additional options to merge worksheets.

    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:
Combine Sheets for Excel

And the data will be combined perfectly by column headers:
The data from different sheets are combined 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!
Merge specific columns from multiple sheets.

As the result, only the data from the columns that you selected get into the summary sheet:
Only selected columns from multiple sheets are merged.

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 trial version of the Ultimate Suite is available for download here. And if you find the add-ins useful, we will gladly offer you the 15% off coupon code that we've created especially for our blog readers: AB14-BlogSpo.

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.
Merging two Excel sheets into one by the key column

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!

You may also be interested in:

51 Responses to "Consolidate data in Excel and merge multiple sheets into one worksheet"

  1. rahul says:

    Hi,
    I have 3 sheets in each three sheets in 'column A' there are common and different numbers in 20 to 30 'rows' . how do i get 'All' these no. but NOT get repeated in my another "final" sheet,
    Second, I want to get the sum of numbers in 'column I' corresponding and representing to 'columnA' in each sheet to a 'Final sheet'.

    So, for First result i tried
    =IFERROR(VLOOKUP('1st Month'!$D4,'2nd Month'!D4:D22,1,0),IFERROR(VLOOKUP('2nd Month'!$D4,'3rd Month'!D4:D23,1,0),IFERROR(VLOOKUP('1st Month'!$D4,'3rd Month'!D4:D25,1,0),"0"))) formula and not sure if this right.
    for second Result i tried =SUMPRODUCT(SUMIF(INDIRECT(""&test&"''!$D$4:$D$19"),$D4,INDIRECT(""&"'!$I$4:$I$19")), Pls. help me in getting the expected answer. Thanks!

  2. Lyndon says:

    I have 2 workbooks to compare one old one new, each with multiple worksheets. I want to combine all worksheets in each into 2 single worksheets for comparison. I then want to highlight additions and changes in the new one and the deletions in the old one. Can you reccomend a method?

  3. Sarah says:

    Hi,

    Do you know of any way in which I can use a vba code to distribute data based off an unique value from one sheet into existing separate sheets?

    Thanks,
    Sarah

  4. SRINIVAS A says:

    sir i want how to find out different tabs data get in one sheet please tell me sir

  5. KYLE says:

    Hi.

    I want the various sheets in a workbook combine (not consolidated) into 1 sheet (master sheet)

  6. Ravi Shelke says:

    Sir, I Have 5 Excel Sheet Data And i want Make All Data In A Singal Sheet For eg Daywise Data of particular Sheet with subtotal in master Sheet

  7. Val Gibson says:

    Hi,

    How can combine three different workbooks with three different worksheets into one workbook for sharing information?

  8. novi says:

    Hi,

    How can I combine 3 different sheet in one single sheet which in every sheet i have specific column that will be related to other sheet? This is the example:
    - in sheet 1, i have to take ship number and net value
    - in sheet 2, i have to take ship number and billing doc
    - in sheet 3, i have to take bill doc and total price
    I need to summarize all sheet to make a final report with that all variable ( ship number, net value, billing doc, and total price)

  9. Dipak Borase says:

    Hi,

    I have multiple sheets in single folder and want list of particular (C6, E17) from sheet 1 from every workbook.

    I need summary for that two cells in A & B column.

  10. MR Khan says:

    Thanks, it's very helpful.

  11. Jason says:

    Hi,

    Is it at all possible to merge multiple worksheets into one when the column and row lengths all differ in the individual worksheets?

  12. Jyoti says:

    In one worksheet, I have 4 columns, each 2 columns have same heading but different data, want to combine these columns in new workseet.
    Data as:
    Message qty Message Qty
    Hi. 10 Hello. 20

    Need ouput as :
    Message Qty
    Hi. 10
    Hello. 20

  13. Oliver says:

    Hi - I wish to consolidate data from multiple sheets into one sheet BUT the source data from these sheets will change (and be added to). Which of the above methods is the best to ensure that the end product is dynamically updated?

    Thanks

  14. T. says:

    Hi, I am trying to consolidate multiple sheets (with same tables) into 1 by using the Ablebits wizard.
    However as indicated here in the example, when I select consolidat worksheets, I am only able to consolidate the (numerical)data and not the text fields. So in your example there are 5 steps to complete in the workflow, but in my case it only consists of three steps where I do not have the possibility
    how I want to consolidate the data (I can only select the function to consolidate the data).
    I am using Excel 2010.

    Hopefully you can help me to figure out how I can use the consolidation function of the wizard as explained in the tutorial.

    Kr.

  15. Sam says:

    Hi,

    I am looking for a way to merge multiple worksheets into the one consolidated sheet. There is text and numbers that I need to move, no pieces of information are the same.

    I want to be able to update the individual worksheets and have it update the main sheet.

    I used to do this @ 15 years ago, but have forgotten how it is done. I thought it was a lookup formula, but after 3 hours at it, I can't seem to get it to work.

    Hope you can help.

    Sam

  16. Abhishek Pant says:

    Hi,
    I want to consolidate different city data into a master sheet but i am unable to do so because there is some columns are merge and its not working properly please help me out .

  17. Mani says:

    Hi

    I have 6 tabs, I want to combine first 4 tabs data into combined worksheet. But I do not want remaining 2 tabs data into combined tab. Is there any macro where I can mention tab names which i do not want to be touched and copy the rest of the tabs as usual?

    Thanks
    Mani

  18. Justin says:

    I'm trying to find out if there is a way to combine multiple excel files into one file but on multiple pages within that file. Is there an easy way to do this?

  19. Muhammad Ahmad says:

    I want that if an active cell in Sheet1 is A3 then all A3 cells in other sheets should be active. How can i do that?

  20. Kelly says:

    Everything here is contained in 1 workbook.
    I have multiple spreadsheels which (when printed) are forms.
    Each one has multiple cells but the format for all the forms is identical. I would like to be able to input data into each form and have it update my master spread sheet automatically. I cannot use the range option explained above because the cells are not consecutive.

  21. Varun Pandey says:

    i have Multiple sheet in 1 Workbook with some different Header name and i want to Console the all data in one sheet.

  22. Mahesh says:

    I have data with same type having 10 different worksheets. The problem is that, when merging all data i need only one row from 10 different worksheets. Means row A2 from 10 worksheets and combine to make one worksheet. second one is row A3 from 10 worksheets and combine to make one worksheet.

    • Pasha says:

      Hi Mahesh,

      The tool which can help you is called "power query". It's an add-in by default its comes in 2016 editions, but earlier versions of 2016 we have to download externally. you might wanna have to search the tutorials in youtube how to use.

  23. Monika says:

    Hello, i have 6 worksheets. All with the same header, so merge them and linked to just one consolidated worksheet is not problem.
    But what is problem, that when i update my source woksheets, there are increasing number of rows (some new customers and products are appearing in sales). How can i set that consolidated worsheet automatically will take into consideration also new rows and new range(and of course updated numbers of old rows)?

    Thanks

  24. James Afful says:

    How to use excel to compute variance(thus the differences ) for or among three figures e.g 2,000, 2,400 and 1,900 and report the result in a percentage (e.g 19%) and then compare it with a benchmark figure (e.g <=20%). Thanks

  25. Ozz says:

    Hi everyone,

    First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.

    I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.

    In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" (Overview). However there is a different column in the sheet and it's between "Nr." and "Thema" columns (which are in A1 and B1 in all the 9 Sheets) and this different column called "Kategorie" (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this "Nr. (A1), Kategorie (B1) and Thema (C1).....".So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" (Overview) sheet needs to update by itself. How can I do this?

    P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.

    I hope I explained it well. Thanks a lot in advance!

    I wish you merry Christmas and a happy new year!

    oduff

    • Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  26. SITANAGARAJOO says:

    I have previously worked in home loan as a loan processing executive. there I have found that 1st work sheet takes input 2nd worksheet process and 3rd worksheet is output .

    1st worksheet takes input when I click on finish button on 1st sheet it directly goes to 3rd sheet which is only for printout. It doesnot show 2nd sheet it directly jumps to 3rd sheet.

    I want to create one of this with a little change here, in 1st sheet i want to input or insert images along with data ( alpha and numberic data).

    can anyone guide me or help me how to do with the excel or something else.

    thanking you

  27. Joleen says:

    I have 6 sheets with each column total of 10 columns representing a location. Each column i will enter a item#. The master sheet i want to be able to enter the item# and it will pull the info from all 6 sheets for that item# and give me the location. I want to try and eliminate having to use CTL+F every single time.

  28. shiv sharma says:

    this is very needfull but i know for consolidation we need took common range of data...

  29. Julia Moore says:

    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"

  30. KAMLAKAR CHAVAN says:

    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

  31. Mohammed Shaikh says:

    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.

  32. Chatura says:

    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

  33. Joseph Madden says:

    How do I copy all 3 different reports to its own worksheet within a single excel file

  34. Austin Anderson says:

    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?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.4 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard