How To Combine Data in Excel

When working with data in Excel, combining data from multiple tables and sources is a common task. Power Query has two ways to do this: Append and Merge. Knowing the difference and when to use each is key to data manipulation. This post will go through both Append and Merge in detail with step by step instructions and best practices.

Dynamic Date Example

by Mihir Kamdar / Last Updated:

how to combine data in excel

What You'll Learn

In this comprehensive guide, you’ll learn:

  • The fundamental differences between Append and Merge operations

  • When to use each method for optimal results

  • Step-by-step procedures for both Append and Merge

  • Advanced techniques like Fuzzy Matching

  • Best practices for efficient data combination

  • Common troubleshooting tips and solutions

Download File

Download our step-by-step tutorial file now by clicking on the icon  download icon 1  below and follow along to enhance your Excel skills practically and efficiently!

Introduction

Power Query’s append and merge capabilities revolutionize data management by automating the tedious task of combining multiple data sources into cohesive, actionable insights. Through append’s vertical stacking and merge’s relational joining, analysts can transform hours of manual data manipulation into seconds of automated processing, all while maintaining data integrity and creating reproducible workflows.

When working with large datasets, it’s often necessary to set up multiple sheets to organize data efficiently. Understanding how to combine Excel spreadsheets is essential for consolidating related data into a single location.

To merge datasets seamlessly, you can learn how to combine Excel sheets and how to merge sheets in Excel by using built-in features like Power Query. For more specific needs, tools such as “Consolidate” help in consolidating Excel files across different workbooks.

If you’re managing tabs within a single workbook, knowing how to merge tabs in Excel is vital for streamlining your data. This includes techniques on how to merge Excel sheets while maintaining data integrity.

For advanced data manipulation, how to combine sheets in Excel ensures a structured workflow for consolidating information from different worksheets.

How To Combine Data in Excel

For data consolidation from multiple Excel workbooks, start by identifying the data range in each workbook that needs combining. Ensure consistent structure across multiple columns, with headers aligned. Select the upper left cell of the destination sheet as the starting point for data consolidation. Use Power Query or the Consolidate tool in Excel to merge two or more cells from various files into one cohesive Excel file, enabling efficient integration of datasets for analysis and reporting.

To combine data using the consolidate feature in Excel, navigate to the Data menu and select “Consolidate.” This tool allows you to pull data from multiple ranges or workbooks into a single Excel table, ensuring seamless integration. For example, use an example formula like =SUM(A1:A5) to test aggregation. Avoid directly merge cells to maintain data integrity. This method helps consolidate and summarize data efficiently while retaining its structure and usability.

If you frequently work with multiple datasets, you may need to merge tabs in Excel to simplify data analysis. For larger projects, understanding how to merge worksheets in Excel or even merge Excel sheets into one can save significant time and effort.

To streamline workflows, Excel offers methods to combine multiple Excel sheets into one, making it easier to manage consolidated data. If you’re wondering how to merge multiple sheets in Excel, Power Query or VBA macros can automate this process efficiently.

For complex projects, you can learn how to consolidate sheets in Excel, ensuring data from different tabs aligns properly. Knowing how to merge multiple worksheets in Excel or combine Excel sheets into one is especially useful for creating comprehensive reports.

If you’ve ever asked, “How do I combine Excel spreadsheets?”, tools like the Consolidate feature or Append functionality in Power Query provide effective solutions. Additionally, mastering how to combine all sheets in Excel or how to merge Excel spreadsheets will greatly enhance your productivity.

1. Understanding Append vs. Merge to Retrieve Data​

What’s the Difference?

• Append: Stacks tables vertically by adding rows. Like a SQL UNION ALL, combines tables with the same structure into one table

• Merge: Joins tables horizontally by combining columns based on common fields. Like a SQL join, combines data from multiple tables into one table. This process is similar to a vertical lookup in Excel, where you search for a value in the first column of a table and retrieve a corresponding value from the same row.

how to combine excel spreadsheets
Excel: Free Masterclass​
Supercharge Your Skills For Free

2. Deep Dive: Append Operations in a Table Array

Understanding Append

Append combines multiple identical tables into one table. Here’s what to know:

  • Table Structure: Same table structure, same data types, and column names
  • Usage: For combining similar data sets, like monthly reports or quarterly data
  • Process: Tables are stacked vertically, resulting in one table with more rows

Before you append:

  • Table Structure: Tables have the same structure, same data types and column names
  • Data Consistency: Data types match, column headers are the same

Two Ways to Append

1. Append Queries

For adding data to an existing query:

Open Power Query Editor

  • Open Excel and go to the Data tab
  • Select the tables from each source
  • Go to Data > From Table/Range
  • Home > Close & Load To > Only Create Connection
how to merge sheets in excel

Perform the same steps for Feb – Jun and load the data to Power Query. There should be 6 queries for each month.

