Use Copilot in Excel to Analyze Renewal Variance and M&A Retention Risk

What This Does

Two recurring Excel tasks eat time every cycle: comparing this year's renewal numbers against budget, and pulling a first read on retention risk from a target company's headcount data during a deal. Copilot in Excel handles both the same way. It reads a table you already have and returns the variance, trend or outlier summary in the side pane instead of you building a fresh set of formulas each time.

Before You Start

• Your renewal or headcount data sits in a proper Excel Table (Insert > Table), not just a range with headers
• Your account has the Microsoft 365 Copilot add-on. Look for the Copilot icon in the lower right corner of the Excel window; if it's missing, ask your Microsoft 365 admin whether your license includes it (see Microsoft's pricing page for what the add-on covers)
• Note that as of September 14, 2026, Microsoft retired the in-cell =COPILOT() formula. Excel Copilot now works only through the side pane described below

Steps

1. Find the AI feature

Click the Copilot icon in the lower right corner of the Excel window to open the side pane. Select the table you want to work with, or reference it by name in your prompt.

2. Tell it what you need

Ask a specific question rather than a vague one. For variance work, try something like "Compare the Renewal Cost column to the Budget column and show the dollar and percent variance for each plan." For retention risk, try "Flag rows where Tenure is under two years and Level is Director or above." Name the exact columns. Copilot in Excel is more reliable when it doesn't have to guess which field you mean.

3. Review and use the result

Copilot returns a summary, a suggested formula, or a new PivotTable, depending on what you asked for. Check the formula against a few rows by hand, especially any with blanks, zeros or text where you expect a number. Ask a follow-up question in the same pane to refine the result instead of starting over.

Real Example

Scenario 1, renewal variance: Medical, dental and vision renewals came in from the broker, and you need a one-page variance table against the budget you set in the spring for the finance narrative.

What you type/do: With the renewal table selected, you open the Copilot pane and type "Show variance in dollars and percent between the Budget and Renewal Quote columns for each plan, and flag anything over 10 percent."

What you get: A new column showing the dollar and percent variance per plan, with the two lines over 10 percent highlighted, plus a one-line summary you can drop into the forecast narrative.

Scenario 2, retention risk: An acquisition target has sent over a headcount export as part of due diligence, and legal wants a first pass on which roles carry the highest retention risk before the deal closes.

What you type/do: With the headcount table selected, you type "Flag rows where Tenure is under two years and Level is Director or above, and summarize what percent of the Director-plus population that represents."

What you get: A filtered view of the at-risk rows and a summary line with the percentage. That summary becomes the starting point for the retention-risk section of the integration checklist.

Tips

• Keep headcount and pay data in whichever workbook it already lives in rather than copying it into a new file just to run Copilot. Fewer copies floating around means fewer places a sensitive export can go stale or get forwarded by mistake.
• M&A headcount data usually includes names, individual pay and sometimes protected leave status. Before you ask Copilot to analyze it, strip names and replace them with a role or employee ID column, and confirm with legal or the deal lead whether the target's data is cleared to sit in a tool outside the deal room.
• Spot-check any percentage Copilot reports against a manual count on a small sample. It occasionally miscounts when a column mixes blanks and text.

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