Use Excel's Copilot to Build Pay Equity Analysis Pivot Tables

What This Does

Copilot in Excel builds the pivot tables and conditional formatting for pay equity reviews (compa-ratio distribution by demographic group, average salary by gender and job grade, and outlier flagging) based on what you describe in plain English. What used to take 30–45 minutes of pivot building is now a 5-minute conversation.

Before You Start

• You have Excel open with a Microsoft 365 subscription that includes Copilot
• Your employee data is in a table with columns: Employee ID, Job Title, Grade, Salary, Compa-Ratio, Gender, Department (or equivalent)
• Sensitive fields are in a file you're comfortable working with on your corporate device

Steps

1. Format your data as a table

Click anywhere in your data, press Ctrl+T to convert it to an Excel table. This helps Copilot understand your column names accurately.

2. Open Copilot

Click the Copilot button in the Home ribbon (sparkle icon, right side). The Copilot panel opens.

3. Ask for your analysis

In the Copilot chat box, describe the analysis you want. Be specific about which columns to use and what you want to see.

4. Review and insert

Copilot generates a pivot table or formula. Click Add to new sheet to insert it without disturbing your raw data. Review the output before sharing.

Real Example

Scenario: You have a spreadsheet with 1,200 employees and you need to quickly check whether compa-ratios differ by gender and job grade before your quarterly pay equity review meeting.

What you type in Copilot: "Create a pivot table showing average compa-ratio by job grade (rows) and gender (columns). Add a row showing the count of employees in each cell. Highlight cells where the difference between genders exceeds 0.05 in red."

What you get: A pivot table with average compa-ratios by grade and gender, employee counts, and conditional formatting highlighting significant disparities, all in about 15 seconds.

Tips

• Ask Copilot to also create a chart: "Create a bar chart comparing average compa-ratios by gender for each job grade." This is useful for leadership presentations.
• Be careful about distributing output: pay equity pivot tables contain sensitive demographic data. Always check your company's data handling policy before sharing via email.
• If your demographic data uses codes (M/F/NB or 1/2/3), add a note in your Copilot prompt explaining what the codes mean.

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