Combining Multiple Worksheets in Any Version of Excel

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.

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.

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.

Enter your email address to subscribe to our newsletter and receive the best of AccountingWEB every week.

Enter email address

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.

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.

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.