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

Available downloads

Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

106 comments

  1. I just found this site for Excel and I am impressed. I do have a particular demanding task to perform and not only once but relatively often, more than once a month.

    I used to deal with hundreds of CSV file and since i need them as one large file, I used the old DOS command: Copy *.csv Compil.csv
    Now I am receiving those files as full excel *.xlsx
    In a worksheet labelled Analyse AA, the rows are composed of headers and assay results in multiple colums and also contains a second worksheet with QAQC data that is not a priority for the moment.

    My only method was to use a macro to select the data in the rows I needed and copy/paste them into a compilation master file.
    There is not hundreds, but thousands files to merge in this project. Browsing for a more efficient methods, I found the VBA macro MergeExcelFiles but this is adding worksheet instead of appending in one worsheet.
    I did find a promising title: Merge sheets in Excel using VBA code but the suggested VBA example is not accessble anymore. It was removed.

    Any suggestions to help me save time? Does the Ultimate Suite for Excel would help?

    Best regards

    • Hi! If I understand your task correctly, our Combine Sheets Wizard may help you solve it in a few clicks. Merge data from multiple worksheets into a single Excel sheet based on column headers using the Combine Sheets Wizard. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      If you have any other questions, please don’t hesitate to ask.

  2. Alex,
    Good afternoon,

    You are the only person I have found that can explain excel challenges easily - thank you.

    I have been searching for a simple solution to merge (and automatically update) different sheets.

    Specifically - I have a shared "master calendar" spreadsheet which we use to list all of our tasks for our Navy Program. With that, all of the team leads have a individual sheet (all with the same column heads) which they update and I would like to merge them into a separate (master calendar sheet - same workbook).
    Is there a way to do this in Excel.

    Thanks again - appreciate the help.
    Have a great day.
    Best regards
    Eric

    • Hi! I hope you have studied the recommendations in the tutorial above. I recommend paying attention to the Copy Sheets tool. A single tool, four different methods for merging multiple Excel worksheets. All you have to do is specify how you want to combine the sheets. Copy Sheets add-in does the rest. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

      • Thank you very much - I will see if our administrator allows for us to download that Suite - it would help out a ton.

        Have a great day

  3. Hi Alex! You're my new favorite person I have ever found on the internet!

    Disclaimer: I am brand spanking new to Excel - as in, I started fooling with it for the first time about 8 hours ago. I am starting small with my idea for data tracking and only measuring two employees workload.

    I currently have 5 sheets I am working with to consolidate data gathered by 2 people.
    The first sheet for both people includes an array of unique data. Each row is dedicated to one sales order, and the 10-20 columns are for different factors related to that order.

    Currently, I only wish to sum / count 6 columns from each base sheet. The problem I am facing is that the data I am looking to measure for these columns is only input as a date. I'm trying to move that info over from the 2 base sheets to 2 summary sheets, and they are the bane of my existence. The summary sheet rows are dates in ascending order, and the columns are headers from the base sheet - thus the cells show the sum of each heading category per day. This will then be consolidated into a final summary sheet (but I have found that much easier as it is a simple sum formula with no unique values)

    I have tried using =COUNTIF(Sheet1:Sheet2!A1:A50,"01/01/2024")

    This does populate the correct numerical value. HOWEVER, since the quotations indicate a unique value / text, Excel is unable to recognize and automate the pattern. There has to be a better way than me individually inputting the above formula for each of the 6 cells for each corresponding date. That has me manually entering the formula 6x365(2) times.

    Please help. I know there's a solution. I just have no idea how to loop it in!

    Thank you in advance.

    • Hi! If I understand the question correctly, you can use a reference to the cell where the date is written in the formula instead of specifying the date explicitly. Use in the formula a reference to the column of the summary sheet in which the dates are written in ascending order. When you copy the formula down the column, the reference to the cell with the date will change. Read more: How to copy formula in Excel: down a column, without changing references. It is my hope that my advice will be of help to you in your task.

  4. Hi Alexander, I am looking to use two separate worksheets. I want to pull in executive contact information from one worksheet and match it with a field like company name or an ID number for a separate worksheet that has quality scoring metrics for those companies. The ultimate goal is merging the contact data with the companies that had poor quality scoring to create a target sales list.
    It sounds like the Merge tables wizard will do this? Thanks , Kevin

  5. Awsome loved it.

  6. I need to combine names and addresses for a mailing list. How do I do that?

  7. I’ve read the above methods and still not sure there is a way to do what I want.
    Let’s say I have three sheets of data that all combine the exact credit card transaction info from different cards (columns/rows same). Is there a way to consolidate the data from the three sheets into a single sheet with the same columns/rows in order to run reports on all the combined credit card data. If I update data in any of the three separate sheets, can the new data automatically be added into the “consolidated” sheet without running some sort of commmand?
    Thanks

  8. someon can help me how to combine the sheets from one to another with different rows and column?

  9. I manage product registration for cosmetic in 8 countries. Each cosmetic products has a unique number identifier. I keep all IRCs ai nd countries in 1 sheet. How do i avoid creating duplicate entry for the same product in the same country? If it is for just 1 country, I use the condition function on the tool bar to highlight duplicate values but when in 1 excel, the duplicate value for the cosmetic product might be due to 1 in say Australia and the other in New Zealand but what I want to call out is to avoid the cosmetic to appear twice in australia. please help.

  10. I would like to compile multiple data from excel tabs into a table in 1 master worksheet

  11. i have sales summary of january to december on different sheets how can i put them on a sheet

      • I have tried this method but only the numbers are consolidating. But the alphabets are not getting consolidate.

        • Hi!
          Consolidation is the sum of numbers from multiple sheets to get a total result. You cannot summarize text. What exactly did you want to do?

  12. Hi Svetlana Cheusheva ,

    Your article helped me save many time-consuming processes of consolidating the multiple sheets into a single sheet in the same workbook using the VBA Method.

    I am a finance graduate and I sometimes have difficulty with excel formulae whenever I face something difficult in excel I Check your article if there any post you have made relevant to that and that's it my issue will be fixed if I have any post about it and I also study excel formulae using your articles which makes me a better understanding of everything just like index and match, Randomfunction which I used to prepare my schedule to study randomly list goes on.

    Thank you so much for your work and passion and have a nice day.

  13. When combining Excel spreadsheets, you want the source of the worksheet displayed in a column (e.g. Products1, Products2, Products3) so that you know where each line of the data in the combined worksheet is originated from. I couldn't figure out how to do it. Can the tool handle this basic need?

    • You could do this with PowerQuery.

      As a general approach you would start by importing your tab "Products1" into the PowerQuery Editor. Then you'd create a new "Customised column" with e.g. the column title "Origin" and a fix value of "Prod_1".

      You repeat the same steps with Products2, Products3 etc. When you then merge your separate tables in PowerQuery, you'll end up with a merged column "Origin" which contains the information of where a given lines originates from.

  14. Hello Dear
    I have converted PDF file into Excel but 426 sheets are created in converted excel file. The data in all sheets is unique. How can I merge all the data in a single sheet.

    • Hello!
      I'd recommend you to have a look at our Ablebita Data - Copy Sheets tool that can help you to merge all the data in a single sheet.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  15. Hi,
    I have downloaded the Ultimate suite but an error occurred trying to install it on my laptop as below:
    The installer was interrupted before ablebits Ultimate suite for Microsoft Excel, personal edition could be installed. You need to restart the installer try again.
    Click "close" to exit.

    Looking forward to your earliest reply.
    Kind regards
    David

    • Hello David,

      Thank you for contacting us. Sorry to hear that you are having difficulties with the installation of our product. Can you please specify on which step of the Installation wizard the error occurs and send us its screenshot to support@ablebits.com? We'll do our best to help you.

  16. Hey,
    So we have 2 excel sheets - one has Customer Details and the second has Sales details.
    if i have a common column in the 2 sheets - Customer ID - the two sheets have different columns but have 2 matching columns - customer ID and Name )
    How would I go about merging the 2 sheets?
    Regards,
    T

  17. Is there a way create a single workbook by appending 160 similar single-page workbooks and have each of the appended workbooks be on a different sheet. In other words, all 160 appended workbooks would exist in a single workbook with 160 sheets.

  18. Hi, I would like to compile multiple data from multiple excel tabs into a table in 1 master worksheet. Is it possible to do that?

  19. Hi, is it possible to add each sheet name into the consolidated Sheet?

  20. Table 1:
    BOOKING ID CHECK IN CHECK OUT NO OF ROOMS Tariff
    AGVK4701 10/19/2019 10/20/2019 3 7674
    AIUV9058 10/27/2019 10/28/2019 1 1427
    BQMZ7562 10/20/2019 10/21/2019 1 1228
    BZWG1548 10/16/2019 10/19/2019 2 8272
    CRGT0534 10/19/2019 10/20/2019 1 2155
    CRWJ9574 10/24/2019 10/25/2019 1 892

    MAster Data
    Booking ID Guest Reconciliation status Booking Status
    ABJC1972 Mr C Santhosh Completed Checked Out
    ABTR8361 kuna Anveshkumar Completed Checked Out
    ACVM0475 Brahmaiah Manam Completed Checked Out
    ACVS5914 Prasanth Completed Checked Out
    ADWG1024 Parul Shankhala Completed Checked Out
    AENP3549 Parul Shankhala Completed Checked Out
    AFEK2374 rakesh Completed Checked Out
    AFPN6754 N Completed Checked Out

    I am looking the ID of the First work sheet with that of MAster sheet using the following formula =VLOOKUP(A2,'Booking Summary HYD770 Sorted'!$A$2:$A$1126,1,FALSE) in one of the column in Table 1. However it is finding the ID's that are available in various rows. However along with the ID I want a particular column from the Master sheet to be copied into Table 1 and in place of '1' I have given 29 as the cell data I want to retrieve before FALSE parameter in the above formula it is giving REF error. How to get it.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)