Excel Automation with Microsoft Copilot Agent

Build a Microsoft Copilot Agent for Monthly Excel Master Sheet Automation
1️⃣ Background & Objective
Every month, the business receives 4 Excel files with the same structure and names, but new data.
The goal is to build a Microsoft Copilot Agent that:
Accepts these 4 Excel files every month
Cleans and normalizes the data
Merges selected columns into a single Master Excel file
Outputs the Master file with two tabs:
Merchandisable
Non-Merchandisable
Provides a summary/validation report
Uses ONLY Microsoft-owned tools
Is built using Low-code / No-code (no heavy custom coding)
2️⃣ Input Data Description
Monthly Inputs
4 Excel files(merchandisable,published,mau at start date,mau at end date)
Each file:
Contains only one worksheet
Has fixed column names and positions
Contains ~1000 rows
Has duplicate App Names due to different Hub values
File names remain same every month
3️⃣ Core Join Key
App Name column is the only common key
All data merging is done column-wise using App Name
4️⃣ Data Pre-Processing Rules (Critical)
A. Handling Duplicate App Names (Within Same Sheet specially merchandisable sheet)
Problem:
Same app appears multiple times due to different Hub values
Required Logic:
Combine duplicate rows into one row per App Name
Text columns (e.g., Hub):
Combine using comma separation
Example: Teams, Harmony
Numeric columns:
Sum values
Example: Active Users = Teams + Harmony
B. Published Sheet App Name Normalization
Problem:
App names appear as:
Mural Update 1
Mural Update 7
Required Logic:
Normalize names to base app name:
Mural Update X → Mural
This normalization must happen before merging
5️⃣ Master Excel Output Structure
Master File Requirements
Output format: Excel (.xlsx)
Contains 2 sheets (tabs):
Sheet 1: Merchandisable
Contains apps that exist in the Merch Sheet
One row per App Name
Data merged column-wise from all 4 sheets
Fixed column order (predefined)
Sheet 2: Non-Merchandisable
Contains apps that exist in:
Published Apps – Merch Apps
One row per App Name
Same column structure as Merchandisable sheet
6️⃣ Validation & Summary (Mandatory)
The agent must provide a summary including:
Total apps processed
Number of Merchandisable apps
Number of Non-Merchandisable apps
Number of duplicate apps merged
Any missing apps across sheets (if applicable)
Summary should be:
Displayed in Copilot response
OR saved in a separate summary sheet
Technology Constraints (Very Important)
Allowed Tools (Microsoft Only)
Microsoft Copilot Studio
Power Automate Cloud Flows
Excel Online (Business)
Office Scripts
OneDrive for Business or SharePoint
Not Allowed:
Power Automate Desktop
VBA
Python
External APIs
Third-party tools or connectors
Custom backend services
Low-Code Requirement
Solution must be:
Configurable
Maintainable
Replicable in another tenant
Any scripting must be:
Limited to Office Scripts only
Well documented
No heavy custom code or external logic
Required Deliverables (VERY IMPORTANT)
The freelancer must deliver all of the following:
1. Detailed Step-by-Step Documentation (Mandatory)
End-to-end build guide covering:
Folder setup
Excel templates
Office Scripts creation
Power Automate flow (step-by-step actions)
Copilot Studio configuration
Should be written so that another person can replicate it exactly
2. Video Documentation (Mandatory)
Screen-recorded walkthrough showing:
Complete agent working
Uploading 6 files
Flow execution
Final master Excel output
Validation summary
Video should be clear enough to rebuild the agent by watching
3. Sample Files
Sample input Excel files
Master Excel template
Screenshots of flow and copilot configuration
Mandatory Skills:
Microsoft Copilot Studio
Power Automate (Cloud Flows)
Excel Online (Business)
Office Scripts
Data transformation & normalization logic
Low-code solution design
Nice to Have
Power Platform solution documentation
Enterprise automation experience
Data validation and reporting