Use Excel's AI to Build a Renewal Tracking Report

What This Does

Excel's Copilot lets you ask for formulas, filters, and formatting in plain English — no formula memorization required. For an insurance CSR managing renewal dates across dozens or hundreds of policies, this means building a "renewals due this month" view in seconds instead of an hour.

Before You Start

• You have Excel open (desktop version or Excel on the web)
• Your spreadsheet has policy data including at least a column with renewal dates
• Your organization has Microsoft 365 Copilot enabled (requires M365 Copilot license)

Steps

1. Open your renewal tracking spreadsheet

Open the spreadsheet where you track policies. It needs at least one column with renewal/expiration dates (e.g., Column B = "Renewal Date").

2. Open Copilot in Excel

Look for the Copilot button in the Home tab ribbon at the top. It has a sparkle icon. Click it — a chat panel opens on the right side of the screen.

3. Ask for a days-until-renewal column

Type in the Copilot chat panel: "Add a column called 'Days Until Renewal' that calculates how many days are between today and the date in column B."

Copilot will write the formula and offer to insert it. Click Insert column to apply it.

4. Ask for conditional highlighting

Type: "Highlight rows in red where 'Days Until Renewal' is between 0 and 30. Highlight rows in yellow where it's between 31 and 60."

Copilot will write the conditional formatting rules and apply them. You now have a color-coded renewal dashboard.

5. Ask for a filtered view

Type: "Filter the table to show only rows where 'Days Until Renewal' is 60 or fewer."

Copilot applies the filter. You now see only upcoming renewals.

6. Save the view

Click View → Sheet View → New to save this filtered, highlighted layout as a named view you can return to each week.

Real Example

Scenario: It's Monday morning and you need to know which policies are renewing in the next 30 days to prioritize outreach.

What you ask Copilot: "Show me all clients whose renewal date is within the next 30 days, sorted by soonest first."

What you get: A sorted, filtered view of this month's renewals — ready to work from. What would have taken 20 minutes of manual sorting takes 30 seconds.

Tips

• Keep your renewal spreadsheet column headers consistent — Copilot reads the header names to understand your data, so "Renewal Date" works better than "Col C".
• You can also ask Copilot to analyze the data: "How many policies are renewing this month by coverage type?" It will answer in plain English.
• If you export a renewal report from your AMS, you can paste it into Excel and then use Copilot to clean and organize the data.

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