Yes. In Excel for Microsoft 365 on Windows, you can combine sheets by appending their rows with Power Query, stacking matching tables with VSTACK, copying data manually, or summarizing matching ranges with Data > Consolidate. Choose Power Query for repeatable imports, VSTACK for a live formula result, and the Consolidate command when you want totals rather than one long list.
What “consolidate” can mean in Excel
People use “consolidate multiple sheets into one” to describe two different tasks. The first is appending: placing the records from several sheets one under another in a single list. For example, you might combine January, February, and March order sheets into one table with a single header row. The second is summarizing: calculating totals from several ranges, such as adding each branch’s sales by product.
The distinction matters because Excel’s Data > Consolidate command is primarily a summary tool. It can combine values using functions such as Sum or Average, but it does not simply turn separate lists into one continuous table. For a long list of records, use Power Query, VSTACK, or a deliberate copy-and-paste process. Before choosing, check that the source sheets have compatible column names, data types, and layouts.
Choose a method based on how the sheets change
A one-time combination of a few small sheets is often quickest by copying and pasting. If the same process will happen again, Power Query is usually easier to refresh and less prone to missed rows. VSTACK is useful when you want a formula-driven result that updates as the source tables change. Data > Consolidate is a better fit for a compact report that combines corresponding values, not for preserving every transaction as a separate row.
- Power Query: Best for recurring combinations, larger lists, and data that needs cleanup or consistent column handling.
- VSTACK: Best for a formula-based combined view when the source tables have the same columns.
- Copy and paste: Best for a small, one-time job where checking the result is straightforward.
- Data > Consolidate: Best for aggregating corresponding values from similarly structured ranges.
There is no single method that is safest in every workbook. A formula can be transparent but needs an available spill area; a query can be repeatable but requires refresh; manual copying is simple but can omit rows. Match the method to both the shape of the data and whether it will change.
Combine sheets with Power Query
Power Query is Excel’s built-in tool for importing and transforming data. To append sheets that contain the same kind of records, first make each source range an Excel table. On Windows, click inside a range and press Ctrl+T, confirm whether the table has headers, and give each table a descriptive name under Table Design > Table Name. Use distinct names such as January, February, and March. Tables expand when you add rows directly beneath them, which makes them useful query sources.
- Click inside the first table and choose Data > From Table/Range. Power Query Editor opens. Check that the column headings and data types look sensible, then choose Home > Close & Load To… and select Only Create Connection.
- Repeat for each source table. Each table should now have its own query connection. If you have many worksheets, importing every table may take time, but the append operation itself is repeatable.
- Open a query in Power Query Editor and choose Home > Append Queries > Append Queries as New. Select Three or more tables, add the source queries to the tables-to-append list, and confirm.
- Review the new combined query. Choose Home > Close & Load or Close & Load To… and load the result as a table, typically on a new worksheet.
Append matches fields by column name, rather than assuming that the first column in every source has the same meaning. If one query has a column another lacks, the combined result can contain blank values for that field in the unmatched rows. Consistent headings such as “Date,” “Product,” and “Amount” make the output more reliable. Power Query may also assign data types; check dates, numbers, and identifiers before loading, especially if a code such as 00123 must remain text.
When source table rows change, use Data > Refresh All to update the output. A query based on a selected set of table queries will not automatically include a brand-new worksheet or table that you create later; you may need to add that source to the append query. On Mac, Power Query features and their locations can differ by Excel release, so check the available Get Data and query options in the installed desktop version. Excel for the web has more limited query authoring than desktop Excel.
Stack matching tables with VSTACK
VSTACK places arrays or ranges underneath one another and is a concise way to make a combined list without opening Power Query. It is available in Microsoft 365 and Excel 2024. First format each source range as a table, give the tables the names JanSales, FebSales, and MarSales, and make sure they have the same four columns in the same order. Put the formula in a blank cell with enough empty rows and columns for the result to spill.
With matching four-column tables named JanSales, FebSales, and MarSales, and with each table’s headers included only once in the result, enter =VSTACK(JanSales[#Headers],JanSales,FebSales,MarSales). The returned array has the JanSales header row followed by all data rows from the three tables. The other tables’ header rows are not repeated because the structured references JanSales, FebSales, and MarSales refer to their data rows.
Because these are table references, adding a new record within one of the tables expands the formula’s result automatically. If you add another month, edit the formula to include that table. If the spill range is blocked by existing content, Excel returns a #SPILL! error; clear the cells in the way. VSTACK pads shorter arrays with #N/A when their widths differ, so use tables with matching columns rather than treating that padding as a harmless formatting issue.
VSTACK does not clean or reconcile inconsistent data. If one sheet calls a field “Customer ID” and another calls it “Client ID,” a formula will not decide whether those fields mean the same thing. Standardize the source headers and column order first, or use Power Query to rename and transform columns. VSTACK is not available in older perpetual Excel versions such as Excel 2019; in those versions, use Power Query or another method instead.
Use Data > Consolidate for summaries
Use the Consolidate command when you want to calculate a combined result from corresponding ranges. For example, if three regional sheets each have products in the first column and monthly sales in the next column, you can summarize sales by product. This command is not a substitute for appending every underlying transaction into one list.
- On a new worksheet, select the cell where the summary should begin and choose Data > Consolidate.
- Choose a function, such as Sum, in the Function box.
- In Reference, select a source range and choose Add. Repeat for each range to include.
- If the source ranges have labels, select Top row and/or Left column as appropriate so Excel can match entries by label rather than position.
- Select Create links to source data if you want linked summary formulas, then choose OK.
Without label matching, consolidation can depend on the ranges having the same arrangement, so a shifted row may combine the wrong values. With labels, spelling and label consistency matter: “North” and “Northern” may be treated as different items. Check the output against a small hand-calculated example, and keep a copy of the source workbook before making substantial changes. The command is available in desktop Excel; the ribbon may be arranged differently on Mac, while Excel for the web does not offer the same complete desktop workflow.
Copy and paste for a one-time combination
For two or three small sheets with identical columns, manual copying is direct. Create a destination sheet, copy the header row and data from the first source, then copy only the data rows from each remaining sheet and paste them directly beneath the existing records. Do not paste each later header into the middle of the list. Convert the finished range to a table with Ctrl+T so that filters and consistent formatting are easier to use.
Before relying on the result, compare the number of data rows in each source with the total in the destination. Check the first and last records from every sheet, and sort or filter by a distinctive field to look for missing or repeated entries. Copying visible cells from a filtered range can produce a different result from copying every underlying row, so be clear about whether hidden or filtered-out records should be included. Manual copying is quick, but it will not update the destination when the source sheets later change.
Common problems and checks
Many combination errors come from inconsistent source structure, not from the combine feature itself. A worksheet may contain a title above the table, a blank row in the middle, a total at the bottom, or a column formatted differently from its peers. Decide whether those rows are data or decoration before combining; otherwise, a heading or subtotal can become an apparent record in the final list.
- Duplicate headers: Keep one header row in an appended list. Exclude the headers from later pasted ranges or use the table-based VSTACK pattern.
- Different field names: Standardize names before appending, or rename fields in Power Query. Similar-looking labels are not automatically recognized as equivalent.
- Numbers stored as text: Check that amounts and dates have consistent types. Text values may sort or calculate differently from numeric values.
- Repeated records: Combining rows does not automatically identify duplicates. Decide which columns define a duplicate, then review or remove duplicates separately.
- Changed source layout: New columns, renamed headers, or moved data can affect a query or formula. Review the output after structural changes.
- Missing rows: Check table boundaries and source ranges. A table includes rows within its boundary, not unrelated notes farther down the sheet.
Keep an untouched copy of important source data until the combined result has been checked. A useful validation is to compare row counts and totals: if each source has a known number of records, the appended table should contain their sum, excluding header rows. For numeric fields, compare source totals with the combined total where the fields represent additive quantities.
Frequently Asked Questions
Can Excel automatically combine all sheets in a workbook?
Yes, but the setup depends on the workbook. Power Query can combine selected table queries and refresh them later. A query created from a fixed set of tables does not necessarily discover every new sheet automatically; include new sources in the query or build a workbook-level import that selects the intended sheets.
Does Data > Consolidate put all rows under one header?
No. It is designed to summarize corresponding ranges with a function such as Sum or Average. To create one continuous list of records, append the tables with Power Query, use VSTACK where available, or copy the data rows into a destination sheet.
Will a combined sheet update when I edit the source sheets?
A VSTACK formula that references source tables recalculates when those tables change. A Power Query result updates when you refresh it, for example with Data > Refresh All. A manually copied result does not update automatically.
Can I combine sheets with different columns?
Yes, but decide how the fields correspond first. Power Query can align appended fields by column name and leave blanks where a source lacks a field. VSTACK requires compatible widths to avoid padding shorter arrays with #N/A, and neither method can infer that differently named fields have the same meaning.
Can I combine sheets in Excel for the web?
You can edit workbooks and use many formulas in Excel for the web, but some Power Query authoring and desktop commands are limited or unavailable there. For a full query workflow or the Consolidate command, use desktop Excel and check the features available in your version.
Related
- Excel Blue Border Around Print Area: What It Means
- Excel Auto Insert Row When Data Changes?
- Excel Data Validation for an Entire Column, Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Python Pandas Excel to Dictionary: Practical GuideTo convert an Excel worksheet to Python dictionaries with pandas, read it into a DataFrame with pd.read_excel(), then…
- Excel Sum by Color: How to Total Cells by Fill ColorExcel for Microsoft 365 does not include a built-in SUMBYCOLOR formula. The simplest dependable method is to filter…
- Schedule an Email in Outlook on PC: A Step-by-Step GuideTo schedule an email in Outlook on a Windows PC, compose your message, choose a future send time,…
- Excel Data Analysis Extension: ToolPak Setup and UsesExcel’s “Data Analysis” extension usually means the Analysis ToolPak, a free add-in included with desktop Excel that adds…
- Out of Office in Outlook on the Web: A Setup GuideTo set up an out-of-office reply in Outlook on the web, open Settings, go to Automatic replies, turn…
- Excel Workbook Name: What It Means and How to Change ItAn Excel workbook name is the name of the Excel file, such as Quarterly Budget.xlsx; it is not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.