How to Merge Data Sets in Excel

Wrangling data across multiple spreadsheets is a classic Excel challenge. Whether you have monthly sales reports in separate files or customer details in one tab and order information in another, combining them into a single, usable dataset is often the first step to analysis. This guide will walk you through several effective methods for merging data in Excel, from a quick copy and paste to more powerful, automated solutions.

The Classic Method: Simple Copy and Paste

Sometimes the simplest solution is the best one, especially for a one-time task. If you have two or more datasets with the exact same columns and structure, manually copy-pasting is the fastest way to stack them together into one master list.

This method is best for: Small, simple datasets and one-off tasks where you won't need to repeat the process.

Here's how to do it:

1. Organize Your Sheets: Open all the Excel spreadsheets containing the data you want to merge. Create one "master" sheet where you will consolidate everything. Copy the column headers from one of your source sheets into this master sheet.
2. Copy the First Dataset: Go to your first data source sheet. Select all the data, but do not include the header row (since you already have it in the master sheet). A quick way to do this is to click the first cell of data (e.g., A2), then press Ctrl + Shift + End to select everything to the bottom-right corner. Press Ctrl + C to copy.
3. Paste into the Master Sheet: Navigate to your master sheet, click the first empty cell under your headers (e.g., A2), and press Ctrl + V to paste the data.
4. Repeat for Other Datasets: Go to your next data source sheet and repeat step 2. In the master sheet, find the first available empty row below the data you just pasted and paste the next batch. Continue this until all your data is consolidated into the master sheet.

While easy, this method is prone to human error and becomes extremely tedious with many files. It's also static, if the source data changes, you have to redo the entire process.

Using VLOOKUP to Combine Data from Different Tables

What if your data isn't set up to be stacked, but rather needs to be joined side-by-side? For example, you have a list of sales transactions with a CustomerID and a separate list of customer details, also with a CustomerID. You want to pull the customer's name into your sales transaction list.

This is a perfect job for VLOOKUP (Vertical Lookup). It searches for a value in the first column of a table and returns a corresponding value from a different column in the same row.

This method is best for: Pulling information from one table into another based on a shared, unique identifier.

The VLOOKUP formula looks like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Step-by-Step Example:

Imagine you have two sheets: Sales and Customers. You want to add the CustomerName from the Customers sheet into your Sales sheet. Both sheets have a CustomerID column.

1. Add a New Column: In your Sales sheet, add a new column where you want the customer names to appear. Let's call it "Customer Name".
2. Enter the VLOOKUP Formula: In the first cell of your new "Customer Name" column (let's say it's D2), you'll start typing the formula.
3. Complete and Fill: Your final formula should look something like this:

=VLOOKUP(A2, Customers!$A$2:$B$100, 2, FALSE)

Press Enter. You should see the correct customer name pop up. Now, click on that cell and drag the small square in the bottom-right corner (the fill handle) down to apply the formula to the rest of your rows.

VLOOKUP is a workhorse, but it has limitations. It can only look to the right of the lookup column, and it can be slow on very large datasets.

A More Powerful Alternative: Combining INDEX and MATCH

For more flexibility and performance, many Excel pros prefer combining the INDEX and MATCH functions. Together, they achieve the same goal as VLOOKUP but with a few key advantages: your lookup column can be anywhere, and the formula is less likely to break if you add or remove columns from your source table.

The approach nests a MATCH function inside an INDEX function:

=INDEX(column_to_return_from, MATCH(lookup_value, lookup_column, 0))

Step-by-Step Example:

Let's use the same Sales and Customers scenario.

1. Start with INDEX: In your "Customer Name" column on the Sales sheet, begin the formula with =INDEX(. The INDEX function needs the full column of data you want a value from. So, navigate to your Customers sheet and select the entire CustomerName column (e.g., Customers!$B$2:$B$100).
2. Add the MATCH Function: Now, add the MATCH part. This will tell INDEX which row to pull the name from.
3. Complete and Fill: Your final formula will look something like this:

=INDEX(Customers!$B$2:$B$100, MATCH(A2, Customers!$A$2:$A$100, 0))

Press Enter and drag the fill handle down to apply it to all your sales data.

This duo is a more robust and efficient way to join datasets side-by-side in Excel.

The Pro Method: Merging Datasets with Power Query

For repeatable, complex, or large-scale data merging, there is no better tool inside Excel than Power Query (called "Get & Transform Data" in the Data tab). Power Query is a data transformation engine that records your merging and cleaning steps. This means you can set it up once, and then simply click "Refresh" to update your merged data whenever the source files change.

Power Query handles both scenarios we've discussed: stacking data (appending) and joining data side-by-side (merging).

Appending Queries: Stacking Data on Top of Each Other

Use this when you have several files or tables with the same structure, like monthly reports you want to combine into a master year-to-date list.

1. Format as a Table: Open one of your files. First, convert your data range into an official Excel Table by clicking anywhere inside it and pressing Ctrl + T. Repeat this for all the datasets you want to append.
2. Load into Power Query: Click anywhere within your first Table. Go to the Data tab and click From Table/Range. This opens the Power Query Editor.
3. Create Connections: You don't need to load each table onto a new sheet yet. Instead, in the Power Query Editor, click the dropdown for Close & Load, and select Close & Load To.... In the window that opens, select Only Create Connection, and click OK. Repeat this process for all of your data tables. Now, you'll have a connection to each table in the Queries and Connections pane.
4. Append Queries: Now, go to the Home tab, click Append Queries. In the Append dialog, if you have just two tables, you can leave it on Two Tables. If you have more, select Three or More Tables. Select the connection queries you created and click Add to move them to the Tables to Append box. Click OK.
5. Close and Load: Your Power Query Editor will now show you a preview of all the data stacked together. In the Home tab, click the Close & Load button to load the combined data into a new worksheet in Excel.

This is where the magic happens. Next time you add new data to your source tables, you simply go to the Data tab > Refresh All, and the master table updates automatically!

Merging Queries: Joining Data Based on a Shared Identifier

Sometimes, you'll want to pull information from one table into another using a shared unique identifier. This applies to the VLOOKUP and INDEX/MATCH scenarios.

1. Start with Power Query: The process for appending, connecting your datasets to Excel Tables, loading them into Power Query, and creating connections, is the same as the previous section.
2. Merge Queries: On the Home tab in the Power Query Editor, click Merge Queries. Choose your main table (e.g., Sales) on the top dropdown. Then select your second dataset (e.g., Customers) from the bottom dropdown. Match the common columns (e.g., CustomerID) in both tables and click OK.
3. Expand Columns: In the Power Query Editor, you'll see a new column with tables. Click the double-headed arrow in the column header to expand it. Select your fields (for example, CustomerName) and click OK to close the window.

Power Query makes it easy to set up and adjust these processes. Once you're done, it's a quick refresh to get updated data into your Excel workbook for further analysis.

Final Thoughts

We’ve covered the classic copy-paste method, VLOOKUP, INDEX/MATCH, and the fully automated Power Query for repeatable processes. By choosing the right method depending on the complexity and volume of your data, you can perform tasks more efficiently. Merging datasets is just the first step in a larger data processing and analysis project. Analyzing performance from different platforms like Google Analytics, Shopify, or Salesforce requires continuously importing and organizing more CSVs. Instead of spending hours in Excel trying to extract data from a dozen sources, Graphed can automate this whole process, presenting your data in plain English via an interactive dashboard. It automatically connects your Google Analytics, Facebook ads, and Shopify data, so you can get a complete picture of your performance at a single glance.