Yes. To combine rows from several worksheets into one list, use Power Query’s Append feature, or use the VSTACK function in Microsoft 365 or Excel 2024; to summarize matching categories across sheets, use Data > Consolidate. The right choice depends on whether you want to stack records, calculate a summary, or make a one-time copy.
What consolidating worksheets means
People use “consolidate” to describe two different tasks in Excel. One is appending data: placing the rows from several sheets underneath one another to make a longer table. For example, you might combine January, February, and March sales records while keeping each transaction as a separate row. The other is summarizing: combining values from different ranges into totals or other calculations, often matched by row or column labels.
Excel has different tools for these jobs. Power Query and VSTACK are suited to appending rows. The Data > Consolidate command combines corresponding values using a selected calculation, such as Sum or Average. It is not a general-purpose command for stacking every record from several sheets into one list. Choosing the tool based on the shape of your data helps avoid a result that looks complete but answers the wrong question.
Before combining anything, check that the source sheets have compatible columns. If one sheet has Date, Product, and Sales while another has Date, Product, Sales, and Region, decide whether to add the missing Region column to the first sheet or otherwise standardize the structure. Consistent headings and data types make the combined result easier to filter, analyze, and refresh.
Choose the right method
- Power Query: Best for repeatable row stacking. It can combine structured tables, handle differing column orders by heading, and refresh when the source data changes.
- VSTACK: Best for a quick, formula-driven stack when the source ranges have the same number and order of columns. It updates as referenced cells change, but you manage the sheet references in the formula.
- Data > Consolidate: Best for calculating a combined summary, such as total sales by product, from similarly organized ranges.
- Copy and paste: Suitable for a small, one-time job where the source layout is already consistent and no refresh is needed.
For recurring monthly reports, Power Query is usually the most maintainable option. For a short workbook with a few fixed sheets, VSTACK may be simpler. For category totals rather than individual records, use the Consolidate command or a PivotTable, depending on how much flexibility you need.
Stack rows with Power Query
Power Query is part of Excel for Microsoft 365 on Windows. A reliable way to combine sheets in one workbook is to turn each source range into an Excel table, create a query for each table, and append those queries. Tables expand more predictably than arbitrary cell ranges when new records are added.
- On each source sheet, select the data, including its header row, and choose Insert > Table. Confirm that the table has headers. Give each table a clear name using the Table Design tab, such as Sales_Jan and Sales_Feb.
- Click inside the first table and choose Data > From Table/Range. The Power Query Editor opens. Check that the headings and data types are sensible, then choose Home > Close & Load To. Depending on the dialog options available in your Excel build, load the query as a connection or to a worksheet. Repeat for the other source tables.
- In the Power Query Editor, choose Home > Append Queries > Append Queries as New. Choose the option for three or more tables if combining more than two, and add each query to the list. For two tables, select the two-table option and specify both queries.
- Review the appended preview. Then choose Home > Close & Load, or Close & Load To if you want to select a destination. Load the combined result to a new worksheet or another appropriate location.
Append matches columns by their names, not by their left-to-right position. That can be helpful if columns appear in a different order on different sheets, but inconsistent headings can create separate columns. For example, Sales and Sale will be treated as different headings until you standardize them. A missing column is generally represented by blank or null values for rows that do not have that field.
To incorporate edits or new rows later, use Data > Refresh All. If new records are added beneath a source Excel table, the table can expand to include them; check that the new rows are inside the table before refreshing. Power Query does not usually make the combined output an editable copy of the source records: make corrections in the source tables or in the query steps, then refresh.
Use the Consolidate command for summaries
In Excel for Microsoft 365 on Windows, select a blank destination area and choose Data > Consolidate. In the dialog, choose a function such as Sum, Average, Count, Max, or Min. Select a source range, choose Add, and repeat for each range or worksheet. If the ranges have labels, use Top row and/or Left column to match data by those labels. Select Create links to source data if you want linked detail in an outline-style result rather than only a consolidated summary.
For example, suppose three sheets each contain product names in column A and monthly sales in column B. If the product lists are arranged consistently, you can add the three ranges as references and use Sum to calculate combined sales. If you select label options, Excel can match product names even when their order differs. If you do not select label options, the tool relies on the positions of values in the selected ranges, so mismatched row order can produce misleading totals.
The result is a summary, not a transaction-level list. If the source sheets contain individual orders and you need every order in one table, use Power Query or VSTACK instead. The Consolidate dialog is available in desktop Excel; the ribbon layout can vary by platform, and Excel for the web may not provide the same authoring tools. If you do not see the command in your version, use a desktop edition or choose another method.
Stack ranges with VSTACK
VSTACK places ranges vertically and spills the result into neighboring cells. It is available in Microsoft 365 and Excel 2024. Use it when the sheets have the same columns in the same order and you want a live formula result rather than a Power Query query.
Assume the January, February, and March sheets each have headers in A1:D1 and three records in A2:D4. Enter the following formula in an empty cell on the destination sheet:
=VSTACK(January!A1:D4,February!A2:D4,March!A2:D4)
The formula includes the header once, then stacks the nine records below it, producing a spill range of 10 rows by 4 columns. The formula omits the February and March header rows to prevent duplicate headings. If a worksheet name contains spaces, enclose it in single quotation marks, as in ‘January Sales’!A1:D4.
VSTACK returns a dynamic array, so leave the cells below and to the right of the formula clear. If something blocks the output, Excel can return a #SPILL! error. Each range should have the same number of columns; when widths differ, VSTACK can fill missing positions with #N/A, which may require you to fix the source layout. This formula references fixed ranges, so extend the references when records are added beyond row 4. In Excel for the web, support depends on the available Excel version and account; the function is not available in older perpetual editions such as Excel 2019.
Copy and paste for a one-time combination
For just a few small, uniform sheets, a manual method may be quickest. Copy the header and records from the first sheet into a new sheet, then copy only the records—not the header—from each remaining sheet and paste them below the existing data. Check that every block has the same columns and that no blank rows or totals were accidentally included.
This method creates a separate snapshot. Later changes to a source sheet will not appear in the combined list, and there is no refresh command to update the pasted result. It is therefore less suitable for a report that must be rebuilt every month. If you choose manual copying, keep the source sheets until you have checked the combined row count and a few sample records.
Prepare and check the source data
Whichever method you choose, a little preparation prevents common errors. Use one header row per source table, avoid merged cells within the data, and keep each column to one kind of information. Dates should be dates rather than text that only looks like a date; numbers should not be stored as text. Remove subtotal rows from transaction-level data unless you intend to include them as records.
- Use the same spelling for headings across sheets, such as Customer ID rather than CustomerID on one tab and Customer ID on another.
- Check for duplicate records. Combining sheets does not automatically identify or remove duplicates, and duplicate rows may be valid transactions.
- Decide how to handle blank cells and missing categories before interpreting totals.
- Keep a source-sheet or period field if you will need to trace a combined row back to its origin. You can add this in the source tables or as a Power Query transformation.
- Compare the number of expected source records with the combined result, and verify sample values from each sheet.
One frequent misconception is that appending sheets automatically reconciles different layouts. It does not know that Region and Territory might mean the same thing, or that two identical-looking customer names refer to the same person. Naming, data types, duplicates, and business meaning still need review. Similarly, consolidating values with Sum is not the same as removing duplicate records or merging tables based on a shared ID.
Refreshing, sharing, and platform differences
Power Query results can generally be refreshed from the desktop workbook with Data > Refresh All. If the workbook is shared, recipients need access to the source data and compatible Excel features for the intended refresh workflow. A query that reads only tables stored in the workbook is easier to share than one that relies on a file path on a single computer. If a query uses external files, moving or renaming those files can interrupt the connection.
VSTACK recalculates from its referenced cells, but it does not discover new worksheets automatically. You must add a new sheet reference to the formula when a new period is created. The Consolidate command produces a summary based on the references you specify; if source ranges change, review those references and run the command again as needed. For both Windows and Mac desktop Excel, feature availability and ribbon placement can depend on the edition and update channel. Excel for the web is useful for viewing and editing many workbooks, but desktop Excel provides the fuller set of tools for creating and managing some consolidation workflows.
Frequently Asked Questions
Can I combine multiple Excel sheets into one automatically?
Yes. Use Power Query to append source tables and then refresh the query after updating the source data. VSTACK can also combine specified ranges, but you must update the formula when adding another sheet or extending fixed references.
Does Data > Consolidate put all rows into one sheet?
No. It summarizes corresponding values using a calculation such as Sum or Average. To place all source records in one longer list, use Power Query, VSTACK, or manual copying.
Can I combine sheets with different column orders?
Power Query can align appended data by column heading, so consistent names matter more than position. VSTACK stacks by position, so arrange its source columns in the same order and make the ranges the same width.
Will the combined sheet update when the source sheets change?
A Power Query output can be refreshed, and a VSTACK result recalculates from its references. A manual copy does not update. The Consolidate command creates a summary from selected ranges; review and rerun it if the source ranges or data change.
Can I combine sheets from different workbooks?
Yes. Power Query can import tables or files from other workbooks, and the Consolidate dialog can accept references from multiple ranges. External-file methods depend on valid file locations and access permissions, so keep the source files available and test refreshes after moving a workbook.
Related
- Defining Print Area in Excel: Setup and Printing Tips
- DDSolver Excel Add-in: What It Does and How to Use It
- Data Validation in Excel: Lists, Rules, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Solver Add-In: How It Works and How to Use ItExcel Solver is an optimization add-in that finds values for selected cells to maximize, minimize, or reach a…
- How to Delete a Stubborn Blank Page in Microsoft WordTo delete a stubborn blank page in Word, show formatting marks with Ctrl+Shift+8 on Windows or Command+8 on…
- Cover Page in Word: How to Add and Customize ItTo add a cover page in Microsoft Word for Microsoft 365 on Windows, place the cursor anywhere in…
- Creating Labels in Microsoft Word: A Step-by-Step GuideTo create labels in Microsoft Word, open Mailings > Labels, choose the label size under Options, enter your…
- How to Delete a Page in a Word Resume TemplateTo delete a page from a Word resume template, select the page’s contents and remove them, then delete…
- Google Sheets Filter: How to Create and Use OneTo create a filter in Google Sheets in a web browser, select your data range, including its header…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.