Use Excel's Copilot to Build Recurring Compensation Reports

Tool:Microsoft Excel

AI Feature:Copilot

Time:10-15 minutes

Difficulty:Beginner

Microsoft Excel

What This Does

Copilot in Excel creates pivot tables, conditional formatting rules, and analysis formulas for your standard monthly compensation reports: the ones you rebuild from scratch every time leadership asks for a pay distribution or headcount-by-grade breakdown.

Before You Start

Your HRIS data export is pasted into Excel (columns: employee ID, name, department, job grade, salary, market midpoint, compa-ratio)
You have a Microsoft 365 subscription with Copilot enabled
The data is in a named Table (select your data range → Insert → Table)

Steps

1. Open Copilot

Click the Copilot button in the Home ribbon. The Copilot panel opens on the right side of your screen.

2. Ask for the analysis or visualization you need

Type your request in plain language. Copilot can create pivot tables, add columns with calculated fields, apply conditional formatting, and generate charts, all from natural language.

3. Review and apply

Copilot will show a preview of what it's about to do. Click Apply to execute. You can then ask follow-up questions to refine the output.

Real Example

Scenario: Leadership wants a monthly report showing average compa-ratio by department and job grade, with employees below 80% flagged in red.

What you type in Copilot: "Create a pivot table showing average compa-ratio by department (rows) and job grade (columns). Then apply conditional formatting to the compa-ratio values: red fill for below 0.80, yellow fill for 0.80 to 0.85, green fill for above 1.10."

What you get: A formatted pivot table with color-coded compa-ratio cells that you can refresh with a single click whenever you paste in new HRIS data.

Tips

Build your standard reports once using Copilot, then save the Excel file as a template. Next month, paste in new data and click Refresh on the pivot table.
For pay distribution charts, ask: "Create a histogram showing the distribution of compa-ratios across all employees, with bins at 0.70, 0.80, 0.90, 1.00, 1.10, 1.20." This replaces 20 minutes of manual chart configuration.
Ask Copilot to add a summary statistics row: "Add a row at the bottom showing the 25th, 50th, and 75th percentile compa-ratio for each department column."

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