Use Excel's AI to Build a Contract Renewal Tracker
What This Does
Excel's built-in AI features let you describe what you want in plain English, and Excel builds the formulas, conditional formatting rules, and summary tables for you. For contracts administrators, this means you can create a professional renewal alert tracker without knowing a single formula.
Before You Start
- You have Microsoft Excel open (desktop or web)
- You have a Microsoft 365 subscription (Copilot requires M365; basic Ideas feature is in all Excel versions)
- Your contract list is in an Excel table or range with columns like: Contract Name, Vendor, Expiration Date, Status, Owner
- Time needed: 10–15 minutes
- Cost: Included in Microsoft 365 (Business Basic $6/user/mo or higher)
Steps
1. Open the Copilot pane (or use the formula bar)
In Excel for Microsoft 365: click the Copilot button in the Home ribbon (purple sparkle icon). If you don't see it, your organization may not have Copilot enabled. Skip to the manual formula approach below.
If Copilot isn't available: Use the formula bar directly. The AI approach still works: type your formula request into any cell and describe what you want in comments, then use ChatGPT to generate the formula for you.
What you should see: A chat panel opens on the right side of your screen.
2. Describe your renewal alert formula
In the Copilot chat panel, type:
"Add a column called 'Days Until Expiration' that calculates how many days are left until the Expiration Date. Then add conditional formatting: highlight the row red if expiration is within 30 days, yellow if within 60 days."
What you should see: Copilot generates the formula and previews the conditional formatting rules before applying them.
3. Add a status summary table
Ask Copilot:
"Create a summary table below my data showing: total contracts, contracts expiring in the next 30 days, 60 days, and 90 days, and count by status."
What you should see: A pivot-style summary table is inserted, pulling live data from your contract list.
4. Set up data validation for the Status column
Ask Copilot (or use Data → Validation manually):
"Add a dropdown list to the Status column with these options: Active, Pending Renewal, Under Negotiation, Expired, Terminated."
This prevents the free-text chaos in status columns that breaks your summary formulas.
Real Example
Scenario: You manage 45 active contracts and need to give your director a weekly snapshot of what's expiring soon.
What you type: "Add a Days Until Expiration column. Highlight rows red where expiration is within 30 days of today. Create a summary at the top showing: total active contracts, count expiring in 30/60/90 days."
What you get: A live tracker that automatically updates every time you open the file. No more manually checking dates. Your weekly summary report now takes 5 minutes instead of 90 minutes.
Tips
- If Copilot isn't available, paste your column headers into ChatGPT and ask it to write the formulas, then paste them directly into Excel. Works just as well.
- Keep your data in a formal Excel Table (Insert → Table). Copilot works much better with structured tables than plain ranges.
- Add a "Days Until Expiration" column as a number so you can sort and filter by urgency, not just see colors.
Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.