Combining Multiple Worksheets in Any Version of Excel

Spreadsheets and graphs on a desk
xfgiro/istock

From time to time you may have a need to stack data from two or more worksheets into a single list. Most users would copy and paste the data together, but you can also use Microsoft Query. 

Alternatively, you can use PowerQuery in Excel 2013, renamed Get and Transform in Excel 2016, but those features require far more experience with database queries than the average Excel user has accumulated at this point. Fortunately, Microsoft Query is an alternative that is available in all versions of Excel that can guide you through the process.

The process I’ll describe can work within an existing workbook, or you can pull data from multiple workbooks. The only change you’ll make is with regard to the workbook and sheet names, so for this article we’ll assume you’re combining data from within the same workbook.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
May 11th 2016 12:22

Thank you so much
its very useful for me

Thanks (2)
avatar
By tempie
Dec 18th 2016 22:07

Some is going wrong. I get to the point of SQL Query can't be selected..., press OK and receive the following message:
"Could not add the table "'N:\Test.xlsx"."Sheet1'"."
I started with a more complicated situation, but simplified it to see what the problem could be. My test file is simple workbook consisting of sheet1 containing text in the first 2 cells of the first 2 rows and second sheet where I want sheet1 to appear. My SQL statement is:
SELECT *
FROM "N:\Test.xlsx"."Sheet1"

I am using 64 bit Office 2016 on win 7 pro 64 bit

Thanks for any insight.
Duncan

Thanks (0)
avatar
By tempie
Dec 20th 2016 01:59

I found the problem. Did not include the $. But this does not simply append the sheets but sorts them by row. Because of the PDF to Excel conversion, I have rows with no good reference that get sorted out of sequence. I will have to try VB.

Thanks (0)
to tempie
Aug 7th 2017 17:44

I'm just now seeing your comment for the first time. Given that we're using SQL statements here, you can add a WHERE clause that omits blank or null values based on one of the field names. You can accomplish what you want within Microsoft Query.

Thanks (1)
avatar
By Sabine
Feb 3rd 2017 16:02

Hi! I've got a problem...What I need is 10 sheets combined (all in the same workbook in Excel 2010). They all have the same headers but the data is different. I want them all copied together in one table that can be updated automatically. I have no data to manupulate (e.g. sum), I just want all the data pasted together. I also can't use Power query or VBA (long story). I hope the issue can be solved with Microsoft query but I am not sure as I got the impression there need to be overlapping data in a column in all sheets...Could anybody help? Thanks a million in advance!

Thanks (0)
to Sabine
Aug 7th 2017 17:43

I'm just now seeing your comment for the first time. The article describes exactly that process, you'll keep appending additional UNION statements until you've listed all 10 worsheets.

Thanks (0)
avatar
Jun 16th 2017 18:26

As I have already stated in the previous article excel is a very important tool for me in the performance my audit work. I am learning some more interesting things and tricks when using excel through the articles.

Thanks (1)
avatar
Aug 7th 2017 11:18

Thank you for this information.

Can you advise how I can add an additional sheet after the query has been created? I tried the Data tab > Properties > Edit Query but keep getting an 'Unrecognized database format' error message.

Thanks (0)
to Suzanne McMorrow
Aug 7th 2017 17:46

"Unrecognized database format" could indicate that the workbook you've linked to is corrupted. I'd try copying the content to a new, blank workbook and see if the steps work from there. You're on the right track, you should indeed be able to edit the query and add additional SQL statements to the query.

Thanks (0)
avatar
Aug 23rd 2017 17:57

This is great and easy to follow... but when try to click "OK" to run the SQL statement I get:
Could not add the table
"F:\2017 stats orig.xlsx"."2017_12345$"

My SQL statement is:

SELECT*
FROM "F:\2017 stats orig.xlsx"."2017_12345$"
Union
SELECT*
FROM "F:\2017 stats orig.xlsx"."2017_12346$"

I have checked and rechecked all the steps, but cannot find the issue.

I would be very grateful for any assistance!

Thanks (0)
to LaurieMc
Aug 23rd 2017 20:08

Laurie,

I set up a file with the name you used and the sheet names in question, along with some sample data. I was able to get everything to work and I didn't get that error triggered.

It could be that your workbook has some corruption in it. As a starting point I'd copy and paste your data to a brand new workbook and try again to see if it works. If it doesn't, I'd be happy to take a look if you can share your workbook with me. Feel free to remove any confidential data. You can upload the file by way of the Upload a Project file link at www.acctadv.com.

David

Thanks (0)
avatar
Sep 11th 2017 20:42

I would like to use this method of combination for our website inquiries that way we can keep a running tally of the information we receive in separated tabs and a a combined tab where all of the inquiries can be read at once. I have read and re read the instructions and have followed them correctly. I have also recreated the workbook and tried saving it on my desktop rather than the network to get this to work but cannot seem to figure out what I am doing wrong. I am trying to combine 8 worksheets into one combined sheet.

Here is a sample SQL statement:
SELECT *
FROM "T:\Marketing\Website Consult.xlsx"."Administration$"

Union

SELECT *
FROM "T:\Marketing\Website Consult.xlsx"."business law$"

After I click okay I receive the error message: Could not add the table "T:\Marketing\Website Consult.xlsx"."Administration$".

All have the same header (the height and width of the cells do differ a little) I have also used data validation and applied a filter within each page of the workbook. Can these added tools effect how the Query tool works?

Thanks (0)
to vland7788
Oct 3rd 2017 22:15

No, I don't think the problem is with height or width or any other features, except the Filter. Excel's Filter feature could put the workbook in a state where you can't combine the sheets. I'd try turning it off and see if that helps as a rule out. If you have the same problem afterwards, then you can skip that.

For the Administration worksheet, I'd try using Microsoft Query to pull that data into a separate worksheet by itself. If it lets you retrieve the data, copy and paste the SQL statement into your existing query with the Union statements.

It's tough to diagnose without seeing your data, but setting it down and picking it up again with a fresh eye does wonders for tracking down nuances in one's data.

Thanks (0)
avatar
By mickeyk
Oct 3rd 2017 20:12

Thank you, I've found your article usefull.
However when trying to select more than 100 sheets i get an error "Could not add the Table ..".

Am using Excel 2007.

Any tips ?

Thanks (0)
to mickeyk
Oct 3rd 2017 22:17

I'll be honest, whenever I find anyone using 100 sheets for *anything* in Excel, it's typically time to look to another solution. But Excel may be what you have for a tool. In that case, if it were me, I'd try assembling as many sheets as I can into a single worksheet, and then start a second worksheet to combine together as many additional sheets as I can. I'd then use a third query to combine the two "combined" worksheets into one list.

Thanks (1)
avatar
By mickeyk
to David Ringstrom
Oct 3rd 2017 23:15

Thanks for trying to help.
My mistake, cannot run more than 50 SELECTs.
It’s a single worksheet with lots of sheets.
-
I’m trying to run:
SELECT *
FROM "C:\..\0516.xlsx"."Table 1$"
UNION
SELECT *
FROM "C:\..\0516.xlsx"."Table 3$"
UNION
.
.
.
SELECT *
FROM "C:\..\0516.xlsx"."Table 501$"

If the last sentence is:
FROM “.. TABLE 100$”
It runs, but if it

FROM .. 101$
It does not.
-
I've tried Table 2$ to Table 200$.
no success, it gives the error when it's Table 100$.

Thanks (0)
to mickeyk
Oct 3rd 2017 23:33

A VBA macro in Excel can stack everything together into one list. This is a common enough need that I'm sure a Google search would unearth a Combine Sheets macro.

Thanks (0)