How to Consolidate Data in Google Sheets

Wrangling data from multiple tabs or separate Google Sheets is a manual, error-prone headache that keeps you from the actual analysis. Instead of spending hours copying and pasting, you can use built-in formulas and techniques to merge it all into one clean, primary dataset. This article will show you several methods to consolidate your data in Google Sheets, from the simplest formulas to more advanced, automated solutions.

Why Bother Consolidating Your Data?

Keeping data in separate tabs or files might seem organized at first, but it quickly becomes a bottleneck. When you need a high-level view - for example, combining monthly sales reports, weekly ad performance, or regional lead data - you're forced to manually stitch everything together. Consolidating your data into a single master sheet creates a "single source of truth." This makes it dramatically easier to sort, filter, create pivot tables, and build charts that give you a complete picture of your performance without having to click between a dozen different tabs.

First, A Quick Pre-Check: Preparing Your Data

Before you can effectively merge your data, you need to ensure it's structured consistently across all your sources. This is the single most important step. If your column layouts don't match, your formulas will break or produce jumbled results.

Aim for this simple rule: every sheet you want to combine should have the exact same columns in the exact same order.

• Consistent Headers: "Date" in one column should be "Date" in all of them, not "Day" or "Date of Sale."
• Consistent Order: If Sheet1 is Date | Campaign | Clicks | Spend, then Sheet2, Sheet3, and so on should follow that exact structure.
• Data Types: Make sure a column that contains numbers in one sheet doesn't contain text in another.

Taking five minutes to standardize your tabs will save you hours of troubleshooting later.

Method 1: The Simple Combination Using {} (Array Literals)

If your goal is to merge data from multiple tabs within the same Google Sheet, the easiest way is by using curly braces: {}. In Google Sheets, this syntax allows you to build a virtual array by stacking ranges on top of each other.

Let’s say you have three tabs: Jan_Sales, Feb_Sales, and Mar_Sales. Each sheet has data in columns A through D.

Here’s how to combine them:

Step-by-Step Instructions:

1. Create a new tab and name it something like "Master Sales Report."
2. In cell A1 of your master sheet, copy and paste the headers from one of your sales tabs.
3. In cell A2, enter the following formula:

={Jan_Sales!A2:D, Feb_Sales!A2:D, Mar_Sales!A2:D}

How This Formula Works:

• The {} tells Google Sheets you are creating a custom array of data.
• Jan_Sales!A2:D selects all the data from the January sheet, starting from row 2 to exclude the headers. We've defined column D as the end but left the row number blank, so it pulls all rows in that range.
• The comma (,) is the crucial part. It tells Google Sheets to stack the next range vertically (i.e., underneath) the previous one. If you used a semicolon instead, it would try to stack them horizontally.

Instantly, all the data from your three tabs will appear in your new master sheet. If you add a new row to any of the source tabs, this master sheet will update automatically. This method is fast, dynamic, and perfect for consolidating tabs within a single workbook.

Method 2: Merging Data from Different Spreadsheets with IMPORTRANGE

What if your data lives in entirely separate Google Sheets files? That's where the IMPORTRANGE function comes in. This formula pulls data from one spreadsheet into another.

The syntax is: IMPORTRANGE("spreadsheet_url", "sheet_name!range")

Let's imagine you have a sales report for North_Region and another for South_Region, each in its own file.

Step-by-Step Instructions:

1. Create a new spreadsheet that will serve as your master report.
2. The first time you use IMPORTRANGE to pull data from a new source, you'll need to grant permission. In cell A1 of your new sheet, type a simple IMPORTRANGE formula just to connect them:

=IMPORTRANGE("URL_of_your_North_Region_Sheet", "Sales Data!A1")

1. After hitting enter, you'll see a #REF! error. Hover over the cell, and a blue button will appear asking you to "Allow access." Click it. Repeat this process for the South Region sheet's URL in a different cell to connect it, too. Once access is granted, you don't have to do it again for those files.
2. Now, we can combine IMPORTRANGE with the curly brace {} method from before. In your master sheet, enter this formula:

={
IMPORTRANGE("URL_of_your_North_Region_Sheet", "Sales Data!A2:E"),
IMPORTRANGE("URL_of_your_South_Region_Sheet", "Sales Data!A2:E")
}

This formula tells Google Sheets to:

• Go to the North Region sheet and import the range Sales Data!A2:E.
• Then, stack the data from the South Region sheet (Sales Data!A2:E) directly below it.

