Automate Excel Reports with Power Automate Desktop

What You’ll Build

In this guide, you’ll create a Power Automate Desktop flow that reads data from multiple Excel workbooks in a folder, consolidates the data into a single summary spreadsheet, and optionally emails the report to your team. This is one of the most common RPA use cases, eliminating the weekly “copy-paste from five spreadsheets” routine.

By the end, you’ll understand how to work with Excel actions, loops, variables, and folder operations in PAD.

Prerequisites

• Power Automate Desktop installed and signed in (see Getting Started with PAD)
• Windows 10 or 11 (64-bit)
• Microsoft Excel installed locally
• 2–5 sample Excel files in a folder, each with a consistent column structure (e.g., Date, Department, Amount)
• Basic understanding of PAD, you should know how to create and run a flow

Business Context

Every organisation has reporting processes that involve collecting data from multiple sources. Finance teams consolidate regional expense reports. Operations teams merge shift logs. HR teams combine training completion records. These processes share a pattern: open several files, copy rows, paste into a master sheet, format, and send.

This is exactly the kind of repetitive, rule-based, high-volume task that PAD handles well, and it’s a great second flow to build after your initial “hello world” automation.

Step-by-Step Guide

1. Set Up Your Source Folder

Organise your source Excel files in a single folder. For this guide, create a folder such as C:\Reports\Weekly\ and place your sample spreadsheets there. Each file should have the same column headers in row 1.

2. Create a New Flow

1. Open Power Automate Desktop and click New flow
2. Name it “Excel Report Consolidator”
3. The flow designer opens with an empty canvas

3. Get Files from the Folder

1. In the Actions pane, expand Folder and drag Get files in folder onto the canvas
2. Set the folder path to your source folder (e.g., C:\Reports\Weekly\)
3. Set the file filter to *.xlsx
4. This produces a list variable %Files% containing all matching file paths

4. Create the Output Workbook

1. Add a Launch Excel action, select “with a blank document”
2. This creates the master report. PAD stores a reference in %ExcelInstance%
3. Add a Write to Excel worksheet action to write your column headers (Date, Department, Amount) to row 1

5. Loop Through Source Files

1. Add a For each loop using the %Files% variable
2. Inside the loop, add Launch Excel to open the current file (%CurrentItem%)
3. Add Read from Excel worksheet, read all rows starting from row 2 (skip headers)
4. Add Write to Excel worksheet to append the read data to your master workbook
5. Add Close Excel to close the source file (without saving changes)

6. Save the Consolidated Report

1. After the loop ends, add a Save Excel action
2. Set the file path to your output location, e.g., C:\Reports\Consolidated\weekly-report-{date}.xlsx
3. Use a Get current date and time action to generate a dynamic filename

7. Optional, Email the Report

1. Add an Outlook: Send email action
2. Set the recipient, subject line, and body
3. Attach the consolidated file
4. This requires Outlook to be installed and configured

8. Run and Verify

1. Press F5 or click Run
2. Watch PAD open each file, read the data, and write it to the master sheet
3. Open the output file and verify all rows are present

Testing and Validation

• Row count check: Sum the data rows across all source files and compare with the output
• Data integrity: Spot-check 5–10 rows to ensure values weren’t truncated or shifted
• Empty files: Add an empty Excel file to the source folder, your flow should handle it gracefully
• Locked files: Test what happens when a source file is already open
• Large files: Test with a file containing 10,000+ rows to check performance

Governance Considerations

• File access: Ensure the service account (or the user running the flow) has read access to the source folder and write access to the output folder
• Data classification: If the spreadsheets contain sensitive data (financial, personal), ensure the flow runs on an appropriately secured machine
• Scheduling: Use Power Automate cloud flows to trigger this desktop flow on a schedule (e.g., every Monday at 7 AM)
• Error notifications: Add error handling to send a Teams message or email if the flow fails
• Versioning: Keep a backup of the previous consolidated report before overwriting

Common Gotchas and Troubleshooting

“Excel instance not found” error: This happens when PAD tries to write to a workbook that was closed unexpectedly. Add error handling around the Excel actions and ensure you’re referencing the correct Excel instance variable.

Data written to wrong columns: This occurs when source files have inconsistent column orders. Standardise your templates or add column-mapping logic inside the loop.

Flow runs slowly with many files: PAD opens and closes Excel for each file. For 20+ files, consider reading all data into a data table variable first, then writing once. This reduces Excel launch overhead.

Date formats change: Excel may interpret dates differently depending on regional settings. Use the “Set variable” action to format dates explicitly before writing.

Taking It Further

• Add filtering: Only include rows that match certain criteria (e.g., amounts over a threshold)
• Pivot tables: Use Excel actions to create a pivot table summary in the output
• Cloud integration: Store source files in SharePoint and use a cloud flow to download them before running the desktop flow
• Error logging: Write a log file with timestamps, file names processed, and row counts for audit purposes
• Scheduled runs: Combine with Power Automate cloud flows to run unattended on a schedule