Use Excel's AI to Build Merit Cycle Models

What This Does

Excel Copilot can write the complex formulas behind merit matrices, budget models, and compa-ratio calculations, eliminating the hours you'd normally spend building them from scratch or debugging nested IFs that break mid-cycle.

Before You Start

• You have Microsoft 365 with Copilot enabled (check with your IT team if unsure, as Copilot requires an M365 Business or Enterprise subscription)
• Your salary data is in Excel with columns for employee name, current salary, pay grade, performance rating, and grade range minimum/midpoint/maximum
• You're logged in to your Microsoft account

Steps

1. Open your merit cycle workbook

Open your existing compensation spreadsheet (or start a new one). Make sure your headcount data has clearly labeled columns. Copilot works best when columns have descriptive headers like "Current Salary," "Performance Rating," "Grade Min," "Grade Max."

What you should see: A spreadsheet with employee rows and labeled compensation data columns.

2. Open Copilot in Excel

Click the Copilot button in the Home tab ribbon (looks like a small sparkle/star icon). A chat panel will open on the right side of your screen.

Troubleshooting: If you don't see the Copilot button, your organization may not have enabled it yet. Contact IT or check your Microsoft 365 admin settings.

3. Ask Copilot to build your merit matrix formula

Type your request in plain English. Be specific about your merit matrix logic. For example:

Build a formula in column H that calculates merit increase percentage based on the performance rating in column D:
- "Exceeds Expectations" = 5%
- "Meets Expectations" = 3%
- "Developing" = 1.5%
- "Below Expectations" = 0%
Apply to all employee rows. Cap the result so no one exceeds their grade maximum in column G.

What you should see: Copilot proposes a formula (usually a nested IF or IFS statement) and offers to insert it into your spreadsheet.

4. Review the formula and insert it

Read the formula Copilot generated. Click Insert or Apply to add it to your spreadsheet. Check a few rows to verify the output makes sense, and spot-check your "Exceeds" employees to confirm they're getting 5%.

5. Ask for the total budget calculation

Follow up in the Copilot chat:

In cell J2, calculate the total merit budget as a percentage: sum of all merit dollar increases divided by sum of all current salaries. Format as a percentage.

Copilot will generate a SUM-based formula. Insert it and verify the result matches your budget target.

6. Flag at-range issues

Ask Copilot to add a helper column:

Add a column K that says "At Range Max" if the employee is within 5% of their grade maximum, "Below Range Min" if they're below their grade minimum, and "Within Range" otherwise.

What you should see: A new column flagging employees whose merit increase would push them above the range cap or who are below range minimum (a common compliance issue to catch before cycle close).

Real Example

Scenario: You're running your Q4 merit cycle for 450 employees across 12 pay grades. You have a 3.5% total merit budget, and leadership wants you to model three scenarios: standard distribution, "performance-differentiated" (top performers at 5.5%), and a flat approach.

What you type: "Create three columns showing merit percentage for each scenario: Column H = standard matrix (Exceeds=4.5%, Meets=3%, Developing=1.5%, Below=0%), Column I = performance-differentiated (Exceeds=5.5%, Meets=3%, Developing=1%), Column J = flat 3.5% for all eligible employees. Add a summary row showing total budget % for each scenario."

What you get: Three fully modeled scenarios ready to compare, each with budget totals. What used to take 2–3 hours of formula building takes 10 minutes.

Tips

• Always double-check Copilot's formulas against a few known rows before presenting results to leadership
• If your request is complex, break it into two or three separate Copilot messages rather than one long prompt
• Ask Copilot to "explain this formula" if you need to understand what it generated before you trust it in an audit

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