How to Stack Data in Excel

Combining data from different lists or tables into one master list is a task every spreadsheet user faces. Stacking data is essential, whether you're merging monthly sales reports, consolidating regional lead lists, or combining exports from different platforms. This guide will walk you through four methods for stacking data in Excel, from a simple manual technique to powerful, automated solutions.

First, a Quick Note on Your Data Structure

For any stacking method to work smoothly, your data should be structured consistently. Aim for the following:

• Each table or range should have the same number of columns.
• The column headers should be identical and in the same order across all your tables.

If your columns aren't perfectly aligned, don’t worry! One of the methods below, Power Query, is brilliant at handling inconsistencies. For now, just know that clean, consistent data makes everything easier.

Method 1: Manual Copy and Paste (The Quick & Dirty Approach)

The most straightforward method is to simply copy and paste your data ranges one after another. If you only need to combine a couple of tables once, this works just fine.

Step-by-Step Instructions:

1. Click and drag to select the data in your second table. Be sure not to include the header row.
2. Press Ctrl + C (or Cmd + C on a Mac) to copy the data.
3. Navigate to the first table and click on the first empty cell directly below your last row of data.
4. Press Ctrl + V (or Cmd + V) to paste the data.
5. Repeat this process for any other tables you need to stack.

Pros:

• Requires no special functions or features. It's fast and easy for a one-time task.

Cons:

• It's completely static. If the source data changes, you have to repeat the entire process manually.
• Highly prone to human error - it's easy to miss a row, copy headers by mistake, or paste into the wrong spot.
• Extremely inefficient if you need to perform this task regularly (e.g., for weekly or monthly reporting).

This method is fine in a pinch, but for anything you might have to repeat, you'll save yourself a world of headaches by using a more automated approach.

Method 2: Using Power Query (The Most Powerful & Flexible Option)

Power Query (also known as "Get & Transform Data" in recent Excel versions) is a data connection and preparation tool that is perfect for this. It might seem intimidating at first, but it is the most reliable way to stack data, especially if you're consolidating data on a regular basis. The best part? Your actions are recorded as steps, creating an automated workflow that can be refreshed with a single click.

When to use Power Query:

• You need to combine data from multiple tables, sheets, or even different Excel files.
• Your source data updates often, and you need a way to refresh your stacked data automatically.
• Your column names are the same, but might be in a different order across tables (Power Query handles this flawlessly).

Example: Stacking Data from Multiple Tables in the Same Workbook

Let's say you have two Excel Tables named Sales_Q1 and Sales_Q2.

1. Load the First Table into Power Query: Select any cell inside your Sales_Q1 table. Go to the Data tab and click on From Table/Range. This will open the Power Query Editor.
2. Close and Load as a Connection: You don't need to change anything yet. In the top-left, click the dropdown under Close & Load and select Close & Load To.... In the dialog box, choose Only Create Connection and click OK. You'll now see your query in the "Queries & Connections" pane on the right.
3. Repeat for the Second Table: Follow steps 1 and 2 for your Sales_Q2 table. You should now have two connections in the Queries & Connections pane.
4. Append the Queries: Go back to the Data tab. Click Get Data → Combine Queries → Append.
5. Choose the Tables to Stack: An "Append" window will pop up. Since we have two tables, select that option. Choose Sales_Q1 as your primary table and Sales_Q2 as the table to append. Click OK.
6. Load Your Final Stacked Table: Power Query will open a new window showing your combined data. If everything looks good, click Close & Load.

Excel will create a new worksheet containing your stacked table. If you want to stack more than two tables, you can select "Three or more tables" in the Append window and add all the necessary tables to the list.

Now for the best part: if you add new rows to Sales_Q1 or Sales_Q2, all you have to do is go to the stacked data sheet, right-click anywhere in the green table, and select Refresh. Your new data will appear instantly!

Method 3: Using the VSTACK Function (Modern & Dynamic)

If you have Microsoft 365 or Excel 2021, you have access to a new generation of dynamic array functions. The best one for this job is VSTACK, which is designed specifically for vertically stacking arrays or ranges.

Step-by-step with VSTACK:

Using the same Sales_Q1 and Sales_Q2 tables from our last example, the formula is shockingly simple.

