Combining Sheets in Excel: Everything You Need to Know

Combining sheets in Excel doesn’t have to be a daunting task. Whether you’re working with small datasets or managing multiple workbooks, Excel offers powerful tools to merge and consolidate data. Merging two sheets in Excel or more can streamline your workflow, save time, and ensure data consistency. For example, you could consolidate sales reports, merge customer lists, or create a summary sheet from multiple tabs.

In this guide, you'll explore three practical ways to combine sheets in Excel: using Excel’s built-in tools, VBA, SQL, and Enquery—which lets you chat with your spreadsheets and write formulas and SQL with natural language. Get Enquery for FREE here.

Using Excel’s Built-In Tools to Combine Sheets

The Copy and Paste Method

This is the simplest way of combining sheets in Excel and is perfect for quick, one-off tasks. It’s ideal when you have a small number of sheets or minimal data to combine. However, for larger datasets, it can be time-consuming and prone to errors.

Here’s how to do it.

1. Open all the sheets you need to combine.

2. Select the data in one sheet by clicking and dragging over the desired cells.

3. Right-click and choose Copy or press Ctrl + C.

4. Navigate to the destination sheet and click on the starting cell where you want to paste the data.

5. Right-click and choose Paste or press Ctrl + V.

Repeat the process for all the Excel sheets you want to merge.

Using the Consolidate Function

Excel’s Consolidate feature is designed to combine data from multiple sheets or workbooks, especially when dealing with numeric data. It’s a great option for summarizing data from different sources.

Here’s a step-by-step guide.

1. Go to the sheet where you want the consolidated data to appear.

2. Click the Data tab in the top menu, then select Consolidate in the Data Tools group.

3. In the Consolidate dialog box:

• Choose a function from the Function drop-down menu (e.g., Sum, Average, Count).

4. Add the data ranges you want to consolidate:

• Click in the Reference box.

• Switch to the sheet you want to reference, select the range of cells, and click Add.

• Repeat for each range you want to include.

5. Under Use labels in, check the boxes for Top row and Left column if your data has headers.

6. Click OK to consolidate the data into your selected worksheet.

Power Query

Power Query is one of Excel's most powerful tools for combining sheets in Excel and transforming data. It automates the merging process, saving you significant time and effort, especially when handling multiple sheets or frequent updates. You can also refresh combined data automatically whenever the source sheets are updated, which ensures your data remains current. Additionally, it offers advanced data transformation capabilities that surpass standard Excel features. 

Here’s how to use Power Query to combine sheets in Excel.

2. Load each sheet into Power Query:

• Select New Query > From File > From Workbook.

• Browse and select the workbook containing the sheets.

• In the Navigator window, select the sheet you want to import and click Load or Edit if you want to make transformations.

3. Append Queries to merge sheets:

• In the Power Query Editor, go to Home > Append Queries.

• Choose Append Queries or Append Queries as New to create a new combined query.

• Select the tables or queries you want to combine.

4. Perform any transformations needed:

• Remove duplicates by selecting Remove Duplicates under the Home tab.

• Filter rows, change data types, or split columns as necessary.

5. Load the combined data into a new worksheet:

• Click Home > Close & Load to return to Excel with your combined data.

Using VBA to Automate Sheet Combination

If you find yourself combining sheets in Excel frequently, VBA (Visual Basic for Applications) can save you significant time by automating the process. This method is powerful for automating repetitive tasks, handling large datasets, or when you need a customized solution for combining sheets in Excel.

Here's a step-by-step guide to creating a VBA macro that combines all sheets in a workbook into one.

1. Open the VBA Editor.

• Ensure the Developer tab is visible. If not, click File > Options and select Customize Ribbon. In the right pane, check the box next to Developer. 

• Click the Dev tab and choose Visual Basic or press Alt + F11 in Excel to open the VBA Editor.

2. Insert a New Module.

• In the VBA editor, click on Insert > Module to create a new module for your code.

