big hungry geek

Merge Worksheets in Excel

It’s no secret that I love Excel. I’ve said it before, I’ll say it now and I’m sure I’ll hum this tune again next week.

Today I’m tackling a common problem that’s plagued office heroes for years. How do you merge multiple Excel worksheets into one master worksheet, automatically, and without macros or VBA? Down the line I’ll discuss other methods to merge and consolidate data in Excel, but today I’m focusing on this particular situation: you have more than one worksheet, all formatted with the same columns and rows, but each worksheet contains different data that you want to pull together and summarize.

Lucky for you, Excel has a neat little Consolidate feature that’s powerful, flexible and very easy to use. Best of all, it requires no formulas, macros or VBA code. Here are some typical cases where this feature comes in handy:

  • Roll up expenses from multiple offices, branches, employees, children, etc.
  • Roll up sales from multiple customers, products, months, quarters, years, and so on.
  • Merge worksheets that were previously distributed among colleagues or other groups of people for updating back into one master worksheet.

The file I’m working with, which you can download here or at the end of this post, is for a fake used car dealership that sells the Honda Accord, Honda Civic, Toyota Corolla, Toyota Matrix and Toyota Camry. There are 30 employees and three separate worksheets (one for January, February and March), each containing the total cars sold that month per employee, per car.

Let’s get started.

How It’s Done

  1. Ensure that the worksheets you intend to merge all have the same columns and rows in the same order.
    1. For example, notice that the January, February, and March worksheets all have the same columns and rows in the same layout.
    2. The only difference between the worksheets to merge is the data contained within these columns and rows.

      Same Layout Required for Merge
      All worksheets to merge should have the same layout.
  2. Create the Merged Master worksheet. This worksheet should look identical to the other worksheets (same columns, rows and layout) except the space to contain the mergeddatashould be blank.
    1. Right click on the January worksheet and select Move or Copy…
    2. Double-click on the newly created January (2) worksheet, type “Merged Master” and press Enter.
    3. Select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35). Press Delete.

      Blank Merged Master
      The newly created “Merged Master” worksheet should have the same layout, but without any data.
  3. Select cell B5 again. Navigate to Data → Consolidate.
  4. In the Consolidate dialog box that appears, under Function, choose the summary function thatyou want Excel to use to consolidate your data.
    1. We’ll leave the default, Sum, since we’re looking to sum the sales from January, February and March, but I suggest trying out different functions (Count, Average, Max, Min, etc.) to see what they do.
  5. Under Reference click the small Select Range Button button, then select the January worksheet, select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35) and press Enter.
    1. You’ll see the range you just selected now appears in the Reference box. Click Add to include this range in the All References box.
  6. Click the small Select Range Button button again. This time when you click the February worksheet you’ll notice the same range appears in the dialog box, except it’s been updated to February instead of January. Press Enter, then click Add.
  7. Repeat step six for March. Your Consolidate dialog box should now look like this:Consolidate Dialog Box
  8. When you click OK Excel will complete the merge, and the Merged Master worksheet will now contain the consolidated first quarter sales for each employee. Viola!

    Consolidated Master
    Excel automatically summed the data from all three worksheets, sans formulas or VBA.

A Few Notes

  1. If you select the Create links to source data option, Excel will automatically update the newly consolidated data on the Merged Master worksheet whenever you update any of the source values from the other worksheets.
  2. If the data you want to consolidate is contained in separate workbooks, you can simply select the Browse… button to find that workbook, then click OK to close the Browse dialog box. Excel automatically includes the file path in the Reference box followed by an exclamation point.
  3. It’s important to remember that the space to contain the merged data should be blank, since Excel will automatically overwrite anything it finds in that space when it completes the merge.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Merge Multiple Worksheets

Ryan

, , ,

