Sep
1

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

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!

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

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!
Ultimate Suite for Excel Professionals
 
 
50+ 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