Yes. Power Automate can consolidate data from multiple Excel workbooks by reading rows from a named table in each source file and adding them to a table in a destination workbook. For a reliable flow, keep the source tables consistent, then use a scheduled or file-based flow with Excel Online (Business) actions to list and append the rows.
What consolidating Excel files means
In this context, consolidation usually means combining records from several workbooks into one central table. For example, each branch might maintain a monthly workbook with the same columns—Date, Branch, Product, and Units—and a flow can append those records to a master table. The result is one growing dataset that can be filtered, reported on, or analyzed in Excel.
That is different from merging worksheets, copying a workbook’s formatting, or combining arbitrary ranges. Power Automate’s Excel connector works with Excel tables, not just any occupied cells. It transfers table rows; it does not automatically recreate each source worksheet’s layout, charts, formulas, or visual formatting in the destination.
It is also important to distinguish a one-time import from ongoing consolidation. A flow that runs every day and rereads every file will append the same records again unless you design it to identify files or rows it has already processed. The best design depends on whether files arrive once, change over time, or need to be re-imported.
How the flow works
A typical cloud flow has three parts: find the source files, read their table rows, and add those rows to the destination table. The workbooks generally need to be stored in OneDrive for Business or SharePoint so the Excel Online (Business) connector can access them. The flow runs in the cloud; Excel desktop does not need to be open.
- Start with a Recurrence trigger for a periodic batch, or an appropriate SharePoint or OneDrive trigger when a file arrives.
- Use a file-listing action, such as SharePoint’s Get files (properties only), to find workbooks in the relevant folder. Filter the results to the intended Excel files if the folder also contains other documents.
- Inside an Apply to each loop for the files, use Excel Online (Business) — List rows present in a table. Supply the current file and the source table name.
- Inside a second Apply to each loop for the returned rows, use Excel Online (Business) — Add a row into a table to write each record to the destination table.
The source and destination workbooks must be selected from supported cloud storage locations. In a flow that processes many files, file identifiers from the file-listing action can be used to identify each workbook. The destination workbook and its table should be fixed and easy to locate. If the flow offers a table selector that cannot resolve a changing source file at design time, verify the action’s dynamic file and table inputs before building the full loop; a stable table name across all input files makes this much easier.
Prepare the workbooks before building the flow
Consistency is the main prerequisite. In every source workbook, select the data range and create an Excel table using Insert > Table, or press Ctrl+T in Excel for Windows. On Mac, the shortcut is Command+T. Confirm that the table has headers, and give it a clear name from the Table Design tab. The destination data range must also be an Excel table. A plain range with headings is not enough for the connector’s table-row actions.
Use the same column names and compatible data types across the source files. For example, if one file calls a field Customer ID and another calls it Client Number, the flow cannot safely assume they refer to the same destination column without explicit mapping. Align the headers or add mapping logic in the flow. Check that dates are actual date values rather than inconsistently formatted text, and store identifiers such as account numbers as text if leading zeros matter.
- Keep a single header row and avoid merged cells in the table.
- Use one stable table name in all source workbooks, such as SourceData.
- Make destination columns match the fields you plan to map.
- Decide how blank cells, extra columns, and malformed rows should be handled.
- Consider adding SourceFile and ImportedAt columns to the destination for traceability.
A table can grow as people add rows, which is useful for recurring submissions. However, each file should contain the records intended for import only. Totals, notes, and explanatory text should sit outside the table, or they may be treated as data rows.
Choose a trigger and an import pattern
A scheduled flow is a practical choice when staff upload files throughout the day or when the process needs to run at a predictable time. For instance, a nightly flow can scan a SharePoint folder for new monthly submissions. A file-created trigger can reduce the delay between upload and import, but it needs care: a flow may start while a file is still being uploaded or edited. In that situation, use a suitable delay or retry approach and verify that the workbook is ready before reading it.
For files that arrive once and should be imported once, record which files have been processed. One approach is to move a successfully processed file to an Archive folder. Another is to maintain a SharePoint list of processed file identifiers and check it before importing. If a flow can run again after a partial failure, this record helps avoid duplicating data, but it does not by itself solve duplicate rows inside a file.
For files that can be revised after their first upload, decide whether a revision means replacing the earlier contribution or importing a new version. Simply appending the revised file produces duplicate or outdated records. A more controlled pattern uses a stable business key, such as Order ID, and updates or replaces matching destination records. That requires additional lookup and update logic; it is not what Add a row into a table does by itself.
Prevent duplicates and preserve a useful audit trail
Appending is not the same as synchronizing. Each successful Add a row into a table action creates another destination row. If the same flow is rerun over the same set of source files, it will usually add those rows again. Before deployment, decide what makes a row unique. It could be a transaction ID, or a combination such as SourceFile plus SourceRowID. A timestamp alone is rarely a good uniqueness key if a row can be imported more than once.
For a small dataset, a flow might check whether the destination already contains a matching key before adding the row. That is understandable but can become slow when every source row triggers a search through a large destination table. For a regular high-volume process, consider storing import state separately, using a database designed for record-level operations, or restructuring the process around files that are processed once and archived.
Adding the source filename, import time, and—where available—a source record identifier makes it easier to investigate a wrong or repeated entry. Keep in mind that logging a file as processed before all its rows have been written can cause missing records after a failure. Mark it complete only after successful processing, and consider logging an error state so someone can retry or investigate incomplete runs.
Limits, performance, and reliability
The Excel connector is convenient for modest, table-based workflows, but it is not a bulk data-loading engine. A flow that inserts records one at a time can take a while when many files contain many rows. Avoid unnecessary work inside nested loops, test with a representative batch, and monitor the flow’s run history. If processing is slow or unreliable at the intended volume, assess whether the data belongs in a more suitable storage system.
In List rows present in a table, pagination is important when a table may exceed the action’s default return size of 256 rows. Open the action’s Settings, turn on Pagination, and set a threshold appropriate to the expected data volume and flow limits. Check the run output to confirm that the action returned all the records you expected; a successful run does not necessarily mean an unpaginated query read every row.
Microsoft documents a 25 MB file-size limit for Excel Online (Business) connector operations. The connector can also lock a workbook for updates or deletion for a period after use, and Microsoft warns that simultaneous modifications to the same workbook by multiple clients or flows are not supported. Avoid having people edit the destination workbook while a flow is writing to it, and avoid parallel runs that write to the same table. Sequential processing is safer when preserving a predictable write order matters.
Excel workbooks can also be affected by formulas, data validation, protection, and inconsistent cell contents. Test the actual files that users will submit, not just a clean sample. If a run fails, inspect its run history for the failing action and file; then check that the workbook is accessible, the table still exists, the columns match, and the file is not locked or being edited.
Power Automate or Power Query?
Power Automate is useful when consolidation is part of a process: files arrive in a folder, someone needs a notification, imported files must be archived, or the result needs to be sent to another service. It can react to events and coordinate actions across Microsoft 365 services. Its row-by-row approach, however, can add complexity when the only goal is to combine many similar files for analysis.
Power Query is often a better fit for repeatable analytical imports. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Folder, select the folder, and choose Combine & Transform Data. Power Query can combine files with a consistent structure and let you clean or reshape the results, then refresh the query when the source files change. Excel for Mac has Power Query features, but availability and connector options can differ by version; check the commands available in your installation. Power Query refreshes data into a query result rather than acting as an event-driven workflow that archives files or sends notifications.
You can also use both tools: Power Automate can place incoming files in a controlled folder and notify the team, while Power Query combines that folder’s contents for reporting. If multiple people or business systems need to update records reliably at scale, a database or Dataverse may be a better destination than a shared Excel workbook.
Common problems to check
- No rows are returned: Confirm the range is an actual table, the selected table name is correct, and the flow account can access the workbook.
- Only some rows appear: Check pagination on List rows present in a table and compare the action output with the table’s row count.
- Columns are blank or misplaced: Review the field mapping and make sure source headers and destination columns are consistent.
- The flow fails on a particular file: Look for a missing table, a renamed header, an unsupported or damaged workbook, or a file that is still being edited.
- Records appear more than once: Check whether the flow reprocessed old files or retried after a partial run without an idempotency check.
Do not assume that an Excel file is ready just because it appears in a folder. Uploads, edits, and synchronization can take time. A dependable process checks file readiness, limits competing writes, and records both successful and unsuccessful processing.
Frequently Asked Questions
Can Power Automate combine Excel files with different columns?
It can, but the flow needs explicit mapping and rules for missing or extra fields. Standardizing the source table headers and data types first is simpler and less error-prone.
Do the source workbooks need to be open?
No. A cloud flow reads files stored in supported cloud locations, such as OneDrive for Business or SharePoint. The files do need to be accessible to the account used by the flow.
Can a flow consolidate multiple worksheets in one workbook?
Yes, if the data on each worksheet is formatted as a table and the flow reads each relevant table. The Excel connector does not automatically combine arbitrary sheet ranges just because they are in the same workbook.
Why does my flow import only 256 rows?
List rows present in a table returns 256 rows by default. Enable Pagination in that action’s Settings and set an appropriate threshold, then verify the returned rows in the run history.
Will the flow update existing rows instead of adding duplicates?
Not automatically. Add a row into a table appends a new record. To update existing records, define a unique key and build logic to find and update matches, or use a storage system better suited to record updates.
Is Power Automate better than Power Query for combining files?
It depends on the purpose. Choose Power Automate for event-driven steps such as processing arrivals, archiving, and notifications. Choose Power Query when the main task is to combine and transform files for analysis or reporting.
Related
- Excel Remove Duplicates from a Column: What to Know
- Excel Remove Print Area: Clear It Without Deleting Data
- Excel Protect Sheet: How to Lock Cells and Set Permissions
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Switch Excel Columns From Letters to Numbers?To switch Excel column headings from letters to numbers in Microsoft 365 for Windows, select File > Options…
- Wedding Budget Spreadsheet for Beginners: A Simple SetupA beginner wedding budget spreadsheet should show your total spending limit, planned costs, final or contracted costs, and…
- How to Copy and Paste Only Filtered Data in Excel SafelyTo copy only filtered records in Excel for Microsoft 365 on Windows, select the filtered range, press Alt+;…
- How to Create a Table Template in Word for Easy ReuseTo make a reusable table in Word, create and format the table, select it, then choose Insert >…
- Google Sheets IMPORTRANGE From the Same Sheet?No: IMPORTRANGE is intended to bring cells into a Google Sheets file from a different spreadsheet file. To…
- Excel Split Names in One Cell Into Two CellsTo split names in Excel, select the name cells and choose Data > Text to Columns > Delimited,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.