Yes. To combine the rows from several similarly structured tables into one refreshable table, use Power Query: convert each source range to an Excel table, load each with Data > From Table/Range, then choose Home > Append Queries > Append Queries as New and load the result. For a one-time summary by matching labels, use Data > Consolidate instead; it summarizes values rather than stacking all the source records.
What “consolidate” can mean in Excel
People use “consolidate” to describe two different jobs. One is appending records: for example, joining January, February, and March sales rows into one longer list. The other is summarizing: for example, adding the sales totals for the same product across several worksheets. Excel has different tools for these jobs, and picking the right one avoids a result that looks plausible but answers the wrong question.
- Append tables when you need every source row in a combined dataset. Power Query is usually the best choice for repeatable work; VSTACK is a formula alternative for compatible tables.
- Summarize tables when you need totals or another calculation grouped by matching row or column labels. Use Data > Consolidate, a PivotTable, or Power Query, depending on the shape and purpose of the data.
Neither approach automatically solves inconsistent data. Differences such as “North” versus “N.”, dates stored as text, or sales figures in differently named columns may require cleanup before the combined result is dependable.
Append tables with Power Query
Power Query is a good fit when source tables share a general purpose but contain different records. It creates a repeatable query: after the source data changes, you can refresh the combined output instead of copying and pasting the rows again. These directions are for Excel for Microsoft 365 on Windows.
- Make sure every source has a header row and consistent column names. Select a source range and press Ctrl+T, confirm whether the table has headers, and select OK. Repeat for each range. Give each table a clear name under Table Design > Table Name, such as SalesJan and SalesFeb.
- Select a cell in the first table, then choose Data > From Table/Range. In Power Query Editor, check that the headers and data types look right. Choose Home > Close & Load To, if offered, and select Only Create Connection. Repeat for the other tables. Depending on the build, Close & Load may first load a sheet; you can change the load destination later.
- In Power Query Editor, choose Home > Append Queries > Append Queries as New. Select Three or more tables if needed, add the source queries to the tables-to-append list, and select OK.
- Review the new query, then choose Home > Close & Load to place its result in a worksheet. You can also use Close & Load To to choose a destination or create a connection.
Append combines rows, not columns. Power Query matches columns by their header names, rather than assuming that the first column in every source means the same thing. If one table has a column another lacks, the combined result includes that column and shows null values for records without a value. That is useful when source schemas vary, but it is a reason to review the output rather than assume every row is complete.
Keep the combined result up to date
After loading an appended query, choose Data > Refresh All to refresh its source queries and output. If the sources are Excel tables, rows added within a table are normally included when the query refreshes. A new, separate table is not automatically added to an append query that was configured with a fixed list of sources; edit the query to include it.
Refreshing is different from continuously recalculating a formula. The output remains as it was until refreshed, and a refresh can take time for large or external sources. Keep source tables and the output clearly separated so it is obvious which sheet contains the original records and which contains query results. Avoid typing manual corrections into query output: a refresh can replace that output. Correct the source or the query steps instead.
Use Data Consolidate for summaries
The built-in Consolidate command is for combining calculations, not creating one detailed list of all records. It can apply a function such as Sum, Average, or Count to source ranges and place the calculated result in a destination range. It is especially useful when worksheets have comparable summaries, such as a small monthly report with product names down the left and totals beside them.
- Choose a blank destination area, preferably on a separate worksheet, and select its upper-left cell.
- Choose Data > Consolidate. Select the function you need, such as Sum.
- In Reference, select a source range and choose Add. Repeat for each source range. You can select ranges on other sheets, and you can add references from other open workbooks.
- If the ranges have labels, select Top row and/or Left column so Excel can match corresponding headings. Select Create links to source data if you want an outlined result linked to the source cells; otherwise, the result is a consolidated set of values.
- Select OK, then check the output against a small manual calculation.
For example, suppose two monthly reports each list sales totals for the same product names. Consolidate can add the matching product totals even if the products appear in a different order, provided you tell it to use the left-column labels. Without label matching, Excel combines cells by their positions, so the first row of one range may be added to the first row of another even when those rows represent different products.
Use VSTACK for a formula-based result
VSTACK can join compatible table data vertically and spill the result into cells. It is available in Microsoft 365 and Excel 2024. Unlike Power Query, it recalculates as its referenced table data changes, but the output is a formula spill range rather than a query-loaded table. Leave enough empty cells below and beside the formula so the result can expand.
With two Excel tables named SalesJan and SalesFeb, each with the same four columns in the same order, enter this formula in a blank cell:
=VSTACK(SalesJan[#Headers],SalesJan,SalesFeb)
The formula includes the January headers once, followed by the data rows from both tables. If SalesJan contains 10 data rows and SalesFeb contains 8, the spill contains 19 rows altogether: one header row plus 18 records. VSTACK combines positions, not column meanings, so put the columns in the same order in both tables. If you stack entire tables without separately selecting one header row, the headers can appear again as data between sets of records.
Choose the right method
- Choose Power Query for repeatable appending, larger datasets, multiple sources, or cleanup steps you want to apply again on refresh.
- Choose VSTACK for a compact, live formula result when the tables have matching columns and your Excel version supports the function.
- Choose Data > Consolidate for a quick total or other summary from similarly laid-out ranges, especially when matching labels matter more than preserving individual records.
- Choose a PivotTable when you want to group and summarize a combined list interactively, such as sales by product and month. A PivotTable summarizes data; it does not replace the step of appending separate source lists when they are not already combined.
Excel for the web and Mac may show different options from the Windows ribbon, and Power Query authoring features can vary by platform and build. On Mac, check the Data tab for the available Get Data or Power Query commands; if the append workflow is unavailable in your installation, use a supported Windows version or a compatible VSTACK formula. On Mac, Insert > Table is an alternative way to create a table if the Windows Ctrl+T shortcut does not apply.
Common problems and checks
- Repeated headers appear among the records. A source range may include its header row as data, or a formula may stack each table’s headers. Make sure the query treats headers as headers, or include just one header row in a VSTACK result.
- Some values appear under the wrong heading. With Power Query, check spelling and consistency of column names. With VSTACK and Data Consolidate without label matching, check column or row positions.
- Numbers or dates do not behave like numbers or dates. A source may contain text values, mixed formats, or blanks. In Power Query, review the column’s data type; in a worksheet, inspect representative cells and correct the source values.
- New records do not appear. Refresh a query with Data > Refresh All. Confirm that new rows are inside the source Excel table, and that a newly created source table has been added to the append query. A fixed range or a fixed query source will not necessarily include new material.
- The output has extra columns or blanks. Power Query retains columns found in any input, so inconsistent headers can produce additional fields and nulls. Standardize names and decide whether those fields belong in the final dataset.
Before relying on a combined result, compare its row count with the source row counts, check several records from each input, and verify a total such as sales amount. These checks can reveal accidental duplicate sources, omitted tables, or a mismatch in headers. If the data is used for reporting, keep the source sheets unchanged and document any transformations in the query steps.
Frequently Asked Questions
Does Excel have a button to merge multiple tables?
Yes, but the right command depends on the goal. Use Power Query’s Append Queries to stack records from multiple tables. Data > Consolidate combines calculations from ranges; it does not create a full row-by-row list.
Can I combine tables from different worksheets?
Yes. Power Query can append tables from different worksheets in the same workbook once each is available as a query. Data > Consolidate can also use source ranges on different sheets. For a formula, VSTACK can reference tables on other sheets in the same workbook.
Do the column names have to match?
For Power Query, matching names help Excel place values in the intended columns; unmatched names can create separate columns. VSTACK uses column position, so the columns must be in the same order and compatible. Consolidate can match row or column labels when its label options are selected.
Will the combined table update automatically?
A Power Query result updates when you refresh it, for example with Data > Refresh All. A VSTACK formula recalculates when its referenced cells change. Data Consolidate generally needs to be run again for an updated value-only result; linked results and source ranges have their own limitations and do not automatically expand to include arbitrary new source areas.
Can I combine tables with different columns?
Power Query can append tables with different columns, but fields missing from a source will be blank or null for that source’s rows. Decide whether similarly named fields really represent the same information, and standardize headings where appropriate. VSTACK requires compatible column counts and relies on position, so mismatched layouts need to be rearranged first.
Related
- Excel Data Validation: Lists, Rules, and Setup
- Remove Data Validation Restrictions in Excel
- Excel Data Validation: Rules, Lists, and Input Tips
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 Clear Cache in Outlook 365 on Windows, Mac, and WebTo clear the Outlook 365 cache, first choose the version you use: classic Outlook for Windows can rebuild…
- Excel Data Analysis and Business Modeling ExplainedExcel data analysis turns organized information into summaries, patterns, and decisions; business modeling uses formulas and assumptions to…
- Outlook Email Archive: How to Save Messages SafelyTo archive an email in Outlook, select the message and choose Archive on the toolbar or ribbon; Outlook…
- Excel vs CSV: Key Differences and When to Use EachExcel is spreadsheet software and a workbook format; CSV is a plain-text format for rows and columns. Choose…
- Can’t Merge Cells in Excel? Common Causes and FixesYes—Excel can merge cells, but the command may be unavailable if the selection is in a table, the…
- Excel: Remove Table Formatting Without Losing DataYes. In Excel for Microsoft 365 on Windows, choose Table Design > Table Styles > More > Clear…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.