69 responses to “Merge Worksheets in Excel”

  1. Hi! This is great, I was wondering if you could help me with a slightly different situation. I have 3 worksheets, one refers to spare parts sales, another to warranties and the last one to repairs,. Each worksheet has different data about said categories, however, I have the same numbering for all three items and they all have the respective date and status. I would like to make a “master” worksheet where I have the reference number, date and status for all of them (sales, warranties and repairs) as to always be able to see the status for everything. Is it possible to do this and for the “master” worksheet to update itself each time I enter a service? Thanks

    Reply

  4. I have a workbook with each sheet being the timetable of an airline and there are 123 of them. The fields in the timetable are same for all the sheets. The sheet names are by the 3-lettered airline IATA code. I want to get the records from all the sheets into one sheet.
    Will Consolidate achieve that or something else?

    Reply

    • Possibly, as long as each worksheet has the same columns and rows, and as long as the data from each worksheet can be aggregated in some way (i.e. sum, count, average, product, etc.), then yes you should be able to merge the records into one master sheet.

      Have you tried following the instructions presented in the post?

      Reply

      • I have the same issue as indigene. I need to consolidate or merge several worksheets, but none of them need to be aggregated. Each worksheet has manufacture, ship and install dates for various vendors. I would like to combine them into one master so that I don’t need to move between them in order to schedule work crews.

        Reply

    • If all sheets are in one workbook, put workbook into a folder, e.g. C:\DATA and use MSQRY32 and as many UNION ALL statements (less one) as there are sheets.

      XLODBC
      1
      DBQ=C:\DATA\Workbook.xlsx;DefaultDir=C:\DATA;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm,
      *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
      SELECT * FROM Sheet1 UNION ALL SELECT * FROM Sheet2 UNION ALL SELECT * FROM Sheet3 UNION ALL SELECT * FROM Sheet4

      UNION ALL SELECT * FROM Sheet5 UNION ALL SELECT * FROM Sheet6 UNION ALL SELECT * FROM Sheet7 UNION ALL ……. etc.

      Reply

  5. This is a great help with merging worksheets that have identical columns and rows. I have a different problem that includes several (very large) sets of data. I can make the columns the same but I am trying to match rows. The data is x,y,z data for mapping surfaces in 3D space. The issue is that one surface has more x’s and y’s than the other surface. I am trying to join the two lists because Excel won’t play well with many more than a million rows. The first data list has (x1, y1, z1A), (x2, y2, z2A), (x3,y3, z3A) and on and on. The second data list has (x1, y1, z1B), (x3,y3, z3B) and on and on. What I need is one list that has (x1, y1, z1A, z1B), (x2, y2, z2A), (x3,y3, z3A,z3B), etc. Note that there is no Z2B value at x2, y2 because there was no x2, y2 value in that list.

    Do you have any suggestions on how to get these two lists merged together? Many thanks for your thoughts

    Reply

    • There may be several ways to accomplish this, but I need a little more information first:

      1. Is each value (e.g. x1) in its own cell, or are all three in one cell?

      If each value is in its own cell, you could add a fourth column called “From B” and use Excel’s VLOOKUP function to find the correct value from List B, using the X value from List A as the lookup value. Does that make sense?

      Reply

      • Ryan, your clue set me off in the right direction. This is a seismic survey (x,y locations) with surface picks (z1 through z7). For each z there is a unique list of xs and ys. That is at each xy combo, there is just one value of z1, z2, etc. What I needed to do was to sort each list with a two level sort on x and then y. Then I concatenated those two columns and used vlookups to populate each of the z columns from each of the seven files. Many thanks for your help.

        Reply

  6. I Want to combine two sheets from different workbook into one workbook but asa a different sheets …can you tell me how to do it.

    Reply

    • Ashwini,

      If I’m understanding you correctly, you can just use Excel’s Move or Copy... feature. Right click on the worksheet tab (e.g. Sheet1) and select Move or Copy.... In the dialog box that appears, make sure “To book:” shows the name of the workbook where you want to move to, select “Create a copy,” and click OK. The other workbook should now have both worksheets.

      Reply

  7. I’m trying to figure out the same problem as Brantly above. I’m trying to combine two mailing lists. The columns are the same, but the rows are different. They are supposed to be the same data, but they’re not – so I need to find the rows that aren’t.

    Reply

  8. I’m trying to combine 5 different worksheets into one, master worksheet for artists and galleries. However, the data that I have in the rows and columns is not identical in each separate worksheet. Is there any way I can consolidate this without losing all the data I have found? I also need it to be able to sort through it and update it accordingly. Thanks in advance for your help.

    Reply

        • Ryan,

          No worries, I figured you were busy. If you could just give me an example to get me started that would be great. Do you think a H lookup would work?

          Reply

          • Hi Sherry, I just replied to your email and attached your worksheet updated with an example of how you might use VLOOKUP to accomplish what you’re after. Hope that helps!

  10. I am looking to merge customer data from multiple databases into one, eliminating duplicates by email address. Any ideas?

    Reply

  11. Thanks! I’m Upul It was helpful for me to Amalgamation. I want to amalgamate automatically witch is separate multiple excel files save same folder

    Reply

  12. Hey Ryan this is really awesome! It’s very useful to know and will assist me greatly. However for the current task at hand I would like to merge data typed in text across several worksheets. Is there a way to do that?

    Reply

    • Thanks, glad you find it useful!

      I’m sure there is a way to merge your data, but I’ll need a little more information first. What kind of data are you looking to merge (i.e., numeric, alphanumeric, etc.), and what are you hoping to achieve by merging (i.e., summing numbers, etc.). If you want, you can always email me an example spreadsheet to [email protected].

      Reply

  13. Hi Ryan

    Thanks for all your amazing contributions here! Really useful. I wonder if you could think of any solutions to a problem I’m having, which I thought the “consolidate” command could fix, but hasn’t?

    I have data pertaining to about 50 different magazines that my company publishes – on-sale date, cover price, issue number, barcode etc. I have one worksheet on my document for each different magazine, but I want to have a master worksheet that pulls in all the data on those sheets into one place, while also preserving the links so that if I change one of the data points for one particular magazine, the master sheet updates too.

    The consolidate function doesn’t quite do this – but I wonder if there was a function that did?

    All help really gratefully received!

    Thanks

    Sam.

    Reply

    • Hi Sam,

      Ideally you would store your data for all 50 magazines in one structured Excel table, which could then be easily sorted or used for a simple PivotTable. The only change you’d have to make to your current format is to add one additional column for “Magazine.”

      If you don’t want to take this route, you could also create a PivotTable using multiple consolidation ranges (i.e., multiple sets of data across multiple worksheets). Each worksheet would have to be setup the same (same number of columns, same column headings, etc.), with the only differences between the worksheets being the actual data, or the number of rows of data. Press ALT+D, then press P to bring up the PivotTable and PivotChart Wizard, select Multiple consolidation ranges, and follow the remaining steps to select and add your data ranges across the multiple worksheets.

      Feel free to email me your worksheet (or a sample if you’d rather not share your data) and I’d be happy to show you what I mean.

      Reply

  14. Hello,

    I am trying to have a ‘master” for 4 different sales reps, the column names are the same for each workbook. But, there will be different information in each worksheet. What I would like to accomplish is that data entered in any one of the worksheets be added to the NEXT blank line automatically.Without me having to manually copy/paste them into the ‘master book’. Please help!

    Reply

  15. I have no clue.? It would be so awesome if you could ‘jump into my computer” LOL! I do not need a chart. Just a list on the master file.

    Reply

  17. Hi Ryan, this is great. Is there a way to make this work if, using your example, some of the sales people changed from month to month? We’re struggling with a very similar case, and the easiest way to explain it would be if e.g. using your example, “Kent Upchurch” and “Mina Ihle” quit in Feb, and “Bob Jones” was hired in Feb, and then in March, “Zack Smith” and “Linda Ace” was hired. So we’d like to have the consolidated page still have Kent and Mina’s info, since they did have sales, and also automatically add Bob, Zack, and Linda?

    Reply

    • Hi Ed,

      Here’s one approach you could take.

      1. Add three new rows to the Merged Master, January, February, and March worksheets underneath “Von Fitzgerald.”
      2. In February, on row 35, add “Bob Jones,” along with his sales numbers for the month.
      3. In March, zero out the sales figures for both “Kent Upchurch” and “Mina Ihle,” but leave their names in the column.
      4. In March, on row 36 and 37, add “Zack Smith” and “Linda Ace,” respectively, along with their sales numbers for the month.
      5. On the Merged Master worksheet, select cells A5 through G38, right-click and select “Clear Contents.”
      6. With the range still selected, navigate to Data -> Consolidate..., add the ranges “January!$A$5:$G$38,” “February!$A$5:$G$38,” and “March!$A$5:$G$38,” and select Left column under “Use labels in,” then select OK.

      I updated the spreadsheet here, so you can download and take a look to see what I mean. Hope this helps!

      Reply

      • Hi Ryan, awesome. Thanks for the reply! Unfortunately my issue is a bit more complex than I led on. I thought it was similar enough to your example but actually it’s not quite. Imagine that I have a spreadsheet which is a list of items that a customer ordered for shipment in July. So that spreadsheet would have a column for Item #, Item Name, and a Qty.

        Then I have another spreadsheet for shipment in Aug. It’d have the same columns, but might have *some* duplicate items as July and some new items.

        What I’m trying to do is “consolidate” the two lists so I have one “Summary” list of *all* items ordered for both months, but as with your example, I want to see separately the qtys ordered in July, the qtys ordered in Aug, and then a total.

        Right now what I’m having to do is copy-n-paste both months into one sheet, side by side and sorted by item #, then I *manually* adjust the rows so the dups line up and the unique items have their own rows. Then I do more manual cutting-n-pasting till I have my Summary.

        So it seems like this doesn’t quite work with your method? It seems like with your method I’d actually have to know what’s unique ahead of time. But I don’t know that until I go through my manual process of lining things up first. Do you know what I mean?

        Reply

    • Hi there,

      Just make sure that your master table, along with all of the tables you’re trying to merge, have the same number of rows to accommodate all of the data. That may mean you’ll have to insert some blank rows. Also keep in mind that your row labels down column A all need to be in the same order across your different worksheets.

      Reply

  19. I have a spreadsheet with 20+ Tabs with 5 lines of info on them, all same columns but i want to copy these to all one tab so i can do a vlook up on the info, is there quick and easy way of doing this.. any help appreciated…

    Reply

  20. Hello Ryan,
    I have spreasheets of clients pulled from various databases. Some have the same unique identifiers while others don’t. But they all have the same clients along with new listed in their respective sheets as well as various other fields. None of the spreadhseets have same rows and columns. How can i consolidate these spreadsheets into one master sheet. To give you an idea on type of data im working with: I have to process a claim for a client who can be known to social services, local school, police, child care service etc. All these services provide me with their list of clients. I then need to check for this person on all their respective spreadheets and check whether they fulfill certain criteria to be accepted for a claim. I would be great if i can identify the same client from all the sheets and then link them into one master sheet(taking out any duplicates) where i can then check against all criteria’s to be accepted for aclaim. Any help greatly appreciated.

    Reply

    • Hi Ryan, I have a very similar issue as above. I am working for an organization that just had 4 smaller organizations merge into this one. Each had their own separate mailing list. Someone copy and pasted all the mailing lists into one excel file (there are at least 7000 entries) and all of the sheets had identical rows/columns so the data all lines up in the correct column, but for some reason some of the data from certain worksheets will not integrate into the whole worksheet when I try to sort the entire list by one category (i.e. sort by address so I can delete any duplicates of constituents listed). How can I get it so all of the data integrates together?

      Reply

  21. There is a work around to this if you have adobe pro.
    – Open Excel document
    – right click the first worksheet on the bottom and click “select all sheets”
    – click the MS OFFICE logo on the top left and click print.
    – change the printer to adobe pdf. Make sure the print range selection is set to “all”.
    – click print and select your file destination in pop up window.
    – adobe will do its converting and put all the worksheets together. Done!

    Reply

  22. This was really a big help on solving some of my excel dilemmas, but I was wondering if you could help me on this one. I have GROUP/ARTIST, ALBUM, DESCRIPTION, INCLUSIONS and PRICE on ten of my sheets and I want all of these to be put on one master list. This master list, however must have two more columns left untouched, the MONTHORDERED and DATERECEIVED, which can be edited or modified when needed. All of the sheets have been labeled as to the group names with these five as headers. This blog could have solved the problem, doing a little editing on the formats- but artists make albums all the time, so I would have to add in new data when they do, and these data must be added in to the master list automatically so I would not have to repeat the copy-paste process all the time. Please do e-mail me if you could help me out. Thanks in advance!

    Reply

  23. Hi. I am trying to combine data from 7 different worksheets into one master worksheet. As an example, I want the data from worksheet # 2 to drop in under the data from worksheet #1 etc… I’m not looking to summarize my data. I just want it all in one master work sheet. When I try copying and pasting, I get a message that the data I’m trying to past is not the same size. All worksheets are unmerged and identical in format. Help!

    Reply

  24. Hi, I have a question….Say your excel file has months at the bottom….one worksheet for each month. Is there a way to create a “year” tab that you can dump all of your month tabs into? So for instance, I have 12 separate worksheets for 2014, each labeled Jan, Feb, Mar, etc, and I want to have those 12 under a tab called 2014. But I want to click on the “2014” tab and have each “month” tab open up as normal….is this possible??

    Reply

  25. I’m using Excel 2010 — I’ve used consolidation before but in a different version of Excel but it’s been a few years back.
    Data is contained in separate workbooks located in the same folder as my master workbook on the c drive. In testing consolidation I get this error message when selecting “add” and after selecting the file under “reference” – “consolidation reference is not valid” !! Why? and do you have any information that can help me correct this?

    Reply

  26. Hello Ryan – I realize this post is getting dated, but I have the same issue as Naty, Indigene, andSherry above – specifically, I would like to create a list on a summary sheet of payments made to vendors that are recorded on separate worksheets. Would you mind posting the solutions you came up with for them when they sent you the samples? I will also email you my sample. Thanks!

    Reply

  27. Similar to the scenario Judy Friedman described above, I have separate tabs in a workbook that different individuals will be maintaining. I would like to have the data they enter on their individual worksheets roll up into one master worksheet automatically without having to copy/paste from each one into the master. Is this doable?

    Reply

  28. I have around 50 different worksheets within one workbook, all with the same headers that start in B4 and go through D4. My data starts in B5, goes through D5 and each worksheet has a different amount of records. How do I get all of the data between B5 and D5 in each worksheet into one Master worksheet?

    Reply

  29. I have one master worsheet that has 4 spreadheets under different tabs.
    Now I am getting 6 different people send in their inputs for their customers (mostly text/comment in the cells) using this same idential spread sheet.
    How do I consolidate all these text/comments from these various spreadsheets into this one master.
    I have used the Consolidate tool and love it for numbers/figures but this is all text and has those docs under different tabs.
    What is the best Excel feature to use to do this.

    Reply

  30. Hi Ryan, It’s very useful. I have the data to merge text across several worksheets. Is there any way to do that?

    Reply

  32. Can you use the consolidate feature to consolidate text that is different in each of the tables you are trying to consolidate on to one worksheet from multiple or do they have to be #, date values?

    Reply

  34. Hi. i have a simple question. I have 2 sheets for expenses for different time period, for example 2014 and 2015 expenses. I want to add both the sheet, so that in one sheet i have the expenses for 2014 and 2015 (for 2015 data is changing on daily basis). Currently i am doing it manually, by pasting data in 3rd sheet for 2014 and then pasting the data for 2015 to get the complete sheet. I don’t require summarizing, i just need adding the data.
    How can i do it. Thanks.

    Reply

  35. Hi Ryan

    Is possible to merge two workbooks with different data & columns? Both worksheets have at least 20 columns each? The only similarity is both worksheets have one column with a serial number that can be matched up. Is the process a vlookup? and how how is it done for more than one column?

    Reply

Comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.