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

    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 Consolidate Worksheets Wizard

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:
Multiple Excel spreadsheets to be combined into one

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.

  1. Start the Consolidate Worksheets Wizard. On the Excel ribbon, go to the Ablebits tab, Merge group and click the Consolidate Worksheets button.
    Click the Consolidate Worksheets button to start the wizard.
  2. 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 Collapse Dialog icon.
    Select the ranges to merge.

    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.
  3. 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.
    Select the consolidation type.<em>
  4. 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.

    Choose one of the pasting options.

    Other options:

    • 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.
  5. 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.
    Select the destination for the merged data.

    Click the Finish button and you will have a result similar to this:
    Three Excel worksheets merged into one

Additional options for merging sheets in Excel

The Consolidate Worksheets Wizard provides 2 special options to handle the following scenarios.

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

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.
Select the 'My tables have headers' option to merge worksheets with a different order of columns.

As the result, your Excel worksheets will be merged as demonstrated in the following screenshot.
Two Excel sheets with a different order of columns are merged perfectly.

Example 2. Merge certain 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. 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.
Merging only certain columns from multiple Excel sheets.

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.
Only certain columns from multiple sheets are merged.

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.

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

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!

You may also be interested in:

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

  1. rahul says:

    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:


    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?


  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:


    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:


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

  8. novi says:


    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:


    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:


    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?


  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.


  15. Sam says:


    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.


  16. Abhishek Pant says:

    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:


    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?


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


  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!


    • 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 or

      Sorry I can't assist you better.


    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"


    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

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 -
Ultimate Suite 2018.3 Summer Offer