Use Excel's AI to Automate Compensation Reports

What This Does

Excel Copilot turns your raw HRIS salary export into a formatted compensation report with summary tables, charts, and a written narrative, without you manually building pivot tables or writing summaries from scratch. What used to take 4–6 hours takes under an hour.

Before You Start

• Microsoft 365 with Copilot enabled
• You have a salary data export from your HRIS (Workday, SuccessFactors, ADP, etc.) saved as an Excel file
• The export has columns for: employee name/ID, department, job grade, current salary, performance rating, and any other fields you report on
• Sensitive data handling: confirm your organization's policy on using Copilot with employee data. Some organizations restrict AI features for PII.

Steps

1. Open your HRIS export in Excel

Open the salary export file. Make sure there's a clear header row. If the data came out with multiple blank rows or merged cells at the top, delete those first so row 1 is your column headers.

What you should see: A clean spreadsheet with employees in rows and labeled data in columns.

2. Convert data to a table

Select your data range, go to Insert → Table, and confirm the table includes your headers. Named tables give Copilot much better context.

3. Open Copilot and ask for a summary

Click the Copilot button in the Home ribbon. Start with a high-level summary request:

Summarize this compensation dataset. How many employees are there, what is the average and median salary by department, and what is the distribution across performance ratings?

What you should see: Copilot provides a written summary and may offer to create charts or pivot tables.

4. Ask for the specific report tables you need

Based on what your CHRO or HR leadership typically wants, ask for the specific breakdowns:

Create a pivot table showing average salary, median salary, and headcount by department and pay grade. Sort by department alphabetically.

Create a chart showing salary distribution as a histogram. Use $10,000 salary bands.

Show the percentage of employees in each performance rating category, and the average merit increase percentage for each rating group.

5. Ask Copilot to write the narrative

After the tables and charts are generated, ask Copilot for the written summary:

Write a 200-word executive summary of this compensation data highlighting: the departments with the highest and lowest average salaries, any grades where average pay is significantly above or below midpoint, and the distribution of employees by performance rating. Write in a professional tone for an HR leadership audience.

What you should see: A written paragraph ready to paste into your PowerPoint or email to leadership.

6. Save the report template

Once Copilot has built your tables, charts, and narrative structure, save this workbook as a template. Next month, replace the data tab with the new HRIS export and the formulas will update automatically. Then ask Copilot to refresh the narrative with updated numbers.

Real Example

Scenario: Your VP of HR asks for the monthly compensation health check: headcount by grade, average salary vs. midpoint by department, and any notable changes since last month.

What you type: "I have a 450-person salary dataset. Create: (1) A table showing headcount, average salary, and average compa-ratio by pay grade. (2) A department summary showing average salary vs. grade midpoint. (3) A written paragraph for the CHRO summarizing the biggest outliers — departments where average compa-ratio is below 0.90 or above 1.10."

What you get: Within 5–8 minutes of Copilot requests, you have the complete report. What previously took 4 hours now takes 45 minutes including your review and cleanup.

Tips

• Break large requests into smaller steps. Copilot handles "create this table" better than "create this entire report."
• If the narrative has wrong numbers, paste it back to Copilot and say "Correct this paragraph. The Engineering department average is $118,000, not $112,000" and it will rewrite accordingly.
• Always verify Copilot's summary statistics against your own manual check of 3–5 rows before distributing to leadership

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.