Build a Claim Tracking Spreadsheet with Excel Copilot

What This Does

Excel Copilot builds formulas, creates columns, and analyzes your claim data on command — so you can have a personal claim tracker that flags overdue follow-ups, tracks reserve aging, and prioritizes your workload without needing to know a single spreadsheet formula.

Before You Start

• You have Microsoft Excel open (Microsoft 365 version)
• Microsoft 365 Copilot is enabled on your account
• You have a basic list of your current claims (even just claim numbers in column A is enough to start)

Steps

1. Open Excel and Create Your Claim Tracker Template

Open Excel and create a new blank workbook. Add headers in row 1 for the key data you want to track. Suggested columns:

• A: Claim Number
• B: Insured Name
• C: Date of Loss
• D: Claim Type (auto/property/liability)
• E: Date Assigned to You
• F: Follow-Up Due Date
• G: Current Status
• H: Days Open
• I: Reserve Amount
• J: Priority Flag

2. Open Copilot

Click the Copilot button in the Excel toolbar (look for the sparkle/Copilot icon in the Home tab ribbon). A Copilot panel opens on the right side of your screen.

What you should see: A chat interface asking "What do you want to do with this spreadsheet?"

Troubleshooting: If you don't see the Copilot button, check that your Microsoft 365 subscription includes Copilot, or try the web version at excel.office.com.

3. Ask Copilot to Build Your Formulas

Type your request in plain English — no formula knowledge needed.

For days open: "Add a formula in column H that calculates how many days each claim has been open, based on the date in column E."

For overdue follow-ups: "Add a formula in column J that says URGENT in red if the follow-up date in column F has already passed, SOON in yellow if it's within 3 days, and OK in green otherwise."

For reserve analysis: "Create a summary row at the bottom that shows total reserves and average days open."

4. Review What Copilot Built

Copilot will either insert formulas directly into your spreadsheet or show you what it's about to do and ask for confirmation. Click Apply or Insert to add the formulas.

What you should see: Color-coded cells and working formulas. Your overdue files will be highlighted in red immediately.

5. Enter Your Claims Data

Now fill in your actual claims. As you enter data, the formulas automatically calculate days open and flag priority items. Sort by the Priority column to see your most urgent files at the top.

Real Example

Scenario: You have 85 open claims and can't remember which ones have follow-up dates coming up this week.

What you do: Paste your claim list into column A. Add follow-up dates in column F. Ask Copilot: "Highlight column F in red if the date is before today, yellow if it's within 5 days."

What you get: Your spreadsheet instantly shows 12 past-due follow-ups in red and 8 upcoming ones in yellow. You know exactly where to start your day.

Tips

• Start simple — 5 columns of real data is more useful than 15 columns of empty ones
• Ask Copilot to "create a pivot table showing how many claims I have by type" for a quick caseload overview
• Export your Guidewire or CMS claim list (if your system allows it) and paste into Excel — then Copilot can analyze your entire portfolio

Tool interfaces change — if a button has moved, look for similar Copilot/AI options in the Excel toolbar under the Home tab.