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.

To do so:

  1. Create a new worksheet labeled Combined to house the data from the other worksheets, as illustrated in Figure 1.
  2. Click on the Data menu.
  3. Click on FromOther Sources and then select From Microsoft Query from the drop-down.
  4. A Choose Data Source dialog box should then appear; from which you’ll clear the checkbox for Use the Query Wizard to Create/Edit Queries.
  • If the Choose Data Source dialog box doesn’t appear, a prompt will notify you that Microsoft Query is not presently installed. In this case, follow the on-screen instructions to enable Microsoft Query within your version of Excel. Microsoft Query is an optional feature that could have been omitted when Excel was installed on your computer. Once you have Microsoft Query installed, you can then continue with step 4.
  1. Double-click Excel Files* within the Choose Data Source dialog box. Double-clicking enables you to skip the OK button.

Combine worksheets 1

Figure 1: Make sure to uncheck the Use the Query Wizard to create/edit queries in step 4.

  1. Use the Select Workbook dialog box to choose the name of the current workbook, as illustrated in Figure 2. It may feel circular to select the same workbook but Microsoft Query doesn’t make a distinction.
  2. Click OK to close the Select Workbook dialog box.
  3. Click Close without making any selections within the Select Table dialog box.

Combine worksheets 2

Figure 2: You’ll need to select the name of the current workbook even though it’s already open.

  1. At this point Microsoft Query will appear onscreen, from which you’ll click the SQL button. SQL is short for Structured Query Language, which is the vernacular used to request information from databases. In this case we’re treating the additional worksheets within our workbook as database tables.
  2. In the SQL Statement box, enter the following:

SELECT *

FROM “E:\Combine Worksheets.xlsx”.”January$”

Union

SELECT *

FROM “E:\Combine Worksheets.xlsx”.”February$”

In this context “Select *” informs Microsoft Query that we wish to pull all of the data from the given worksheet, based on the contiguous block of cells that starts in cell A1. The “FROM” portion tells Microsoft Query where to find the data. In this case you’ll replace E:\Combine Worksheets.xlsx with the location and name of your workbook.

You’ll also replace January$ and February$ with your corresponding worksheet names. The word “Union” tells Microsoft Query that we wish to stack the data from the January worksheet on top of the data from the February worksheet. You can keep adding Union and Select statements as needed.

  1. Click OK once you’ve completed the SQL statement.
  2. Click OK to disregard the warning that the SQL Query can’t be displayed graphically.

Combine worksheets 3

Figure 3: Enter the SQL Statement, and replace E:\Combine Worksheets.xlsx with the workbook location and name of your workbook.

  1. The combined worksheets will now appear in the Microsoft Query dialog box, as illustrated in Figure 4.
  2. Select the File menu within Microsoft Query.
  3. Select Return Data to Microsoft Excel at the bottom of the menu.
  4. An Import Data dialog box will allow you to decide how you wish to present the data:
  • A Table will be a simple list within the worksheet that displays all of the information from the other worksheets.
  • A PivotTable report enables you to create a report based on data from the other worksheets without having to see the combined data itself.
  • A PivotChart and Pivot Table Report enables you to summarize the data from the other worksheets both in chart and pivot table form at once.

Combine worksheets 4

Figure 4: You can display the combined data in Excel in a variety of ways.

A benefit to using Microsoft Query is that if the data from the other worksheets changes, you can update the combined worksheet by right-clicking on any cell within the resulting table or pivot table and choosing Refresh.

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 (2)
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
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)
avatar
By alanb
May 30th 2018 10:32

Hi David,

Instead of adding the additional data as a new row, I want to add it as a new column, how do I do that?

I'm using Excel 2016.

Thanks!

Thanks (0)