How to Combine Data in Excel

Tired of manually copying and pasting data from one Excel spreadsheet to another? You're not alone. The good news is that there are much faster and more reliable ways to merge your data. This article will show you three powerful methods for combining data in Excel, from simple formulas to fully automated workflows.

Before You Start: A Quick Tip on Data Structure

For any of these methods to work smoothly, your data needs to be structured consistently. This means your tables or sheets should have the same column headers in the same order. For instance, if you're combining monthly sales reports, each file should have columns like 'Date', 'Product ID', 'Units Sold', and 'Revenue' in that exact sequence.

A little cleanup upfront will save you massive headaches later. With that out of the way, let’s get into the methods.

Method 1: Combining Data with Formulas

If you need to combine text from different cells into a single cell - like merging a first name and a last name - formulas are your best friend. Excel offers a few different functions for this, each with its own strengths.

Using CONCATENATE or CONCAT

CONCATENATE is the classic function for joining text strings together. The newer CONCAT function does the exact same thing but is slightly quicker to type.

Let's say you have a first name in cell A2 and a last name in cell B2. To combine them into a single cell with a space in between, you would use this formula:

=CONCAT(A2, " ", B2)

Here’s how it works:

• A2 is the first text string (e.g., "Jane").
• " " is the second text string - a space enclosed in quotes. You must tell Excel to add connectors like spaces or commas manually.
• B2 is the third text string (e.g., "Doe").

The result would be "Jane Doe" in the cell where you entered the formula.

A More Powerful Option: TEXTJOIN

The TEXTJOIN function is a major upgrade because it lets you specify a separator (like a comma or underscore) to place between each item you’re joining. It can also automatically ignore empty cells.

The syntax looks like this:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Imagine you have an address spread across four columns: Street (A2), City (B2), State (C2), and Zip Code (D2). You want to combine them into a standard address format.

You’d use this formula:

=TEXTJOIN(", ", TRUE, A2:D2)

Let’s break it down:

• ", " is the delimiter. It will put a comma and a space between each piece of text.
• TRUE tells Excel to ignore any empty cells.
• A2:D2 is the range of cells you want to combine.

The result would be something like "123 Main St, Anytown, CA, 90210" without needing to manually add the ", " between each cell reference.

Method 2: Combining Sheets with Power Query

If you have several Excel files or sheets with the same structure that you need to merge into one master file, Power Query is your best tool. Think of it as an automation engine built right into Excel. You set it up once, and afterward, you can combine your files with a single click.

This is perfect for consolidating things like weekly ad-spend reports, monthly sales data, or regional performance reports into a single, comprehensive dataset. For this example, let's say you have a folder on your computer filled with monthly sales reports - one file for each month.

Step 1: Get Data from a Folder

First, make sure all your Excel files are in a single folder. Then, in a new Excel workbook:

• Go to the Data tab.
• Click on Get Data -> From File -> From Folder.
• A dialog box will appear. Click Browse and navigate to the folder containing your files. Click OK.

Step 2: Combine the Files

A navigator window will pop up, showing a list of all the files in your selected folder. At the bottom, click the dropdown arrow on the Combine button and select Combine & Transform Data.

Choosing "Transform" opens the Power Query editor, giving you a chance to review and clean the data before loading it into Excel. It's almost always a good idea to do this.

Step 3: Select the Sheet & Review

Power Query will ask for an "example file" to use as a template. It needs to know which sheet within each workbook holds the data.

• Select the appropriate sheet (commonly named 'Sheet1').
• Click OK.

The Power Query Editor will open. You’ll see that it has magically appended all your data into one big table. As a bonus, it automatically adds a column called 'Source.Name' that lists the original filename for each row - this is incredibly useful for tracing your data back to its source.

Step 4: Load the Data into Excel

If the data looks good, click the Close & Load button in the top-left corner of the editor. Excel will now load your combined data into a new worksheet as a formatted Excel Table.

The best part? This process is repeatable. Next month, when you add the new sales file into the folder, just right-click your combined table in Excel and hit Refresh. Power Query will automatically run the process again and add the new data to your master table.

Method 3: Using VBA to Combine All Worksheets in a File

Sometimes your data isn't in separate files, it's scattered across multiple worksheets within the same workbook. For these situations, a short VBA (Visual Basic for Applications) script can instantly consolidate everything for you.

Step 1: Open the VBE (Visual Basic Editor)

• First, make sure the Developer tab is visible. If not, go to File > Options > Customize Ribbon and check the box for Developer.
• On the Developer tab, click Visual Basic. You can also just press Alt + F11.

Step 2: Insert a New Module

In the VBE, find your workbook in the "Project" pane on the left. Right-click on it, then select Insert > Module. A blank code window will appear.

Step 3: Paste the VBA Code

Copy and paste the following code into the module window. This script will create a new worksheet named "Master Sheet" and then copy the data from every other worksheet into it, skipping the header row for all but the first sheet.

Sub CombineAllSheets()
    'Declare variables
    Dim ws As Worksheet
    Dim MasterSheet As Worksheet
    Dim LastRow As Long
    Dim CopyRange As Range

    'Disable screen updating to speed up the process
    Application.ScreenUpdating = False

    'Create and name the Master Sheet
    Set MasterSheet = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    MasterSheet.Name = "Master Sheet"

    'Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        'Make sure it's not the Master Sheet itself
        If ws.Name <> "Master Sheet" Then
            'Find the last row with data in the Master Sheet
            LastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
            
            'Copy the header row from the first data sheet
            If LastRow = 1 And MasterSheet.Range("A1").Value = "" Then
                ws.Rows(1).Copy MasterSheet.Rows(1)
            End If
            
            'Find the range to copy (all data except the header)
            Set CopyRange = ws.UsedRange
            Set CopyRange = CopyRange.Offset(1).Resize(CopyRange.Rows.Count - 1)
            
            'Copy the data to the Master Sheet
            CopyRange.Copy MasterSheet.Cells(LastRow + 1, "A")
        End If
    Next ws

    'Auto-fit columns on the Master Sheet
    MasterSheet.Columns.AutoFit

    'Re-enable screen updating
    Application.ScreenUpdating = True

    'Notify the user that the task is complete
    MsgBox "All sheets have been successfully combined into the ""Master Sheet""."
End Sub

Step 4: Run the Macro

Close the VBE to return to Excel. Go to the Developer tab, click Macros, select 'CombineAllSheets' from the list, and hit Run. The script will execute in seconds, leaving you with a brand new, consolidated master sheet.

Final Thoughts

As you can see, Excel provides multiple ways to combine your data, from simple formulas for merging text to powerful tools like Power Query for automating entire workflows. The method you choose depends on whether your task is a one-off merge or a repeatable reporting process that needs to happen weekly or monthly.

Of course, combining data in a spreadsheet is just one piece of the puzzle. Most marketers and business owners struggle with combining data from completely different platforms - like Google Analytics, Shopify, Facebook Ads, and Salesforce. Stitching that data together takes hours of manual work. That's why we built Graphed. We connect to all your sources, allowing you to instantly consolidate your data and build powerful, real-time dashboards just by describing what you want to see in plain English.