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.

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)