How to Collate Data in Excel

Bringing data together from multiple Excel sheets can quickly turn into a time-consuming headache. Whether you're combining monthly sales figures, weekly project updates, or campaign performance metrics, the copy-paste routine gets old fast. This guide walks you through several methods to collate data in Excel, from simple techniques for one-off tasks to powerful, automated solutions for your recurring reporting needs.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Does It Mean to Collate Data?

In the context of Excel, "collating data" simply means gathering and combining information from different sources into a single, unified list or table. These sources might be multiple tabs within the same workbook or even separate Excel files.

Imagine these common scenarios:

• You have sales data for different regions (North, South, East, West) on separate worksheets and need to create a single master report.
• Each member of your team tracks their project tasks in individual spreadsheets, and you need to combine them for an overall status update.
• You download a marketing performance report every week from a specific platform, and you want to merge each new report into one historical dataset for trend analysis.

The goal is to move from scattered information to a single, consolidated dataset that you can easily analyze, pivot, and chart.

Before You Begin: The Importance of a Consistent Structure

Before you try any collation method, you need to prepare your data. No technique, no matter how advanced, can effectively combine data that's messy and inconsistent. Taking a few minutes to standardize your source files will save you hours of frustration.

Focus on these two key rules:

1. Your Column Headers Must Match Exactly

Every data source you plan to merge should have the exact same column headers, in the same order. This is the most critical step. Excel uses these headers to understand how to stack your data correctly.

• Spelling matters: "SalesRep" is different from "Sales Rep".
• Case matters: "Revenue" is different from "revenue".
• Extra spaces matter: "Date " (with a trailing space) is different from "Date".

Audit each of your source sheets or files and make sure the headers are identical across the board. The contents of the rows will be different, but the column structure needs to be uniform.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Format Your Data as a Table

Formatting your data ranges as official Excel Tables makes any consolidation task much smoother. Tables automatically expand to include new rows, meaning you won't have to constantly update your formula ranges or data source settings.

To create a Table:

1. Click any cell inside your data range.
2. Press Ctrl + T on your keyboard (or go to Insert > Table).
3. Ensure the "My table has headers" box is checked.
4. Click OK.

Do this for the data setup on each of your source sheets. It's a small step that brings big benefits in flexibility and scalability.

Method 1: The Quick and Dirty Manual Copy-Paste

Let's start with the method everyone knows. Manually copying and pasting data from one sheet to another is straightforward, but it's also the most time-consuming and error-prone.

How It Works:

1. Create a new "Master" or "Consolidated" worksheet.
2. Copy the headers from one of your source sheets and paste them into the first row of your master sheet.
3. Go to your first data source sheet, select all the data rows (not the headers), and copy them.
4. Paste the data into the master sheet, directly under the headers.
5. Repeat this process for every other source sheet, pasting each new data block at the bottom of the previous one.

Best for: One-time consolidation tasks with only 2-3 small data sources where you know the data won't change or need updating.

Drawbacks: Highly inefficient for recurring reports, easily leads to copy-paste errors, and is impossible to scale if you have many data sources.

Method 2: Using the Consolidate Feature

Did you know Excel has a built-in feature designed specifically for this purpose? The Consolidate tool is a fantastic middle-ground that summarizes and combines data without complex formulas.

How It Works:

Imagine you have monthly sales worksheets (Jan, Feb, Mar) with the same product lists, and you want to summarize total sales on a new "Q1 Summary" sheet.

1. Click on the cell where you want your consolidated data to begin on your summary sheet.
2. Go to the Data tab and click on Consolidate in the Data Tools group.
3. In the popup window, choose the function you want to use. "Sum" is the most common, but you could also use "Count," "Average," etc.
4. Click the icon in the Reference field, navigate to your first data sheet (e.g., "Jan"), and select the entire data range, including headers. Click Add.
5. Repeat the process for all other data sources ("Feb," "Mar," etc.), adding each one to the 'All references' list.
6. Under "Use labels in," check both Top row and Left column. This tells Excel to use your headers to match and align the data.
7. (Optional but powerful): Check the Create links to source data box. This creates a dynamic link, so if a number in a source sheet changes, your consolidated summary will update automatically. It also creates a helpful outline view.
8. Click OK.