3. Copy and paste the VBA Code.

Copy the following code into the module.

Sub CombineSheets()
    Dim ws As Worksheet
    Dim wsMaster As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    
    ' Add a new worksheet for the combined data
    Set wsMaster = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsMaster.Name = "Combined"

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsMaster.Name Then
            ' Find the last row in the master sheet
            If Application.WorksheetFunction.CountA(wsMaster.Cells) = 0 Then
                lastRow = 1
            Else
                lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row + 1
            End If
            ' Set the range to copy
            Set rng = ws.UsedRange
            ' Copy headers only from the first sheet
            If lastRow = 1 Then
                rng.Copy wsMaster.Cells(lastRow, 1)
            Else
                rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count).Copy _
                    wsMaster.Cells(lastRow, 1)
            End If
        End If
    Next ws
End Sub

4. Run the Macro.

• Press F5 or go to Run > Run Sub/UserForm to execute the macro. Wait for Completion, as the time it takes depends on the amount of data.

• Verify the result by checking the "Combined" sheet to ensure all data has been consolidated correctly.

5. Customize the VBA Script to fit your needs better.

• Change the destination sheet name by updating the line wsMaster.Name = "Combined" to your preferred sheet name.

• Exclude specific sheets by adjusting the If condition: vba If ws.Name <> wsMaster.Name And ws.Name <> "SheetToExclude" Then. Replace "SheetToExclude" with the name of the sheet you don't want to include.

• Adjust for different data structures by modifying the range copying logic if your sheets have different layouts or if you need to skip certain rows or columns.

Additionally, consider the following when using VBA.

• Enable Macros: Make sure macros are enabled in your Excel settings to run VBA scripts.

• Security Risks: Only run macros from trusted sources to protect against potential security threats.

• Backup Data: Always save a copy of your workbook before running macros to prevent data loss.

Using SQL to Combine Excel Data

SQL (Structured Query Language) offers a powerful way to combine sheets in Excel, especially for advanced users dealing with large datasets or complex data relationships. By treating Excel sheets as database tables, SQL allows you to query and merge data efficiently.

To use SQL within Excel, you can leverage either Microsoft Query or Power Query to run SQL statements against your Excel data. Alternatively, you can use Enquery, which simplifies the process. Enquery integrates SQL capabilities with AI, allowing you to write complex queries or use plain language to perform tasks. 

Using Microsoft Query

1. Prepare your data.

Ensure each sheet you want to query has a defined Named Range or is formatted as a Table.

Define Named Range:

• Select the data range in your sheet.

• Go to Formulas > Define Name.

• Provide a meaningful name (e.g., Sheet1Data, SalesData).

Format as Table:

• Select your data range.

• Go to Insert > Table.

• Ensure My table has headers is checked.

2. Initiate Microsoft Query.

• Navigate to Data > Get Data > From Other Sources > From Microsoft Query.

3. Choose Data Source.

• Select Excel Files in the Choose Data Source dialog box and click OK.

• Browse and select your workbook file, then click OK.

4. Select tables.

• In the Query Wizard – Choose the Columns window and select the tables or named ranges you defined.

• Click Add to include them in your query.

• After adding all desired tables, click Close.

5. Open SQL Editor.

• In the Query Wizard, choose View data or edit query in Microsoft Query and click Finish.

• In Microsoft Query, go to View > SQL to open the SQL editor.

6. Write and execute your SQL query.

• Replace any existing text with your SQL query to combine data.

For example:

SELECT * FROM Sheet1Data
UNION ALL
SELECT * FROM Sheet2Data

7. Return data to Excel.

• After verifying that your query works, go to File > Return Data to Microsoft Excel.

• Choose where you want to place the data in your workbook and click OK.

Using Power Query with SQL

1. Load data into Power Query.

• Go to Data > Get Data > From File > From Workbook.

• Browse and select your Excel file, then click Import.

• In the Navigator window, select the sheets or tables you want to use.

