Use Excel's Copilot to Build Recurring Compensation Reports

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 has one clean header row (a named Table via Insert → Table helps but is no longer required, Copilot reads plain ranges too)

Steps

1. Open Copilot

Click the Copilot icon in the lower-right corner of the Excel window. The Copilot panel opens on the right side of your screen.

Microsoft moved Copilot off the Home ribbon in 2026, so this corner button is now the one entry point. Press F6 if you prefer the keyboard, or right-click the corner button and choose Move to ribbon to put it back up top.

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.