Use Excel's AI to Build an Endorsement Tracking Spreadsheet

What This Does

Excel's Copilot feature helps you build a working endorsement and pending-items tracker — with status flags, days-overdue calculations, and conditional formatting — without writing formulas from scratch. Once built, you update it daily to stay on top of what's outstanding.

Before You Start

• You have Excel open (desktop version, Microsoft 365)
• Your organization has Microsoft 365 Copilot enabled (requires a Copilot license — check with IT)
• You have a blank workbook open or a basic spreadsheet with some existing data

Steps

1. Set up your column headers

In row 1, type these column headers across columns A through H:

• A: Account Name
• B: Policy Number
• C: Endorsement Type (address change, add driver, increase limits, etc.)
• D: Date Submitted
• E: Carrier
• F: Expected Completion
• G: Status (Pending / In Progress / Complete / Follow-Up Needed)
• H: Notes

2. Open Copilot

Click the Copilot button in the Excel ribbon (Home tab, far right — looks like a sparkle icon). The Copilot panel opens on the right side.

3. Ask Copilot to add a Days Overdue column

In the Copilot chat box, type: "Add a formula in column I called 'Days Since Submitted' that calculates how many days have passed since the date in column D."

Copilot suggests the formula (something like =TODAY()-D2) and offers to insert it. Click Insert column to apply.

4. Ask for conditional formatting on overdue items

Type in Copilot: "Add conditional formatting to highlight any row where the value in column I is greater than 5 in red."

Copilot applies the formatting — now any endorsement sitting more than 5 days gets a red highlight automatically.

5. Ask Copilot to create a summary

Type: "Create a summary at the bottom of the sheet showing: total pending endorsements, count by status, and oldest submission date."

Copilot generates the formulas and labels. You now have a live dashboard showing your open workload.

6. Update daily

At the start of each day, update the Status column as you complete items or receive carrier confirmations. The Days Since Submitted column updates automatically.

Real Example

Scenario: It's a Monday morning in March and you have 12 open endorsements from last week — some waiting on carriers, some waiting on policyholders to return forms.

What you have after setup: A color-coded tracker where anything over 5 days since submission turns red. You can sort by "Expected Completion" to see what's due today and by "Follow-Up Needed" to see where you need to chase the carrier.

Time saved: Instead of mentally tracking 12 open items and hoping nothing falls through the cracks, you have a 2-minute morning scan that tells you exactly where to focus.

Tips

• Start with just 5–10 rows of real data before asking Copilot to add features — it works better with actual content than a blank sheet.
• Add a "Policyholder Notified" column (Y/N) so you can quickly see who still needs an update after their endorsement completes.
• If your agency uses a shared drive, save this as a shared Excel file so your manager or other CSRs can see the queue status.

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