• Click Transform Data to open the Power Query Editor.

2. Access the Advanced Editor.

• In the Power Query Editor, go to Home > Advanced Editor.

3. Write your SQL query.

• Replace the existing code with an SQL query wrapped in Power Query's M language.

For example:

let
    Source = Excel.Workbook(File.Contents("C:\\Path\\To\\Your\\File.xlsx"), null, true),
    SQLQuery = Sql.Database(".", "", [Query="SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$]"])
in
    SQLQuery

4. Apply and load data.

• Click Done to close the Advanced Editor.

• Preview the data to ensure it looks correct.

• Go to Home > Close & Load to import the data into your Excel workbook.

Using SQL in Excel comes with certain considerations. There is a learning curve involved, as it requires knowledge of SQL syntax and database concepts. The initial configuration may be more complex compared to other methods, making the setup more involved. 

Additionally, you need to ensure that your version of Excel supports the necessary features, such as Power Query or Microsoft Query, to utilize SQL effectively—unless you’re using Enquery.

Enquery for Combining Sheets in Excel and Other Data Integration

If you're looking for a simpler way to merge your projects or combine sheets in Excel without diving into SQL complexities, you can try Enquery. It innovatively combines the user-friendly nature of spreadsheets with the robust capabilities of databases, personally guiding you through the merging process step by step.

Key Features of Enquery

• Intuitive Interface: User-friendly interface makes it easy for Excel users to navigate and operate.

• Automated Merging: With Enquery, the merging process is automated, reducing the need for manual intervention and minimizing errors.

• Flexible Integration: Enquery allows for seamless integration with existing Excel files and SQL databases. This provides immense flexibility in data management.

• Step-by-Step Guidance: Enquery provides step-by-step guidance throughout the merging process, ensuring users can merge files efficiently and accurately.

• Data Validation: Enquery includes built-in data validation features to help users identify and resolve any inconsistencies or errors in the merged data.

With Enquery, you can streamline your data integration process, save time, and ensure accuracy in your merged datasets. Say goodbye to manual data manipulation and hello to a smoother, more efficient workflow. Try Enquery today for 30 days FREE.

The Challenges of Joining Data Across Sheets in Excel

While Excel offers tools for combining data from multiple sheets, each method has limitations. Here are some things to keep in mind.

Managing Duplicates and Inconsistencies

Combining data often introduces duplicates or inconsistencies, such as variations in data formatting (e.g., “John Doe” vs. “Doe, John”) or mismatched column values. Even though Excel’s Remove Duplicates feature can help, manually reviewing and resolving discrepancies across sheets can require significant effort.

Aligning Data Structures

Mismatched headers, varying column orders, or missing fields often occur when combining data from multiple sources. Standardizing these structures before merging can involve renaming columns, reordering data, or filling in missing information—steps that can delay your workflow.

Comparing Data Across Sheets

Tasks like verifying whether certain values exist in one sheet but not another require functions like VLOOKUP, INDEX-MATCH, or XLOOKUP. While effective, these formulas can be tricky to set up and prone to errors if ranges or references are misaligned.

Handling Large Datasets

Excel’s performance can decline with large datasets, especially when using complex formulas or merging numerous sheets. This can lead to slower processing times, freezing, or calculation delays, creating frustration and inefficiencies.

Keeping Data Updated

Combining sheets in Excel is rarely a one-time task. Keeping merged data synchronized with updates in the source sheets is challenging unless you use advanced tools like Power Query. Even then, setting up and maintaining these updates requires additional steps and ongoing management.

With Enquery, these challenges become a thing of the past. Enquery simplifies comparing and merging data across sheets by leveraging AI-driven tools and SQL integration. Save time and reduce manual effort. Try Enquery today for FREE.

Comparison of Methods for Combining Sheets in Excel

To help you choose the best method for your needs, here's a side-by-side comparison of each method discussed—Excel's built-in tools, VBA, SQL, and Enquery.