Excel will instantly generate a summary table combining the values from all your specified sources.

Best for: Summarizing numerical data across multiple sheets that have an identical or similar layout.

Drawbacks: It's best for aggregation (summing, averaging) rather than stacking raw data rows into one long list. If you need a raw transaction log, this isn't the right tool.

Method 3: Stacking Data With Formulas (Advanced)

For users comfortable with formulas, newer functions in Microsoft 365 provide dynamic ways to stack data. Functions like VSTACK are built for this.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

How It Works with VSTACK:

The VSTACK function allows you to append arrays vertically. In plain English, it stacks ranges on top of one another.

Assuming your data on sheets "North," "South," and "East" are formatted as tables named Table_North, Table_South, and Table_East, the formula is incredibly simple. In your master sheet, just type:

=VSTACK(Table_North, Table_South, Table_East)

That one formula will pull all the data from those three tables and stack it. If you add new data to any of the source tables, the master list will update instantly.

Note: VSTACK is only available in Microsoft 365 versions of Excel. If you have an older version, this function won't be available.

Best for: Microsoft 365 users who want a dynamic, formula-driven way to stack raw data from consistently structured tables.

Drawbacks: The formula can become long if you have many sources, and you have to remember to update it if you add a new sheet/table to your workbook.

Method 4: The Best Way to Collate Data in Excel: Power Query

For any collation task that you'll perform more than once - especially if it involves pulling data from separate files - Power Query is by far the superior method. It’s Excel’s dedicated data transformation tool, and it automates the entire process in a repeatable way.

Let's use the most common scenario: you have multiple data files (CSVs or Excel workbooks) in a single folder, and you want to combine them all.

Step 1: Get Data from a Folder

1. Open a blank Excel workbook.
2. Go to the Data tab.
3. Click Get Data > From File > From Folder.
4. Browse to and select the folder that contains all your source files.

Step 2: Combine the Files

1. A navigator window will appear, showing you a list of the files in that folder.
2. At the bottom of the window, click the dropdown next to 'Combine' and select Combine & Transform Data.
3. Power Query will then ask you to select a "sample file" to use as a template. Just select the first file from the dropdown and the sheet/table that contains your data.
4. Click OK.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Transform and Load

The Power Query editor will open. Here’s where the magic happens. Power Query has automatically stacked the data from every file in your folder into one long table. It even added a "Source.Name" column, so you can see which row of data came from which file!

In this editor, you could perform other data cleaning steps (like removing columns, changing data types, etc.), but if your source data is already clean and consistent, you might not even need to do anything else. When you’re ready, click the Close & Load button in the top-left corner.

Step 4: The Payoff: Refreshing Data

Your combined, collated data is now loaded into a new worksheet as a formatted table. But here’s the best part: the process is automated.

Next month, when you get a new data file, simply save it into the same folder. Then, go to your master data table in Excel, right-click anywhere inside it, and hit Refresh. Power Query will re-run the entire process in seconds and automatically append the new file's data to your master list. No more copy-paste required.

Best for: Pretty much everyone, but especially for recurring reports, combining multiple files from a folder, and creating automated, repeatable data collation workflows.

Final Thoughts

From a basic copy-paste to the automation powerhouse of Power Query, Excel provides a toolkit for any data collation scenario. For simple, one-time jobs, a manual approach works fine. For anything you have to repeat, learning to use Power Query will save you an incredible amount of time and eliminate manual errors for good.

Of course, collating data in Excel is just one piece of the reporting puzzle. The real challenge often comes from pulling that data down from a dozen different marketing and sales platforms before it even gets to a spreadsheet. We built Graphed because we believe you shouldn't have to be a data wizard to understand your business. Instead of manually collating CSVs from Google Analytics, Shopify, and Facebook Ads, we let you connect those AIs and ask for a report in plain English, getting a real-time answer in seconds.