Graphed LogoGraphed
Login

How to Merge Data in Google Sheets

Cody Schneider•

Combining reports from different sources doesn’t have to involve endless, error-prone hours of copying and pasting. Learning how to properly merge data in Google Sheets saves you time, reduces mistakes, and turns fragmented information into a powerful, unified view of your business. This guide breaks down the most effective methods for merging data, from simple stacking techniques to powerful lookup functions that connect related information instantly.

Understanding the Two Types of "Merging" in Spreadsheets

Before jumping into formulas, it's important to know what kind of merge you need to perform. Most data "merging" tasks fall into two categories: appending and joining.

  • Appending Data (Stacking): This is when you have data sets with the same columns that you want to stack on top of one another. Think of combining monthly sales reports into a single, year-to-date master list. Each report has columns like 'Date', 'Product ID', 'Quantity', and 'Revenue', and you simply want to combine them into one long list.

  • Joining Data (Looking Up): This is when you have two or more datasets that share a common piece of information (like a 'Customer ID' or 'Product SKU'), and you want to pull columns from one list into the other. For example, you might have a sales report with a 'Customer ID' and a separate customer contact list. You can "join" them to add the customer's name and email address to your sales report for easy reference.

We’ll cover the best techniques for both scenarios, starting with the simplest way to stack data.

Method 1: Appending Data with an Array Formula ({})

The fastest way to stack data from multiple tabs is by using an array formula, which is signaled by curly braces {}. This technique tells Google Sheets to treat multiple ranges of data as a single, virtual table. It's perfect for consolidating information that’s been split across different sheets, like monthly regional sales reports, weekly lead generation exports, or different ad platform performance data.

Let's imagine you have sales data for three months located on separate tabs named 'Jan_Sales', 'Feb_Sales', and 'Mar_Sales'. Each sheet has the same four columns: Order ID, Product, Quantity, and Revenue. Our goal is to create a 'Master_Sales' tab that combines all this data.

Step-by-Step Instructions:

1. Prepare Your Data

Ensure that the columns on each sheet are in the exact same order. The array formula stacks a range on top of another range, so if 'Revenue' is in column D on one sheet and column C on another, your final merged data will be a jumbled mess.

2. Create Your Master Sheet

Add a new tab in your Google Sheet and name it something like 'Master_Sales'. In this new sheet, copy the header row from one of your sales tabs and paste it into row 1. This ensures your final report has the correct column titles.

3. Write the Array Formula

In cell A2 of your 'Master_Sales' sheet, you’ll write a formula to pull in the data. The syntax is surprisingly simple. You wrap the ranges you want to stack in curly braces {} and separate them with a semicolon ,, which tells Google Sheets to stack them vertically.

Here’s the formula:

Let's break that down:

  • {...}: This tells Google Sheets we're creating a virtual array.

  • Jan_Sales!A2:D: This selects all the data from row 2 downwards in columns A through D on the 'Jan_Sales' sheet. Using 'D' without a row number makes it select all the way to the bottom.

  • ,: The comma is used to separate ranges horizontally, but in array syntax, when stacking vertically, a semicolon is typically used. However, in Google Sheets, within curly braces, semicolons , are used for vertical stacking, commas , are for horizontal concatenation. So, the correct syntax for stacking should be:

  • Feb_Sales!A2:D, Mar_Sales!A2:D: Repeat the process for other sheets.

Note: To stack ranges vertically, the separator should be ,.

Press Enter, and just like that, all your data from the three separate tabs will be neatly stacked in one place. Best of all, this is a dynamic formula. If you add a new sale to the 'Jan_Sales' sheet, it will automatically appear in your 'Master_Sales' sheet.

Pro Tip: Clean Up Blank Rows with QUERY

If your source sheets have blank rows scattered throughout, your merged data will include them. You can use the powerful QUERY function to wrap your array formula and automatically filter out these empty spaces. It's an easy way to keep your combined list tidy.

Modify the formula like this:

This adds a condition telling Google Sheets to only show rows where the first column (Col1) is not empty, effectively cleaning up your results.

Method 2: Joining Data with VLOOKUP

When you need to join data instead of just stacking it, VLOOKUP is the classic function many users turn to. It stands for 'Vertical Lookup' and works like searching for a name in a phone book. You give it a piece of information (the 'search key'), tell it where to look (the 'range'), and ask it to return a corresponding piece of information from another column.

Let's say you have one sheet named 'Transactions' with columns for SKU, Quantity, and Date. You have another sheet named 'Products' that contains the SKU and the full Product Name. You want to bring the Product Name into the 'Transactions' sheet next to its corresponding SKU.

