Combine Data From Multiple Sheets in Excel

To combine data from multiple sheets in Excel, you have four good options: the VSTACK function, Power Query's Append, the Consolidate tool, or plain copy and paste. Which one fits depends on whether you need every row or just totals, and whether the data keeps changing. This guide explains each method with a simple monthly example, so you can pick the right one quickly.

Which method should you use?#

Start with what the result should look like and how often the data changes:

The examples use two sheets, Jan and Feb, with the same layout. Each has headers in row 1:

On each sheet, the data sits in A1:C4. Keeping the same columns in the same order on every sheet makes every method below easier.

Method 1: Stack sheets with VSTACK#

VSTACK stacks ranges on top of each other and spills the result. On a new sheet, type:

=VSTACK(Jan!A2:C4,Feb!A2:C4)

The result is six rows: the three January orders, then the three February orders. Jan!A2:C4 means "cells A2:C4 on the sheet called Jan". If a sheet name has spaces, put it in single quotes: 'Feb 2024'!A2:C4. Change a number on the Jan sheet, and the combined list changes too.

A few useful variations:

• Add the headers: =VSTACK(Jan!A1:C1,Jan!A2:C4,Feb!A2:C4) puts one header row on top.
• Every sheet in a row of tabs: =VSTACK(Jan:Mar!A2:C50) takes A2:C50 from Jan, Mar and every sheet between them.
• Drop empty rows: using a long range like A2:C50 leaves blank rows, which show as 0. Filter them out:

=FILTER(VSTACK(Jan!A2:C50,Feb!A2:C50),VSTACK(Jan!A2:A50,Feb!A2:A50)<>"")

• Sort the result: wrap it in SORT, such as =SORT(VSTACK(Jan!A2:C4,Feb!A2:C4),3,-1) for the biggest amounts first.

VSTACK needs Microsoft 365 or Excel 2024. In Excel 2021 and older, it shows #NAME?.

Method 2: Append sheets with Power Query#

Power Query is a data tool built into Excel (Excel 2016 and later on Windows, and Microsoft 365 on Mac). It's the best choice when you combine many sheets every month.

1. On the Jan sheet, click in the data and press Ctrl+T (Mac: ⌘T) to make it a table. Do the same on Feb.
2. Click in the Jan table and choose Data > From Table/Range. In the Power Query window, choose Close & Load To > Only Create Connection. Repeat for Feb.
3. Go to Data > Get Data > Combine Queries > Append.
4. Choose Three or more tables if needed, add each query, and click OK.
5. Click Close & Load. Power Query puts the combined table on a new sheet.

Next month, add rows to the source tables and press Data > Refresh All. Power Query matches columns by their header names, so the headers must be spelled the same on every sheet.

Method 3: Total by label with Consolidate#

If you only need totals, such as units per product across months, Data > Consolidate adds matching labels together without listing every row.

1. On a new sheet, click the top-left cell of where the result should go.
2. Go to Data > Consolidate and choose Sum as the function.
3. In Reference, select the range on the first sheet, including labels, and click Add. Repeat for each sheet.
4. Tick Top row and Left column under Use labels in, then click OK.

Excel lists each label once with its total. Tick Create links to source data if you want the totals to update when the sheets change. For the same item in the same cell on every sheet, a 3D formula is simpler. See how to sum the same cell across sheets.

You can also total by label with formulas. With January items in A2:A5 and February items in D2:D5 (or on separate sheets), add one SUMIF per sheet:

=SUMIF(A2:A5,"Pens",B2:B5)+SUMIF(D2:D5,"Pens",E2:E5)

With 120 pens in January and 140 in February, the result is 260.

Method 4: Copy and paste#

For a one-time job, copying is fine. Copy the rows from Jan, paste them on a new sheet, then copy Feb's rows (without the header) and paste them below. Move between sheets with Ctrl+Page Down and Ctrl+Page Up (Mac: Option+→ and Option+←); the guide to switching sheets with the keyboard has more shortcuts. Remember the copy doesn't update when a source sheet changes.

Combining columns instead of rows#

Sometimes "combining sheets" means adding information from another sheet to each row, such as a customer's region from a Customers sheet. That's a lookup, not stacking. Use =XLOOKUP(B2,Customers!A:A,Customers!C:C) or VLOOKUP. The guide to VLOOKUP between two sheets walks through it.

Tips for clean combined data#

• Same headers, same order on every sheet. Power Query needs matching names; VSTACK needs matching column order.
• Add a Month column to each sheet before combining, so you still know where each row came from.
• No merged cells or total rows inside the ranges. They end up in the combined list.
• After combining, use filter buttons to check the result; see how to filter data in Excel.

Practise it#

Formulas that reach into other sheets are the base of every combining method. Practise worksheets and cross-sheet references in a live spreadsheet: refer to cells like Prices!B3, look up a price list on another tab and total across monthly tabs, with instant checks. Try the SUMIF exercise on this page first.