Automating Excel Reports with Power Automate: A Complete Business Scenario

Introduction

One of the most common pain points I see in organizations is the manual process of compiling Excel reports, analyzing data, and sending summaries via email. What typically takes 2-3 hours manually can be completely automated with Power Automate. In this guide, I'll walk you through a real-world scenario: automating a weekly sales report that reads data from Excel Online, processes each row, generates insights, and emails a summary to stakeholders.

The Business Scenario

Imagine you're managing a sales team that records daily transactions in an Excel file stored in SharePoint. Each week, you need to:

• Read all transactions from the past seven days
• Calculate total sales by region
• Identify top performers
• Create a formatted report file
• Email the summary to leadership

Without automation, this requires manual work. With Power Automate, it runs every Monday morning while you have coffee.

Step 1: Connect to Excel Online and Read Data

The foundation of your automation is reading data from Excel. Start by creating a new cloud flow scheduled for weekly execution.

Action: Add "List rows present in a table"

This connector allows you to read all rows from your Excel table:

Location: /sites/Sales/Shared Documents
Document Library: Shared Documents
File: Weekly_Sales.xlsx
Table: SalesData

Make sure your Excel data is formatted as a proper table with headers. This is critical—Power Automate recognizes tables, not ranges.

Step 2: Process and Filter Rows

Once you've retrieved the data, you'll want to filter it. Use the "Filter array" action to isolate relevant records. For our example, we'll filter transactions from the last 7 days:

Filter condition:
Field: TransactionDate
Operator: is greater than
Value: @addDays(utcNow(), -7)

Next, use the "Group by" feature to aggregate data by region. This prepares your data for the summary email.

Step 3: Calculate Summaries and Create Formatted Content

Now comes the intelligence layer. Use actions like:

For total sales calculation:

Action: Compose
Inputs: sum(map(items('Apply_to_each'), int(item()['SalesAmount'])))

For identifying top performers:

Action: Sort array
Array: filtered sales data
Sort by: SalesAmount
Order: Descending
Limit to top 5 items

Compose an HTML table for your email using the "Create HTML table" action. This transforms your array data into a professional-looking format.

Step 4: Create a New Excel File with Results

Many stakeholders prefer having the data in Excel format. Use the "Create CSV table" action followed by "Create file" in your SharePoint document library:

Action: Create file
Site Address: /sites/Sales
Folder Path: /Reports/Weekly
File Name: Weekly_Report_@{utcNow('yyyy-MM-dd')}.xlsx
File Content: [your formatted data]

This creates a timestamped report file that stakeholders can access and archive.

Step 5: Generate and Send Summary Email

With data processed and files created, compose a professional email:

Action: Send an email (V2)
To: leadership@company.com
Subject: Weekly Sales Report - @{utcNow('MMMM dd, yyyy')}
Body: 

Hi Team,

Attached is this week's sales summary:

[Insert your HTML table here]

Total Sales: $@{variables('totalSales')}
Top Performer: @{first(outputs('Top_Performers'))['EmployeeName']}

Full details available in SharePoint: [link to report]

Best regards,
Automation Bot

Include a link to the newly created Excel file so stakeholders can drill into the data.

Step 6: Schedule Your Flow

Finally, configure the trigger:

Scheduled cloud flow

• Frequency: Week
• Every: 1 Week
• On: Monday
• At: 8:00 AM

This ensures the report lands in inboxes early Monday morning.

Error Handling and Monitoring

Add a "Configure run after" action to your email step to handle failures gracefully:

• If the flow fails, send an alert email to IT
• Log failures to a SharePoint list for troubleshooting
• Use the "Retry" policy for API actions that might timeout

Pro Tip

Include a "date range" parameter in your Excel file that the flow reads at the start. This allows you to modify the reporting period without touching the flow logic. Store it in a named range like "ReportDays" and reference it with variables('daysBack'). This makes your automation flexible and maintainable—always aim for dynamic solutions over hardcoded values. Additionally, version your Excel template separately from your data file; this prevents accidental data deletion and makes recovery seamless if something goes wrong.

Conclusion

Automating Excel reports with Power Automate transforms hours of manual work into a reliable, repeatable process. Start with this scenario, refine it based on your needs, and soon you'll find dozens of similar opportunities in your organization.