Use Excel's AI to Generate Compensation Formulas

What This Does

Excel Copilot writes the exact formulas for compa-ratio calculations, range penetration, salary structure modeling, and pay equity flagging: the formulas you use every week but still have to look up or rebuild from scratch each time.

Before You Start

• Microsoft 365 with Copilot enabled
• Your data is in an Excel table or named range with clear column headers
• Data is clean (no merged cells in your data range)

Steps

1. Label your columns clearly

Before asking Copilot for formulas, make sure your spreadsheet columns have descriptive headers. Copilot uses column names to understand what data it's working with. Good examples: "Current Salary," "Grade Midpoint," "Grade Min," "Grade Max," "Hire Date," "Performance Rating."

What you should see: Row 1 contains clear, descriptive column headers.

2. Open Copilot from the Home tab

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

3. Ask for your first formula in plain English

Describe exactly what you need to calculate. Be specific about column locations if you know them, but Copilot can figure them out from column headers too.

Example for compa-ratio:

Calculate the compa-ratio for each employee in column I. Compa-ratio = current salary divided by the grade midpoint. Format as a percentage.

What you should see: Copilot proposes a formula like =D2/E2 or a more complex version referencing your specific column headers.

4. Insert the formula and verify

Click Insert to add the formula. Check 3–4 known employees to verify the math is correct. A compa-ratio of 1.00 means the employee is at exactly the midpoint of their grade.

5. Ask for additional formulas as needed

Keep the Copilot panel open and continue asking. Each request builds on the same sheet context.

Common comp formulas to request:

Range Penetration (where is the employee within their range):

Add column J: range penetration = (current salary minus grade min) divided by (grade max minus grade min). Format as percentage. This shows where each employee sits within their grade range from 0% (at min) to 100% (at max).

Pay Equity Flag:

Add column K: flag employees whose compa-ratio in column I is below 0.85 as "Equity Risk" and all others as "OK". Sort the sheet so "Equity Risk" employees appear at the top.

Annualized Cost of Increase:

In column L, calculate the annualized cost of bringing each "Equity Risk" employee to a 0.85 compa-ratio: that's (0.85 × grade midpoint - current salary). Show 0 for employees already at or above 0.85.

Real Example

Scenario: Your CHRO asks you to run a quick pay equity check on 200 employees before a board meeting next week. You need compa-ratios, range penetration, and a flag for anyone below 85% of midpoint, plus the total cost to remediate.

What you type into Copilot (four requests in sequence):

1. "Calculate compa-ratio in column I = Current Salary / Grade Midpoint"
2. "Calculate range penetration in column J = (Current Salary - Grade Min) / (Grade Max - Grade Min)"
3. "Flag column K: 'Equity Review' if compa-ratio < 0.85, 'OK' otherwise"
4. "Column L: cost to remediate = MAX(0, (0.85 × Grade Midpoint) - Current Salary). Sum at bottom of column."

What you get: A complete pay equity snapshot in 15 minutes. The sum at the bottom tells you exactly what remediation would cost.

Tips

• If Copilot gives you a formula that references wrong columns, just say "That formula referenced the wrong column. Compa-ratio should use column D for salary and column F for midpoint" and it will correct it
• For large datasets, ask Copilot to "apply this formula to all rows in the table" rather than dragging down manually
• Save a "formula library" sheet in your workbook where you paste the formulas Copilot generates. Reuse them next cycle without asking again.

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