Combine Excel Worksheets into One: Practical Methods

How-to basics · Updated November 22, 2026

To combine Excel worksheets into one, use Power Query to append the sheets when you want a refreshable result, or stack matching ranges with VSTACK in Microsoft 365. For a one-time merge, copy and paste the rows into a new worksheet. The best method depends on whether the sheets have the same columns, how often the data changes, and whether you want to keep the result linked to its sources.

What “combine” means in Excel

People use “combine worksheets” to mean a few different things. Most commonly, they want to put records from several sheets one after another in a single list. For example, January, February, and March sales sheets might all have Date, Product, and Amount columns. Appending them creates one longer table with the same columns.

That is different from joining data side by side. A join matches rows using a shared field, such as a customer ID, and brings columns from another list into each matching row. It is also different from summarizing values from several sheets into totals. Power Query can append or merge tables; Excel’s Consolidate command is intended for summarizing ranges, not simply stacking their rows.

Before choosing a method, check that each sheet has a clear header row and that the records are laid out in columns. Avoid blank rows within a data block, merged cells in the data, and different labels for the same field. These details affect how reliably Excel can recognize and combine the information.

Choose a method that fits the job

  • Power Query: Best for repeatable combinations, larger lists, or data that will be refreshed. It can append sheets or tables and keeps the source data separate from the combined output.
  • VSTACK: Useful when you have Microsoft 365 or Excel 2024, matching columns, and want a formula-driven result that updates as source cells change.
  • Copy and paste: A straightforward choice for a small, one-time task. It is quick, but does not automatically update when the original sheets change.
  • Consolidate: Consider this when the goal is a summary, such as adding corresponding monthly totals, rather than creating one long record list.

For recurring work, Power Query is usually the most maintainable choice. For a short-lived workbook with a few small sheets, manual copying may take less time. If you use VSTACK, make sure your Excel version supports it and that the combined result has enough empty cells to spill into.

Append worksheets with Power Query

Power Query is built into Excel for Microsoft 365 on Windows. It imports, reshapes, and combines data, then lets you refresh the output when the sources change. For worksheets in the same workbook, first turn each data range into an Excel Table so that the source has a defined name and header row.

  1. On each source sheet, click inside the data and choose Insert > Table. Confirm that My table has headers is selected, then choose OK. Give each table a useful name from Table Design > Table Name, such as Sales_Jan or Sales_Feb. Table names must be unique within the workbook.
  2. Click inside the first table and choose Data > From Table/Range. The Power Query Editor opens. Check that the column names and data types look right, then use Home > Close & Load > Close & Load To… and select Only Create Connection. Repeat for the other source tables.
  3. Open the Power Query Editor for one of the queries. Choose Home > Append Queries > Append Queries as New. Select Three or more tables if you have at least three sources, then add the queries to the list of tables to append. For two sources, choose the two-table option and select both queries.
  4. Give the new query a clear name, such as CombinedSales. Check the preview for missing columns, unexpected blank rows, or incorrect data types. Then choose Home > Close & Load to load the result to a worksheet.

Appending stacks rows; it does not match records by an ID. Power Query aligns fields by column name, not by their left-to-right position. If one table uses Customer and another uses Customer Name, Excel treats them as different fields. Rename the columns consistently before appending if they represent the same information.

When a source table changes, use Data > Refresh All to update the query result. Refreshing does not mean every possible change to the query source is automatically included: for example, rows entered outside the defined source table are not part of that table. Add new records inside the table, or expand the source range, so the query can see them.

Stack ranges with VSTACK

VSTACK places arrays or ranges beneath one another and returns a dynamic array. It is available in Microsoft 365 and Excel 2024; it is not available in older perpetual versions such as Excel 2019. The source ranges should have the same columns in the same order, and you normally include the header only once.

Assume the January, February, and March sheets each have headers in A1:D1 and exactly three data rows in A2:D4. Put this formula in an empty worksheet cell outside an Excel Table:

=VSTACK(Jan!A1:D4,Feb!A2:D4,Mar!A2:D4)

The result is a four-column spill range containing one header row and nine data rows: three records from each month. The formula includes January’s header and starts the other ranges on row 2 to avoid repeating it. If a sheet name contains spaces, put single quotation marks around the sheet name and its exclamation mark, for example 'January Sales'!A1:D4.

For a changing number of records, you can make each source range an Excel Table and revise the formula to use table references, or use Power Query to avoid maintaining fixed row limits. A fixed reference ending at row 100 will not include a new record entered on row 101. VSTACK’s output is a spill range: keep the cells beneath and beside the formula clear. If something blocks the result, Excel displays a #SPILL! error. The formula result itself cannot be edited cell by cell; edit the source or replace the result with pasted values if you need a static, editable copy.

