Use Excel's Copilot to Build Merit Cycle Formulas

What This Does

Copilot in Excel writes the nested lookup formulas for your merit planning model (merit matrix logic, compa-ratio banding, proration calculations) based on plain English descriptions of what you need. No more Google-searching VLOOKUP syntax at 10pm during merit cycle crunch.

Before You Start

• You have Excel open with a Microsoft 365 subscription that includes Copilot
• Your merit matrix table is already in the spreadsheet (or paste it in now)
• You have a column for each employee: current salary, performance rating, compa-ratio

Steps

1. Find the Copilot feature

Click the Copilot button in the Home ribbon (right side, looks like a sparkle icon). If you don't see it, click Insert → Add-ins → check that Microsoft Copilot is enabled. A Copilot chat panel opens on the right side of your screen.

2. Describe the formula you need

In the Copilot chat box, type a plain-language description of the formula logic. Be specific about which cells your data is in and what you want the formula to return.

3. Review and insert the formula

Copilot will show you the formula with a brief explanation. Click Insert to place it in the selected cell. Review the logic before copying it down the column, and spot-check 2–3 rows manually.

Real Example

Scenario: You have a merit matrix where the increase % is determined by performance rating (1–5) and compa-ratio band (below 80%, 80–100%, above 100%). Your matrix is in cells A1:D4. Employee data starts in row 8 with performance rating in column D and compa-ratio in column E.

What you type in Copilot: "Create a formula in F8 that looks up the merit increase percentage from my merit matrix in A1:D4, based on the performance rating in D8 and the compa-ratio band (if E8 < 0.80 use column B, if E8 is between 0.80 and 1.00 use column C, if E8 > 1.00 use column D)."

What you get: A formula using nested IF statements and MATCH/INDEX or VLOOKUP that correctly applies your matrix logic, typically 2–3 lines that would take 20 minutes to write from scratch.

Tips

• If the formula doesn't work correctly on first insert, paste it back into Copilot and say "this formula is returning [error/wrong value], can you debug it?" It will explain what went wrong.
• Use Copilot to add a separate column that calculates the proration factor for partial-year employees: "In G8, calculate a proration factor: if the hire date in C8 is before January 1, use 1.0; if between January 1 and June 30, use 0.5; if after June 30, use 0."
• Ask Copilot to add conditional formatting: "Highlight cells in column F where the merit increase exceeds 8% in yellow." This is useful for flagging outliers before manager review.

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