The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →For recurring Excel files with the same columns, use Power Query’s folder connector: it combines the files into one table and lets you refresh the saved query when new files arrive. Choose a different method if you need totals by category rather than every row, have only a few fixed ranges, or are importing from Google Sheets.
Choose the method that matches your goal
“Consolidate” can mean either appending records into one long table or summarizing corresponding ranges into totals, averages, or counts. Those are different jobs. First decide what the output should contain; then choose the workflow.
As an Amazon Associate I earn from qualifying purchases.
- Append rows: preserve each source record in a single combined list. Power Query is usually the best fit for a recurring batch of similarly structured Excel workbooks.
- Summarize ranges: calculate results across matching worksheets or ranges. Excel’s Consolidate command is designed for this.
- Import known ranges: use formulas when the source locations are few and fixed, or Google Sheets’ IMPORTRANGE for a specified range in another spreadsheet.
Before automating, check that source columns have consistent headers and that the data is arranged as a list without entirely blank rows or columns. Differences in layout or column names can make a combined result harder to interpret or maintain. Microsoft explains the value of consistent, list-shaped data.
1. Power Query from a folder: best for recurring Excel files
Use this when similarly structured workbooks arrive repeatedly and you want one combined table. The folder connector applies the same transformation steps across files, and a later refresh reruns those saved steps. Microsoft’s supported-version list for this workflow includes Microsoft 365, Excel 2024, 2021, 2019, and 2016. Menu availability can vary by platform and edition, so confirm that your Excel version supports Power Query before relying on a particular path. Microsoft’s folder import guide and Power Query’s combine-files overview describe the process.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
- Put only the intended source workbooks in a dedicated folder. Keep unrelated files and subfolders elsewhere, or plan to filter them in the query.
- In Excel, open Data > Get Data > From File > From Folder. If your menus differ, use your edition’s Power Query import options.
- Review the files listed from the selected folder. Exclude anything that should not contribute data; folder selection may include subfolders.
- Choose Combine and Transform to inspect and adjust the data before loading, or Combine and Load to load the combined result directly.
- Check the resulting columns and data types, then load the table. When files are added or source data changes, refresh the query to apply the saved steps again.
This approach is most straightforward when each workbook has a matching schema. If columns or sheet layouts vary, inspect the transformation steps and standardize the inputs rather than assuming every file will combine cleanly.
2. Power Query from selected workbooks: best for known sources
If the files are known but do not belong to one recurring folder, import workbook content through Excel’s data import flow. The workbook connector lets you select workbook information—such as a sheet, table, or range—then transform or load it. For multiple files stored together, a Folder or SharePoint Folder connector is generally more suitable than importing each workbook one by one. Exact menus depend on the Excel version and platform. Microsoft Learn’s Excel connector documentation describes workbook selection and loading.
- Choose Excel’s Power Query option for importing workbook data, then select the source workbook.
- In the workbook navigator, select the sheet, table, or range containing the data you need.
- Choose to transform the data if you need to filter, rename, or align columns; otherwise load it to the destination workbook.
- For additional known sources, repeat the import and append compatible tables in Power Query. For a growing set of files, switch to a folder or supported shared-folder connector.
Use this route when source workbooks are individually selected or stored on a supported shared service. If those sources move, are renamed, or become inaccessible, the query may need its source path or permissions updated.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall3. Excel Consolidate: best for totals across corresponding ranges
Excel’s Consolidate command calculates summary results from ranges in the same workbook or other workbooks. It can consolidate by position or by category: choose position when the source areas have the same arrangement, and category when labels identify the matching items. The option to create links to source data can keep results linked for updates. This produces a summary, not a transaction table containing every source row. See Microsoft’s instructions for consolidating worksheet data.
- Open the destination worksheet and select the cell where the summary should begin.
- Go to Data > Consolidate and select the summary function, such as Sum, Average, or Count.
- Add each source range, including ranges from other workbooks if required.
- Choose Top row and/or Left column to match by labels, or leave label matching off when the ranges should align by position.
- Select Create links to source data if you want linked results, then run the consolidation and verify that labels and totals match your intended categories.
4. VSTACK or sheet-reference formulas: best for a small, fixed set
If the ranges are known, compatible, and unlikely to change, a formula can stack one range below another. Microsoft’s example is =VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50). The result updates when data in those referenced ranges changes. Microsoft’s guide to combining sheets covers this pattern.
VSTACK works with the ranges named in the formula; it does not discover arbitrary workbooks in a folder. If a source sheet, range, or workbook is added or expanded, update the references. For a changing batch of files, Power Query is a better fit.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.5. Google Sheets IMPORTRANGE: best for a few known spreadsheets
In Google Sheets, IMPORTRANGE imports a specified range from another spreadsheet. The receiving sheet needs access to the source, and the first connection may require an authorization grant. Google says it checks for updates hourly while the receiving document is open, so it should not be treated as instant synchronization. Google also advises limiting receiving sheets because each one reads from the source, and warns that spreadsheets referencing one another can create cycles. See Google’s IMPORTRANGE documentation.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Use this for a small number of known Google spreadsheets and ranges. Keep permissions stable and avoid chains of dependent imports when a direct source reference will do.
Quick Recap
Compare the options before building the workflow
| Method | Best for | How it updates | Main constraint |
|---|---|---|---|
| Power Query from a folder | Appending recurring Excel files with a consistent schema | Refresh reruns saved query steps | Files should have compatible structure; filter out unintended files or subfolders |
| Power Query from selected workbooks | Known workbooks or supported shared sources | Refreshes through the saved query | Source paths, access, and selected workbook objects must remain usable |
| Excel Consolidate | Summary calculations across corresponding ranges | Can create links to source data | Summarizes ranges rather than appending all records |
| VSTACK or sheet-reference formulas | A few fixed, compatible ranges | Formula results recalculate from referenced cells | References need maintenance as the workbook set or ranges change |
| Google Sheets IMPORTRANGE | A specified range from another Google spreadsheet | Google checks for updates hourly while the receiving document is open | Requires source access; not instant, and dependent imports can create cycles |
Set up a reliable consolidation
- Standardize column names, order, and data types before combining files.
- Keep source workbooks in a stable location and avoid changing sheet or table names used by a query.
- Decide whether new files should be included automatically or only after review; a dedicated folder makes that boundary clearer.
- After the first load, inspect sample rows and totals to catch mismatched headers, missing columns, or duplicate source files.
- Use refresh-based queries for repeatable batches, and formulas or range imports only when their fixed references and access permissions are manageable.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
