Yes. In Excel for Microsoft 365 on Windows, use Power Query to bring sheets or files with similar columns into one table: turn each source range into an Excel table, import the tables with Data > From Table/Range, then use Append Queries to stack their rows. For many workbooks kept in one folder, use Data > Get Data > From File > From Folder and choose Combine & Transform Data. The result is a refreshable query, so you can update it when the source data changes instead of repeating a manual copy-and-paste.
What Power Query consolidation means
Consolidating sheets usually means collecting records from several worksheets into one longer list. For example, a sales workbook might have a January sheet, a February sheet, and a March sheet. If each has the same fields—Date, Region, Product, and Sales—Power Query can append the records into one table. This is also called stacking or combining rows.
Power Query is Excel’s data import and transformation tool. It records the steps used to retrieve and shape data, then can run those steps again when refreshed. It does not need to alter the original sheets. Instead, it creates a query result that you can load to a worksheet or, for some workflows, to the Data Model.
One important distinction: appending adds rows; merging joins tables side by side using a matching field, such as a customer ID. If the goal is one long list of transactions, use append. If the goal is to add a customer’s name to each transaction by matching IDs, use merge.
Prepare the source sheets first
Clean, consistent source data makes the consolidation easier to build and maintain. Each source should have a single header row, one record per row, and the same meaning for each column. Avoid blank rows above the headers, merged cells, subtotals inside the records, and decorative titles that Power Query could interpret as data.
For a reliable same-workbook workflow, convert each data range into an Excel table. Click inside a range and choose Insert > Table, confirm whether the table has headers, and give it a useful name from the Table Design tab, such as Sales_Jan or Sales_Feb. Use matching column headings across the tables. Table names can differ; the column names and meanings should be consistent.
It is fine for one month to have more rows than another. Power Query is designed to handle varying row counts. It is less safe to change a heading from Month to Sales Month in only one sheet: append matches fields by column name, not by the column’s position.
Append sheets from one workbook
- Turn each range into a table. Make sure the tables have compatible headers and do not include totals rows as ordinary records.
- Import each table. Click inside the first table and choose Data > From Table/Range. In the Power Query Editor, review the headers and data types, then choose Home > Close & Load > Close & Load To if you want to control where the result goes. For a source query, choose Only Create Connection where that option is offered. Repeat for the other tables.
- Create the appended result. In the Power Query Editor, choose Home > Append Queries > Append Queries as New. Choose Two tables for two sources, or Three or more tables to select a larger set, then select the queries to combine.
- Review and load. Check the combined preview, fix headers or types if necessary, and choose Home > Close & Load or Close & Load To. Loading to a new worksheet is a practical choice for a table you want to inspect and filter.
The appended query is separate from the original tables. If you add rows to an Excel table later, refresh the query to include them. If you add an entirely new table, you may also need to edit the append step so the new query is included.
Combine workbooks from a folder
When a recurring report arrives as one workbook per branch, month, or department, a folder query can be more maintainable than importing every file one by one. Put the intended source files in a dedicated folder, then in Excel for Windows choose Data > Get Data > From File > From Folder. Browse to the folder and select it. In the file list, choose Combine & Transform Data.
Excel asks which object in the sample workbook to use, such as a named table or worksheet. Select the object that represents the records you want to combine, inspect the preview, and continue to the Power Query Editor. The combined query applies a transformation based on the sample file to the matching files in the folder. The exact dialog labels can vary slightly by Excel build.
This approach works best when all included files use a consistent layout and the same object name. Keep unrelated workbooks, temporary files, and prior consolidated outputs somewhere else; otherwise they may be picked up as inputs. Filter the folder’s file list by name or extension when appropriate, and check that the sample file is representative. If a new file has a different structure, the combine step may produce errors or unexpected columns.
For example, suppose each monthly workbook contains a table called Transactions with columns Date, Account, Description, and Amount. A folder query can combine those tables, and refreshing after adding the next monthly file brings its rows into the result. If the files instead have differently named sheets or inconsistent layouts, standardize them first or build transformations that explicitly account for the variations.
How Power Query handles columns and changes
Append does not simply put column A under column A and column B under column B. It aligns fields by their header names. If one query has a Department column and another has Department and Manager, the combined result includes both columns; rows from the first query have a blank value in Manager. In Power Query, that missing value is represented as null.
This behavior can be useful when new fields appear, but it can also reveal a source-data mistake. A typo such as Amout instead of Amount creates a separate field rather than correcting the intended column. Inspect the final column names and remove or rename accidental duplicates.
Power Query may assign data types automatically, but automatic detection is not infallible. A date stored as text, an account number with leading zeros, or a column containing both numbers and labels may be interpreted incorrectly. Review the type icon beside each column heading in the editor and set appropriate types. Identifiers are often best treated as text when preserving leading zeros matters.
Also decide whether source tabs contain only records or include totals and notes. A total row appended as a transaction can inflate later summaries. Filter out such rows in the query, or—better—keep summary information outside the source table.
Refresh, load, and maintain the result
After loading the query, use Data > Refresh All to update queries in the workbook. You can also right-click a query result or use the Queries & Connections pane to refresh an individual query. Refresh reruns the saved retrieval and transformation steps; it does not mean that every kind of change is automatically incorporated without review.
For a folder query, refresh can detect additional eligible files in the same folder. For table-based sources in the current workbook, it can read updated rows in the referenced tables. A renamed or deleted table, moved folder, changed file permissions, or altered headers can break a step and require a fix in the editor. If the source folder moves, inspect the Source step in Query Settings and update the path.
Choose the output location based on how the consolidated data will be used. A worksheet table is easy to browse, filter, and reference in other sheets. Loading only as a connection avoids placing another copy of a large result on a worksheet. The Data Model can be useful for analysis with PivotTables, but is not necessary for a straightforward combined list.
Keep the source data separate from the query output. Editing cells in the loaded result is generally not a durable way to correct source records, because a refresh rebuilds the output. Make corrections in the source or add an intentional transformation step in Power Query.
Power Query versus Excel’s Consolidate command
Excel also has a separate Data > Consolidate command. It is intended to summarize data from multiple ranges, often by a function such as Sum or Average and, where suitable labels are provided, by category. That is different from creating one normalized list of all source records.
Use Power Query when you need to append detailed rows, clean or reshape source data, combine recurring files, or repeat the same process after updates. Consider the Consolidate command when the task is specifically to calculate a summary from similarly arranged ranges. Neither method makes inconsistent source data consistent by itself: clear labels, matching fields, and a decision about duplicates still matter.
Common problems and practical fixes
- Some rows are missing: Check whether every range is inside its Excel table, whether folder filters exclude a file, and whether the query points to the intended sheet or table.
- Columns appear twice: Look for spelling differences, trailing spaces, or different header names. Standardize headings before appending.
- Numbers or dates look wrong: Review the column’s data type and regional interpretation. For example, dates written with day and month in different orders can be ambiguous.
- Refresh fails: Check the source path, file availability, table names, permissions, and any step that references a column that has since been renamed.
- The output contains duplicates: Append retains repeated rows. Decide whether duplicates are valid records; if not, remove them using a defined key or a deliberate Power Query remove-duplicates step.
Before removing duplicates, define what makes a row a duplicate. Two transactions with the same date and amount may still be distinct if they have different IDs. Removing duplicates without a suitable key can discard legitimate records.
Mac and Excel for the web
The instructions above target Excel for Microsoft 365 on Windows, where the folder-combine workflow and Power Query Editor are available through the Data tab. Excel for Microsoft 365 on Mac includes Power Query capabilities, but available connectors, authoring features, and interface details can differ by version and update channel. If a particular source or folder-combine option is absent, check the current Excel for Mac documentation and build rather than assuming the Windows path is identical.
Excel for the web can open and refresh some workbook queries, but it does not provide the same full Power Query authoring experience as desktop Excel. For building or substantially editing a query, use a supported desktop version. Once the result is loaded, the workbook’s sharing and refresh behavior can still depend on the source location and permissions.
Frequently Asked Questions
Can Power Query combine multiple sheets in the same workbook?
Yes. A straightforward method is to convert each sheet’s data range into a table, import each table, and append the resulting queries. If the sheets have inconsistent layouts, clean or transform them before appending.
Does Power Query automatically include new rows?
It can include new rows within a referenced Excel table when you refresh. A folder query can include newly added files that meet its filters and structure. Refresh the query after updates, and check the source when a file or table has been renamed or moved.
Do the column orders have to match?
No. Append aligns columns by header name rather than their left-to-right positions. The headings should match in spelling and meaning; otherwise Power Query treats differently named headings as separate columns.
Can I combine sheets with different columns?
Yes, but the result will contain the union of the column names, with nulls where a source has no corresponding field. Decide whether those differences are expected, and standardize headers when they represent the same information.
Will Power Query change my original worksheets?
Normally, it reads the source and creates a separate query result. Transformations are stored as query steps, not written back into the source ranges. Avoid editing the loaded output as if it were the source, because refreshing can replace those edits.
Choosing a dependable workflow
For a one-time consolidation of a few similarly structured sheets, import their tables and append them as a new query. For a recurring collection of workbooks with a stable layout, use From Folder and combine the matching table or worksheet. In either case, validate the headers, data types, row counts, and duplicate logic before relying on the result. Once those checks are built into the workflow, refreshing is much faster and more consistent than manually copying data from sheet to sheet.
Related
- Can You Undo Remove Duplicates in Excel?
- Can You Use Solver in Excel for the Web? What Works
- Can You Split One Cell in Excel? What Actually Works
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Page Break Lines: How to Hide Them in Normal ViewTo hide page break lines in Excel for Microsoft 365 on Windows, go to File > Options >…
- Excel Slicers: A Clear Guide to Filtering DataIn Excel for Microsoft 365 on Windows, a slicer is a set of clickable buttons that filters a…
- Excel Shortcut to Expand a Column: AutoFit and MoreIn Excel for Microsoft 365 on Windows, select a cell in the column and press Alt, H, O,…
- Excel Consolidate Multiple Sheets: Steps and OptionsTo consolidate multiple sheets in Excel for Microsoft 365 on Windows, go to Data > Consolidate, choose a…
- Copy Excel Formulas to Google Sheets Without ErrorsTo copy a formula from Excel into Google Sheets, copy the formula cell in Excel, select the destination…
- How to Clear Browser Cache in Outlook on the Web SafelyTo clear the browser cache for Outlook on the web, open your browser’s privacy settings and delete cached…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.