Yes. In Excel for Microsoft 365 on Windows, combine worksheets by copying their data into one worksheet, appending source tables with Power Query, or stacking matching tables with the VSTACK function. Use copy-and-paste for a one-time task, and Power Query when you expect to repeat the merge or refresh it later.
What combining Excel sheets means
An Excel workbook can contain several worksheets, each shown as a tab at the bottom of the window. Combining sheets usually means collecting their rows into one list on a single worksheet. For example, you might put January, February, and March sales records together in one table so you can filter or summarize them as a whole.
This is different from combining workbooks, which are separate Excel files, and it is different from summarizing figures across sheets. A combined list retains the individual records; a summary might show only totals by category. Decide which result you need before choosing a method.
For a clean row-by-row merge, the source sheets should have the same columns in the same order. If one sheet has Customer, Date, and Amount while another has Date, Customer, and Amount, stacking the rows without aligning the fields can put dates under the wrong heading. Matching headers and compatible data types make the merge much safer.
Choose the right method
- Copy and paste: Best for a small, one-time merge when the sheets have similar layouts and you do not need automatic updates.
- Power Query: Best for larger or recurring merges. It can append tables, preserve a repeatable transformation process, and refresh the combined output when the source data changes.
- VSTACK: Best when the source data is already arranged consistently and you want a formula-driven result that updates as source tables change. It requires Microsoft 365 or Excel 2024.
- Consolidate: Best for calculating a summary, such as a total or average from corresponding ranges. It does not append every source row into one list.
There is no need to delete or move the original worksheets. Keeping them intact makes it easier to check the result and repeat the process.
Combine sheets manually with copy and paste
Manual copying is straightforward when there are only a few sheets and the data is manageable. First create a blank worksheet for the combined list. On the first source sheet, select the header row and all the data rows, copy them, and paste them into the new worksheet. On each following sheet, select only the data rows, not the repeated header, and paste them directly below the last combined row.
- Check that each sheet uses the same column order and that each column means the same thing.
- Copy the headers and data from the first sheet to the destination worksheet.
- Copy only the data rows from each remaining sheet and paste them below the existing records.
- Compare the number of source records with the number of combined records, and check the first and last rows from each sheet.
- Select the combined range and use Home > Format as Table if you want filter buttons and a clearly defined data range.
Copying values is often preferable when you need a fixed snapshot and do not want formulas from the source sheets to recalculate in the destination. Use Paste Special if you need a particular result: Home > Paste > Paste Values keeps displayed results without carrying over the original formulas. If you paste normally, Excel may bring formulas, formatting, or other cell properties along with the values.
Manual copying is easy to get wrong when a source sheet has a different number of columns, extra title rows, or new records added later. It also does not create a link that automatically brings future source rows into the combined list.
Append worksheets with Power Query
Power Query is Excel’s built-in tool for importing and shaping data. Appending means placing the rows from one query below the rows from another. It is a practical option for a merge you will repeat, because you can refresh the output rather than copy the records again.
For worksheets in the same workbook, first turn each source range into an Excel table. Select a range and choose Insert > Table, or Home > Format as Table, then confirm whether the table has headers. Give each table a useful name from Table Design > Table Name, such as Sales_Jan and Sales_Feb. Table names should be unique.
- Select a cell in the first source table and choose Data > From Table/Range. In the Power Query Editor, check that the headers and data types are correct.
- Choose Home > Close & Load > Close & Load To, then select Only Create Connection. Repeat for each source table.
- Open a query in the Power Query Editor and choose Home > Append Queries > Append Queries as New.
- Choose Two tables or Three or more tables, as appropriate, and add the source queries to the list. Select OK.
- Review the appended preview, then choose Home > Close & Load to place the result on a new worksheet.
Power Query matches fields by column name when appending queries, rather than relying only on their positions. If one source is missing a column, the output can contain blank values for that field in those rows. Confirm that similar columns have consistent names, and inspect the preview before loading. When source tables change, use Data > Refresh All to update the query output. New rows added inside a source table are generally included on refresh; data pasted outside the table may not be.
For separate workbooks, Power Query can also import data from files, including through Data > Get Data > From File > From Excel Workbook. If the files share a consistent structure and are stored together, a folder-based import can be more convenient than opening and copying each file. Menu details can vary slightly between Excel updates, but the Power Query Editor provides the append operation.
Use VSTACK for a formula-based merge
VSTACK places arrays or ranges one beneath another and returns a spilled result. It is available in Microsoft 365 and Excel 2024. Convert each source range to an Excel table first, and make sure the tables use the same columns in the same order. The formula result changes as table data changes, although the formula itself does not perform Power Query-style cleanup.
Assuming the source tables are named Sales_Jan and Sales_Feb and both have the same three columns, enter this formula in an empty cell on the destination sheet:
=VSTACK(Sales_Jan[#Data],Sales_Feb[#Data])
For example, if Sales_Jan contains three data rows and Sales_Feb contains two, the formula returns five rows and three columns. The #Data references exclude the tables’ header rows, so the combined output does not repeat headers. Add a separate heading row above the formula if needed. Leave the cells below and to the right of the formula clear; otherwise Excel may show a #SPILL! error because it cannot place the full result.
VSTACK is convenient for a live result, but it does not automatically align columns by their header names. If the source tables have different column orders, rearrange them first. It also does not remove duplicate records or standardize inconsistent values such as “NY” and “New York.” Power Query is usually easier to manage when sources need cleanup.
Why Data > Consolidate is different
Excel’s Consolidate command is designed to summarize values from multiple ranges, not to combine every transaction row into a single list. Choose Data > Consolidate, select a function such as Sum or Average, and specify the ranges to consolidate. You can use labels to match corresponding rows or columns in source ranges.
For instance, if each worksheet contains monthly totals for the same departments, Consolidate can calculate an overall total by department. It will not create a transaction-level table containing every sale from every month. If you need those individual records, append the data with Power Query, VSTACK, or copy-and-paste instead.
Check the combined data for common problems
A merged list can look plausible while still containing errors. Before relying on it, check both its structure and its contents. These issues are common:
- Repeated headers: Pasting every sheet’s header into the data creates rows that may interfere with sorting, filtering, and analysis.
- Different column meanings: A field named Amount should represent the same kind of amount on every sheet, and values should use compatible units and formats.
- Inconsistent headers: “Customer ID” and “CustomerID” may be treated as separate fields by Power Query. Standardize names before appending.
- Text mixed with numbers: A number stored as text may sort or calculate differently from a numeric value. Review data types, especially dates and amounts.
- Unnoticed duplicates: Appending does not automatically identify duplicate records. Check whether repeated rows are valid or should be removed.
- Rows outside a table: If a source table does not include newly added records, a query or formula that uses that table may miss them.
Compare row counts as a basic check. If the source sheets contain 120, 85, and 95 data rows, a simple append should produce 300 data rows, excluding the single header row. If the output count differs, look for blank rows, filters, missed source ranges, or accidental duplicate pastes. Also inspect a few records from each source sheet in the combined result.
Frequently Asked Questions
Can I combine multiple Excel sheets without copying and pasting?
Yes. Power Query can append multiple source tables and refresh the result later. In Microsoft 365 or Excel 2024, VSTACK can also place compatible table data into one formula-driven output.
Will the combined sheet update when I change the original sheets?
A manual copy does not update automatically. A VSTACK formula recalculates from its source tables, while Power Query updates when you refresh the query, for example with Data > Refresh All.
Do all the sheets need identical column headings?
For VSTACK, the columns need to have the same order and meaning, because it stacks by position. Power Query can match columns by their names, but consistent headings still help prevent missing or mismatched data.
How do I combine sheets and keep only one header row?
When copying manually, include the header from the first sheet only. With VSTACK, use table data references such as #Data and add one heading row separately. In Power Query, promote or verify the headers in each source query before appending.
Does Excel Consolidate combine all rows into one list?
No. Consolidate calculates summaries from specified ranges, such as sums or averages. Use an append method when you need to retain every underlying record.
Keep the source sheets and document the result
Save a copy of the workbook before making a large change, and keep the original sheets until you have verified the combined data. Give the destination worksheet a descriptive name, note which sheets or tables it includes, and record whether it is a fixed copy, a formula result, or a refreshable Power Query output. That small distinction helps prevent confusion when source data changes later.
Related
- Can You Zoom Out in Excel? Methods and View Tips
- Can You Transpose Cells in Excel? Methods and Tips
- Can You Type Subscript in Excel? Methods and Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Enter a Negative Number in Excel CorrectlyTo enter a negative number in Excel, select a cell, type a minus sign followed by the number,…
- Excel Tab Rename Shortcut: Windows and Mac Quick GuideIn Excel for Microsoft 365 on Windows, select the worksheet you want to rename, then press Alt, H,…
- ISBLANK in Excel: Examples and How It WorksIn Excel, ISBLANK checks whether a cell is genuinely empty: for example, enter =ISBLANK(A2) to return TRUE if…
- How to Find a Z-Score Using Excel Solver Step by StepTo find a z-score from a cumulative probability, put a starting z value in one cell, calculate its…
- Clear Outlook Cache on a PC: Safe Steps for WindowsTo clear Outlook cache on a Windows PC, first choose the cache that matches the problem: in classic…
- Freeze Rows in Excel With Keyboard Shortcuts: Step-by-StepTo freeze rows in Excel with keys on Windows, press Alt, W, F, R to keep the top…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.