An exact match is required here. A common unique identifier — like a product SKU, customer ID, or email address — is essential for this to work correctly.

Step-by-Step Instructions:

1. Prepare Your Sheets

Place your data on two separate tabs. The 'Transactions' tab will be where we add our formula. The 'Products' tab will serve as our lookup table. Crucially, in the lookup table ('Products'), the column containing the identifier you are searching for (the SKU) must be the first column in the range you specify.

2. Understand the VLOOKUP Syntax

The VLOOKUP formula has four parts:

  • search_key: The cell containing the value you want to look for (e.g., the SKU in your 'Transactions' sheet).

  • range: The data range where you want to search, including both the lookup column and the column with the value you want back. This is your 'Products' sheet.

  • index: The column number within the range that has the value you want to return. '1' would be the first column in your selected range, '2' the second, and so on.

  • is_sorted: This argument is usually set to FALSE to perform an exact match. It's the safest option 99% of the time.

3. Write the Formula

In the 'Transactions' sheet, click into the first empty cell where you want the Product Name to appear. Assuming your first SKU is in cell A2, you'd type:

Let's walk through it:

  • A2: Look for the value in cell A2 of the current sheet (our SKU).

  • Products!A:B: Go to the 'Products' sheet and search for that SKU in column A. Our lookup range includes column A (SKUs) and B (Product Names).

  • 2: If you find a match, give me the value from the second column of my range (which is column B, the Product Name).

  • FALSE: Only return a match if you find the exact SKU.

Once you press Enter, you can drag the fill handle (the small square in the corner of the cell) down to apply the formula to all your rows.

Method 3: The Superior Alternative - Joining with XLOOKUP

While VLOOKUP works, it has limitations. It can only look to the right, and inserting a new column in your source table can easily break your formulas. Meet XLOOKUP, Google's modern, more flexible replacement.

XLOOKUP does the same job as VLOOKUP but is more intuitive and powerful. You select the lookup column and the result column separately, so it doesn't matter where they are in relation to each other.

Using the same scenario with 'Transactions' and 'Products', here's how you'd do it with XLOOKUP.

Step-by-Step Instructions:

1. Understand the XLOOKUP Syntax

The basic XLOOKUP syntax is simpler:

  • search_key: The value you want to search for (cell A2 with the SKU).

  • lookup_range: The column where the SKU is located (column A in the 'Products' sheet).

  • result_range: The column with the information you want back (column B in the 'Products' sheet).

2. Write the Formula

In the same spot on your 'Transactions' sheet, you would type:

This is much easier to read and understand. You're explicitly telling Sheets: "Look for the value from A2 in column A of the Products sheet, and when you find it, give me the corresponding value from column B." It’s more resilient, easier to write, and should be your go-to function for joining data going forward.

A Few Final Tips for Perfect Merges

Regardless of which method you choose, keeping these best practices in mind will prevent frustrating errors and save you a lot of time debugging formulas.

  • Keep Data Clean and Consistent: This is the golden rule. Ensure your column headers are identical across sheets you're appending. For joining, make sure your unique identifiers (like 'SKU-123') don’t have trailing spaces or other typos, as they can cause lookups to fail.

  • Always Use a Unique Identifier: For joining data with VLOOKUP or XLOOKUP, you need a reliable key that appears in both datasets. This could be an Order ID, an email address, or a product SKU. Don't try to join on something non-unique like a first name.

  • Be Mindful of Data Formats: A common mistake is trying to match a number formatted as text with a number formatted as a number. Google Sheets often sees them as different values. Ensure your data types are consistent in your key columns.

  • Start Small, then Expand: When writing a new formula, check that it works on a single row before dragging it down to a thousand. This makes it easier to spot and fix any issues upfront.

Final Thoughts

Learning how to merge data transforms Google Sheets from a simple data entry tool into a powerful platform for analysis. Whether you’re stacking multiple reports with a simple array formula or enriching your sales data with key details using XLOOKUP, these techniques are fundamental for anyone looking to create clean, coherent, and centralized reports without spending hours manually manipulating data.

Mastering these formulas is a massive step forward, but the process of exporting CSVs from various platforms only scales so far. For instance, putting together your Google Analytics traffic data with your Shopify sales or HubSpot leads requires a constant cycle of exporting and importing. We built Graphed to cut out this manual step. It links directly to your various sales and marketing data sources, allowing you to ask questions in plain English, like "create a dashboard showing Shopify revenue by campaign." This allows you to blend data across platforms in seconds, building live dashboards without ever touching another VLOOKUP.