iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
For recurring batches of similarly structured Excel files, use Power Query’s folder connector: select a dedicated folder, combine the files, and refresh the query when new workbooks arrive. If you mean totals or averages across matching ranges rather than one appended list, use Excel’s Consolidate feature instead. The right method depends on whether you need to append rows, summarize categories, or import a few known ranges.
Choose the method that matches the result you need
| What you need | Best-fit method | Key limitation |
|---|---|---|
| Append rows from recurring, similarly structured Excel workbooks | Power Query from a folder | Files need compatible layouts for straightforward combination; keep the folder’s contents controlled. |
| Import data from a few known workbook sources | Power Query workbook import | You must select and maintain the source locations and ranges. |
| Calculate totals, averages, or counts across corresponding ranges | Excel Consolidate | It summarizes aligned data; it does not append every source row into one transaction list. |
| Stack a few fixed ranges in a workbook | VSTACK or sheet-reference formulas | Formula references do not discover arbitrary files or adjust themselves to a changing workbook set. |
| Import a range from another Google spreadsheet | IMPORTRANGE | Requires source access and checks for updates hourly while the receiving document is open. |
Before setting up an automated workflow, standardize column headers and keep the data in list form without entirely blank rows or columns. Microsoft says consistent headers and list-shaped data help produce reliable combinations. Microsoft’s guidance on combining sheets explains the underlying data-shaping considerations.
1. Combine a recurring folder of Excel workbooks with Power Query
This is the strongest fit when new workbooks arrive repeatedly and use the same schema. Power Query can combine files in one folder into a table, then apply the saved transformation steps when you refresh the query. See Microsoft’s folder-import instructions and its Combine files overview.
- Put only the intended source workbooks in a dedicated folder. Avoid mixing in unrelated files or subfolders; if that cannot be avoided, filter the query by file extension or path.
- In Excel, open Data > Get Data > From File > From Folder. Menu labels can vary by Excel version and platform.
- Review the listed files to confirm they are the intended inputs.
- Choose Combine and Transform to inspect and shape the data before loading, or Combine and Load to load the combined result directly.
- After the query is set up, add new matching workbooks to the folder and refresh the query to incorporate them.
The folder connector may include files in subfolders, so do not treat the chosen folder as a boundary unless you have checked the file list or applied suitable filters. For clean results, keep headers and column types consistent across source workbooks. Power Query’s folder workflow is supported in Microsoft 365, Excel 2024, 2021, 2019, and 2016 according to the cited Microsoft support page; confirm your platform and edition before following the menu path.
#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
2. Import selected workbooks with Power Query
Use this approach when you have a known set of workbook sources, or when the files live on a supported shared service rather than in one local folder. The Excel connector lets you choose workbook information and load it or transform it first. See Microsoft’s Power Query Excel connector documentation.
- Open Excel’s data import flow and select the workbook source.
- Choose the required sheet, table, or range in the workbook navigator.
- Transform the data as needed, such as removing irrelevant columns or aligning headers.
- Load the result into the workbook, or continue transforming it in Power Query before loading.
For multiple files, prefer the Folder or SharePoint Folder connector where available rather than building a separate import for every file. Exact menus differ by Excel version and platform, so verify that your edition supports the connector and path you intend to use.
3. Summarize corresponding ranges with Excel Consolidate
Excel’s Consolidate command is for summary results such as totals, averages, or counts drawn from corresponding ranges. It can use worksheets in the same workbook or other workbooks. Microsoft describes the workflow in Consolidate data in multiple worksheets.
Windows 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 reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute- Open the destination worksheet and select the cell where the summary should begin.
- Choose Data > Consolidate, then select the summary function you need.
- Add each source range. Choose consolidation by position if the ranges share the same order and labels; choose by category if the labels are what should match.
- If you want linked results that can update when source data changes, select Create links to source data.
Use this for a summarized view of aligned ranges, not when you need every original record appended as a row in one table.
4. Stack a small, fixed set of ranges with VSTACK or formulas
For a few known ranges with compatible columns, Microsoft’s example uses =VSTACK(Sheet1!A1:D50, Sheet2!A1:D50, Sheet3!A1:D50) to stack ranges into one list. The combined list updates when the source data changes. See Microsoft’s examples for combining data from sheets.
Formula references are convenient when the source locations are stable, but hard-coded ranges need maintenance if a range grows or the set of source workbooks changes. VSTACK does not automatically discover every workbook in a directory. For a recurring folder of incoming files, use Power Query instead.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.5. Import a range from another Google spreadsheet with IMPORTRANGE
In Google Sheets, IMPORTRANGE imports a specified range from another spreadsheet. The source must be accessible to you, and the first connection can require you to grant access. The function is suited to a few known spreadsheets and ranges, not an automatically discovered folder of Excel files. Read Google’s IMPORTRANGE documentation for syntax and access details.
IMPORTRANGE is not instant synchronization: Google says it checks for updates hourly while the receiving document is open. Google also advises limiting receiving sheets because each one reads from the source, and warns that spreadsheets referencing one another can create cycles.
Quick Recap
How to decide between the five approaches
- Appending or summarizing: Choose Power Query, VSTACK, or an import workflow to combine records; choose Consolidate when you need calculations across corresponding ranges.
- Recurring folder or fixed sources: A folder connector suits a regular batch of files. Selected-workbook imports, formulas, and IMPORTRANGE suit known sources whose locations are maintained deliberately.
- Stable or changing schemas: Similar headers and list-shaped data make Power Query combinations more dependable. If columns vary, inspect and transform the inputs before loading rather than assuming they will align.
- Excel or Google Sheets: Check version and platform support for Excel connectors. Use IMPORTRANGE when the sources are Google spreadsheets and the required ranges are known.
- Refresh or formula-based updates: Power Query updates through query refresh; formulas recalculate from their references; IMPORTRANGE checks hourly while the receiving document is open.
- Access and source stability: Confirm that shared locations, workbook paths, and spreadsheet permissions will remain available to the people and refresh process that need them.
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.