Combine sheets by copying and pasting

For a small one-time merge, create a blank worksheet and copy the header row and records from the first sheet. On each remaining sheet, copy only the records, not the repeated header, and paste them directly below the last used row in the destination. Check the first and last rows of every block after pasting, especially if the source ranges contain blank lines or filters.

On Windows, Ctrl+C copies and Ctrl+V pastes. On Mac, use Command+C and Command+V. If you want to keep the displayed results but not formulas linked to other cells, use Paste Special and choose Values. The exact Paste Special controls can vary slightly by platform and ribbon layout; the standard Paste options are available from the Home tab. Copying whole worksheets with Move or Copy creates separate sheets in a workbook; it does not combine their records into one list.

Manual copying is easy to understand, but it creates a snapshot. If a source sheet changes later, the combined copy will not change with it. It can also introduce formatting inconsistencies, formulas that refer to unexpected cells, or accidental duplicate headers. Keep a copy of the original workbook until you have checked the merged result.

When to use Consolidate or a join instead

Use Data > Consolidate when you want a summary of corresponding values, such as totals for the same categories across several ranges. The command can consolidate by position or by matching row and column labels, depending on how the ranges are arranged. It does not create a straightforward list of all source records, so it is not the right tool for appending monthly transaction sheets.

If you need to attach details from one list to records in another, that is a join or lookup task. For example, you might add a department name to a staff list using employee IDs. Power Query’s Merge Queries matches tables using selected columns; this is distinct from Append Queries, which adds rows. Excel lookup formulas are another option when you need a value from a related table rather than a combined record list.

Common problems and how to prevent them

  • Repeated headers in the output: Remove the header row from all but the first source range or table. If the repeated headings become records, filter them out in Power Query.
  • Columns appear blank or split: Check spelling, spaces, and punctuation in header names. Power Query treats differently named columns as separate fields, even when the headings look similar.
  • Dates or numbers change type: Check Power Query’s detected data types and the source cells. A column containing both numbers and text may not behave like a clean numeric column. Standardize the source values, then check the query preview again.
  • Some records are missing: Confirm that the source tables include all rows and that filters have not led you to overlook records. With a formula, check the referenced range limits; with Power Query, refresh after editing the source.
  • Duplicate records appear: Appending preserves duplicates. Decide whether repeated rows are legitimate before removing them. In Power Query, use Home > Remove Rows > Remove Duplicates only when duplicate rows should truly be excluded.
  • Formatting does not carry over: Power Query combines data values and structure, not every source sheet’s cell formatting. Apply formatting to the output worksheet separately.

It is also sensible to verify the result with a row count and a few spot checks. Compare the combined record count with the total number of source records, excluding headers. Check a distinctive value from each source sheet and confirm that important columns, such as dates and IDs, survived the operation as intended.

Refresh, sharing, and preserving the source

A Power Query output is a separate result table; it does not merge the source worksheets destructively. That makes it easier to review the combined data while retaining the originals. However, someone opening the workbook may need access to the same source data for a refresh. If the query points to an external file or folder, moving or renaming that source can break the connection until its path is updated.

Before sharing a workbook, consider whether recipients should refresh the query or only read the existing output. Keep the source sheets or source files available if refreshes are expected, and document what each table represents. If the data is sensitive, check the workbook’s sharing permissions and the location of any external source files. Avoid sending a workbook with data the recipient is not meant to see simply because it is hidden on another sheet.

Frequently Asked Questions

Can I combine Excel worksheets with different columns?

Yes. Power Query can append tables with different column names, but fields that do not exist in a particular source appear blank for those rows. For fields that mean the same thing, make the headings consistent first; otherwise, one logical field may be split into multiple columns.

Will the combined worksheet update automatically?

A Power Query result can be refreshed with Data > Refresh All after its sources change. A VSTACK formula recalculates when its referenced cells change, subject to the range it references. A copy-and-paste result is static and must be updated manually.

Does appending worksheets remove duplicate rows?

No. Power Query append, VSTACK, and copy-and-paste preserve repeated records. Remove duplicates only after deciding which columns define a duplicate and confirming that repeated entries are not valid transactions or events.

Can I combine worksheets from several workbooks?

Yes. Power Query can import data from other workbooks. For files with a consistent layout in one folder, use Data > Get Data > From File > From Folder, then follow the combine prompts and check the sample transformation and output. Keep the files in the expected location for future refreshes.

Can I combine sheets without repeating the column headings?

Yes. In Power Query, append tables that each have a recognized header row; the headers define the fields rather than becoming records. With manual copying or VSTACK, include the header from the first sheet and start the remaining source ranges on the first data row.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.