Method 1: Append Queries

  • Data > Get Data > Launch Power Query Editor
  • Home > Append Queries
  • Select all tables
  • Add > OK

Method 2. Append Queries as New

  • Home > Append Queries > Append Queries as New
  • Select all tables to combine
  • Add > OK
  • Home > Close & Load To > Only Create Connection
how to combine excel sheets
Excel: Free Masterclass​
Supercharge Your Skills For Free

3. Master Class: Merge Operations

Understanding Merge

Merge combines data from different tables based on common columns, like a SQL join.

Types of Joins

Power Query has six types of joins:

  1. Full Outer Join

    • Returns all rows from both queries
    • Use when you need all data from both tables
  2. Left Join

    • Returns all rows from the left query
    • Use when you want to keep all records from your main table
  3. Right Join

    • Returns all rows from the right query
    • Use when you need all records from your secondary table
  4. Inner Join

    • Returns only matching rows
    • Use when you need only data that exists in both tables
    • The corresponding value is retrieved from the same row where the match is found
  5. Left Anti Join

    • Returns rows from the first query without matches
    • For finding missing or unmatched records
  6. Right Anti Join

    • Returns rows from the second query without matches
    • For unique records in the secondary table
how to combine sheets in excel

Merge Steps

Method 1: Merge Queries

Step 1: Load Your Data

  • Select tables to merge and go to Data > From Table/Range
  • Home > Close & Load To > Only Create Connection
how to consolidate sheets in excel

Load the second table.

how to merge multiple worksheets in excel

Step 2: Merge Data

  • Launch Power Query Editor
  • Select the query and go to Home > Merge Queries
  • Select common columns and choose join type
  • Select the correct column number to ensure accurate data retrieval
  • OK and select columns to expand from dropdown
how to merge excel spreadsheets

Method 2: Merge Queries as New

Step 1: In Power Query Editor

  • Select the query and go to Home > Merge Queries as New
  • Select common columns and choose join type
  • OK and select columns to expand
how to merge multiple sheets in excel
Excel: Free Masterclass​
Supercharge Your Skills For Free

4. Advanced Technique: Fuzzy Match and Exact Match Merge

What is Fuzzy Matching?

Fuzzy matching matches similar but not exact values, for:

  • Customer records with misspellings
  • Similar product names
  • Inconsistent data

This is an approximate match and can be useful in scenarios where exact matches are not possible.

How to Implement Fuzzy Match

Step 1: Prepare Your Data

  • Select Data and load tables into Power Query
  • Close and load to
  • Only create connection
how to combine worksheets in excel

Step 2: Configure Fuzzy Match

  • Select common columns
  • Select join and set similarity threshold to 0
  • Select the column that should be inserted
  • Data is now merged
how do you merge worksheets in excel

What distinguishes Append from Merge in Power Query?

Append Queries: This function allows you to merge tables by stacking them on top of each other, effectively adding rows from one table to another. For this to work seamlessly, both tables must have matching column names and compatible data types.

Merge Queries: This operation joins tables side by side by incorporating columns from one table into another based on a shared column or key, similar to SQL joins like Inner Join and Left Join.

When should I choose Append over Merge in Power Query?

Use the Append function when you aim to combine datasets that share the same structural format, such as adding data for different years. On the other hand, utilize Merge when you need to integrate related information from various tables using a common field, such as appending customer details to sales records.

Is it possible to append tables with varying columns in Power Query?

Absolutely! When you append tables with differing columns, Power Query will automatically create new columns for any extra fields present in one table but absent in the other. For those columns not found in one of the source tables, the cells will display as null.

How does Power Query manage duplicates during the append operation?

When you append queries, duplicates are not automatically removed. To manage duplicate entries, you’ll need to implement additional steps such as using the “Remove Duplicates” feature or the “Group By” function to organize your data.

What types of joins can I utilize with Merge queries in Power Query?

Power Query offers several join types, including Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join. Each join type serves a unique function, allowing you to combine your data according to your specific needs.

How can I consolidate data from multiple cells in Excel?

To consolidate data from multiple cells in Excel, use Power Query’s append or merge features. Append stacks data vertically into one table, while merge joins data horizontally based on common fields. These tools simplify combining datasets and ensure consistency in your consolidated results.

Conclusion

Using Append and Merge techniques can significantly enhance data management capabilities in Microsoft Excel. Append and Merge in Power Query is key to data manipulation. Remember:

  • Append for combining similar table structures vertically
  • Merge for joining related data horizontally
  • Fuzzy Match for handling imperfect data matches
  • Always validate your results after operations
Facebook
X
LinkedIn
Pinterest
Email
Table of Contents

Office Tech Skill is a premier educational platform focused on Microsoft Office, providing professionals with the tools needed to thrive in the ever-evolving tech landscape.

© 2025 Office Tech Skill. All rights reserved