Sep
1

Consolidate data in Excel and merge multiple sheets into one worksheet

by | updated on

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 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 (.zip file)

You may also be interested in

Table of contents

101 comments to "Consolidate data in Excel and merge multiple sheets into one worksheet"

  2. Srinivas Gogula says:
    June 23, 2021 at 5:12 am

    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.

  3. Harry says:
    June 3, 2021 at 11:08 pm

    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?

    • Rolf says:
      September 14, 2021 at 7:46 am

      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.

  4. Nadeem ul Hasnain says:
    January 29, 2021 at 12:22 pm

    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.

  5. David T Ghogomu says:
    August 12, 2020 at 10:08 am

    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

  6. Tania Khan says:
    July 22, 2020 at 12:10 pm

    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

  7. Charles says:
    May 6, 2020 at 6:10 pm

    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.

  9. Jolene says:
    March 10, 2020 at 12:17 pm

    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?

  12. Ramesh says:
    December 10, 2019 at 3:48 pm

    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.

  13. Ann says:
    December 7, 2019 at 12:38 pm

    My husband is trying to merge two work sheets. However when he uses consolidate so information goes missing. How is this resolved?

  14. Fida says:
    November 15, 2019 at 2:43 pm

    Hi,
    I have 48 sheets in a workbook.each sheet has same cells and text values but different numeric values.
    For example,
    Sheet 1
    Date Density Temperature volume
    15.11 .789 36c 48970
    Sheet 2
    Date Density Temperature volume
    16.11 .700 46 40000
    Summery sheet (Required)
    Date Density Temperature volume
    15.11 .789 36 48970
    16.11 .700 46 40000

    I want that as I add sheet3 its required entries may shown in summery sheet on next row.and this row's data may dissapear when I delete sheet3.please guide

  15. Tiffany says:
    October 29, 2019 at 1:46 pm

    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.

  16. Kaitlin Hartjoy says:
    July 9, 2019 at 2:46 pm

    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!

  17. Kiran says:
    July 3, 2019 at 6:39 am

    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

  18. David Nelson says:
    June 6, 2019 at 7:07 pm

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

  19. Gaurav says:
    May 24, 2019 at 1:25 pm

    I'm trying to consolidate data of my team members using below macros. However, the challenge is, it pulls column names for users who do not have any data yet in their spreadsheets. I have to manually delete the column names. Is there a way that macros can skip spreadsheets which have no data.
    Sub SalesConsolidation()
    '*********************************************************************
    'This part of the code opens each file and saves it on Sales tab
    Application.ScreenUpdating = False
    Path = "C:\My Data\Clients\Analysts\"
    Filename = Dir(Path & "*.xls")
    Do While Filename ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
    Application.Goto ActiveWorkbook.Sheets("Sales").Cells(6, 5)
    ActiveWorkbook.Save
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    '**********************************************************************
    Dim bookList As Workbook
    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
    Application.ScreenUpdating = False
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("C:\My Data\Clients\Analysts\")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)

    'change "A2" with cell reference of start point for every files here
    'for example "B3:IV" to merge all files start from columns B and rows 3
    'If you're files using more than IV column, change it to the latest column
    'Also change "A" column on "A65536" to the same column as start point
    Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets("Consolidated_Sales").Activate

    'Do not change the following column. It's not the same column as above
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    bookList.Close
    Next
    Application.ScreenUpdating = True
    End Sub

  21. Mohamed Nabil says:
    May 9, 2019 at 4:12 am

    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

  22. deepal says:
    May 8, 2019 at 6:05 pm

    i have 4 sheets each sheet have 8 lake(excel capacity is 10 lake)ROWS data. i want combine in 1 excel sheet. Is it possible?
    is Yes ..then please guide me..

  25. Pk says:
    March 15, 2019 at 4:09 am

    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

    • Yeshwanth says:
      March 21, 2019 at 8:09 pm

      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

  26. Yeshwanth says:
    March 5, 2019 at 11:21 pm

    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

  28. Dean Deolitsis says:
    January 16, 2019 at 3:01 am

    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?

  30. somya lamba says:
    December 28, 2018 at 10:49 pm

    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.

  31. Anthony Brigantic says:
    November 8, 2018 at 3:30 pm

    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.

  32. Austin Anderson says:
    September 27, 2018 at 12:26 am

    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?

  34. Chatura says:
    September 5, 2018 at 4:49 am

    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

  35. Mohammed Shaikh says:
    August 25, 2018 at 11:27 am

    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.

  36. KAMLAKAR CHAVAN says:
    May 22, 2018 at 2:37 pm

    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

  37. Julia Moore says:
    April 12, 2018 at 10:20 pm

    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"

  39. Joleen says:
    February 28, 2018 at 4:06 am

    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.

  40. SITANAGARAJOO says:
    January 30, 2018 at 11:02 am

    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

  41. Ozz says:
    December 22, 2017 at 10:37 am

    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

    • Gennady Terekhov (Ablebits Team) says:
      March 6, 2018 at 3:41 pm

      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.

  42. James Afful says:
    July 19, 2017 at 3:29 pm

    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

  43. Monika says:
    June 15, 2017 at 7:50 am

    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

  44. Mahesh says:
    May 15, 2017 at 7:30 am

    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:
      June 5, 2017 at 4:48 pm

      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.

  45. Varun Pandey says:
    May 5, 2017 at 9:11 pm

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

  46. Kelly says:
    March 29, 2017 at 9:04 pm

    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.

  47. Muhammad Ahmad says:
    March 22, 2017 at 7:51 am

    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?

  48. Justin says:
    March 17, 2017 at 8:54 pm

    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?

  49. Mani says:
    March 7, 2017 at 5:16 am

    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

  50. Abhishek Pant says:
    February 3, 2017 at 10:33 am

    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 .

  51. Sam says:
    January 11, 2017 at 7:16 am

    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

  52. T. says:
    December 30, 2016 at 11:17 am

    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.

  53. Oliver says:
    December 10, 2016 at 10:00 am

    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

  54. Jyoti says:
    November 16, 2016 at 10:10 am

    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

  55. Jason says:
    October 30, 2016 at 11:35 pm

    Hi,

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

  57. Dipak Borase says:
    October 7, 2016 at 8:33 am

    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.

  58. novi says:
    October 2, 2016 at 8:00 am

    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)

  59. Val Gibson says:
    August 12, 2016 at 8:49 am

    Hi,

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

  60. Ravi Shelke says:
    July 18, 2016 at 9:33 am

    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

  63. Sarah says:
    April 29, 2016 at 3:30 pm

    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

  64. Lyndon says:
    April 15, 2016 at 12:14 am

    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?

  65. rahul says:
    February 6, 2016 at 2:11 pm

    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!

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