1. Click on any blank cell where you want your stacked data to start.
2. Type the following formula:

=VSTACK(Sales_Q1, Sales_Q2)

1. Press Enter. That's it!

Excel will "spill" the results from both tables into one continuous range. Because we referenced the entire table (including headers), this formula stacks everything, creating a header section inside the dataset, which is probably not what you want.

A more practical formula stacks a header row first, followed by just the data from each table. To do this, you can specify the data portion of the table using [#Data] and the headers using [#Headers].

A Better VSTACK Formula:

=VSTACK(Sales_Q1[#Headers], Sales_Q1[#Data], Sales_Q2[#Data])

This formula does a few things:

• Sales_Q1[#Headers]: Pulls the header row from the first table to use as the header for our final stacked table.
• Sales_Q1[#Data]: Pulls only the data rows (no headers) from the first table.
• Sales_Q2[#Data]: Pulls only the data rows from the second table.

Pros:

• It's fully dynamic. Any changes or additions to the source tables are reflected instantly.
• The formula is very simple and easy to understand.

Cons:

• Only available in recent versions of Excel (Microsoft 365, Excel 2021).
• Requires the column structure to be in the exact same order for both tables. If a column is out of place, the data will be stacked in the wrong column.

Method 4: Using VBA (The Coder's Approach)

For those comfortable with macros and the Visual Basic for Applications (VBA) editor, you can write a script to automate the copy-and-paste process. This is powerful for highly custom workflows but is less flexible and more complex to maintain than Power Query.

Here is a sample macro you could use to consolidate all data from every worksheet onto a single "Master" sheet.

Sample VBA Code:

Sub StackAllSheets()

'Declare variables
Dim ws As Worksheet
Dim MasterSheet As Worksheet
Dim lastRow As Long
Dim PasteRange As Range

'Turn off screen updating to make the macro run faster
Application.ScreenUpdating = False

'Create a "Master" sheet if it doesn't already exist
On Error Resume Next
Set MasterSheet = ThisWorkbook.Worksheets("Master")
On Error GoTo 0
If MasterSheet Is Nothing Then
    Set MasterSheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    MasterSheet.Name = "Master"
End If

'Clear any old data on the Master sheet
MasterSheet.Cells.Clear

'Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
    'We don't want to copy data from the master sheet itself
    If ws.Name <> "Master" Then
        
        'Find the last row with data in the Master sheet's column A
        lastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
        
        'Determine where to paste the new data
        Set PasteRange = MasterSheet.Cells(lastRow + 1, "A")
        
        'Copy headers only on the first run
        If MasterSheet.Range("A1").Value = "" Then
            ws.Rows(1).Copy MasterSheet.Rows(1)
            ws.Range("A2:Z" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Copy PasteRange
        Else
            'Copy data without headers
             ws.Range("A2:Z" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Copy PasteRange
        End If
    End If
Next ws

'Turn screen updating back on
Application.ScreenUpdating = True

MsgBox "Data has been stacked onto the Master sheet."

End Sub

To use this code:

• Press Alt + F11 to open the VBA Editor.
• Go to Insert → Module and paste the code into the window.
• Close the editor. Press Alt + F8, select StackAllSheets, and click Run.

This script goes a long way, but it requires that your data always starts in column A and assumes the tables have the same column layout. For any non-standard scenarios, you would need to modify the code.

Final Thoughts

Learning how to properly stack data in Excel is a fundamental skill that frees you from tedious, error-prone manual work. While a quick copy-paste might solve a one-off problem, adopting tools like Power Query or a modern dynamic function like VSTACK unlocks reproducible and reliable reporting workflows that save you time and greatly improve your data accuracy.

Manually exporting CSVs and stacking them in spreadsheets is often just the first painstaking step in pulling together a report. What if you could permanently skip that data wrangling phase? This is where our platform, Graphed, comes in. We enable you to connect directly to platforms like Salesforce, Google Analytics, Shopify, and Facebook Ads, automatically bringing all your data into one unified space. From there, you can just ask questions in plain English - like "create a dashboard comparing Facebook spend to Shopify sales by campaign" - and get real-time, shareable dashboards built for you in seconds.