You now have a live, consolidated view of two separate files in one place. Any changes in the source files will be reflected in your master sheet automatically.

Method 3: The Powerhouse QUERY Function

Combining data is one thing, but what if you also want to filter or rearrange it at the same time? Enter the QUERY function. It's arguably the most powerful function in Google Sheets, allowing you to use SQL-like commands to manipulate your data.

We can wrap our previous formulas inside a QUERY to gain incredible control.

Example 1: Filtering Out Blank Rows

Sometimes when you stack data, you end up with empty rows. A QUERY can clean this up easily.

=QUERY(
  {Jan_Sales!A2:D, Feb_Sales!A2:D, Mar_Sales!A2:D},
  "SELECT * WHERE Col1 IS NOT NULL"
)

Here's the breakdown:

• The first part of the formula is our same curly brace array from Method 1. This is the data source.
• The second part is the query itself, enclosed in quotes: "SELECT * WHERE Col1 IS NOT NULL".
• SELECT * means "select all columns."
• WHERE Col1 IS NOT NULL is the filter. It tells Google Sheets to only return rows where the first column (Col1) is not empty. When using QUERY on a manufactured range (like one made with {}), you must refer to columns as Col1, Col2, Col3, etc., instead of A, B, C.

Example 2: Sorting the Consolidated Data

Want to combine and sort all your data by date? Add an ORDER BY clause.

=QUERY(
  {Jan_Sales!A2:D, Feb_Sales!A2:D, Mar_Sales!A2:D},
  "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1 ASC"
)

Assuming your date is in the first column (Col1), this formula will combine the data from all three tabs, remove any blank rows, and sort the entire dataset by date in ascending order (ASC). QUERY gives you a way to consolidate, clean, and organize your information in a single step.

Method 4: Automating Consolidation with Google Apps Script

If you have a complex workflow, a large number of sheets to consolidate, or need the process to run on a schedule without you even opening the sheet, Google Apps Script is the ultimate solution.

Apps Script is a JavaScript-based platform that allows you to write custom functions and automate tasks across Google Workspace. This method requires a bit of code, but you don't need to be a developer to use a simple script.

Example Script to Combine All Tabs:

This script will loop through all the tabs in your current spreadsheet, copy the data from each, and paste it into a tab named "Master".

1. Open your Google Sheet and go to Extensions > Apps Script.
2. Delete any placeholder code and paste the following script into the editor:

function consolidateSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
  const masterSheetName = "Master", // The sheet to copy data TO
  const ss_sheets = ss.getSheets(),

  let masterSheet = ss.getSheetByName(masterSheetName),
  if (!masterSheet) {
    masterSheet = ss.insertSheet(masterSheetName, 0),
  }

  // Clear the master sheet except for the header row
  masterSheet.getRange(2, 1, masterSheet.getLastRow(), masterSheet.getLastColumn()).clearContent(),

  ss_sheets.forEach(function(sheet) {
    const sheetName = sheet.getName(),

    // Check if it's a data sheet and not the master sheet
    if (sheetName !== masterSheetName && sheet.getLastRow() > 1) { 
      // Get data range, excluding headers
      const sourceRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()),
      const sourceValues = sourceRange.getValues(),

      // Paste data into the next available row in the Master sheet
      masterSheet.getRange(masterSheet.getLastRow() + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues),
    }
  }),
}

1. Use the Save icon, then select the function consolidateSheets from the dropdown and click "Run."
2. The first time, you will have to authorize the script to access your spreadsheet data. Follow the prompts to allow it.

When it finishes, your "Master" tab will contain all the data from the other tabs. You can set this script to run automatically every day using a "trigger," creating a completely automated consolidation workflow.

Final Thoughts

No one should have to spend their week manually copying rows from one spreadsheet to another. From a simple set of curly braces to the powerful QUERY function or a completely automated Apps Script, Google Sheets gives you plenty of tools to centralize your scattered data. Start with the method that fits your needs and take back the hours you've been losing to manual data drudgery.

Constantly merging Sheets is usually a sign that your data lives in too many different apps. We created Graphed to solve this bigger problem by eliminating the spreadsheet middleman entirely. It connects directly to your data sources - like Google Analytics, Shopify, Facebook Ads, or HubSpot - so instead of exporting CSVs and wrestling with IMPORTRANGE, you can just ask in plain English, "create a dashboard showing ad spend and revenue from all platforms this month." We give you the consolidated report instantly, with live data that's always up to date.