Use Google Sheets AI to Build an Insurance Verification Tracker

What This Does

Google Sheets AI helps you build a verification status board with color-coded cells so you and your team can see at a glance which patients for tomorrow are verified, still pending, or flagged — no sticky notes or mental tracking required.

Before You Start

• You have a Google account (free)
• You have Google Sheets open (sheets.google.com)
• You have tomorrow's schedule pulled up in Dentrix or Eaglesoft

Steps

1. Open a new Google Sheet

1. Go to sheets.google.com
2. Click the + (Blank spreadsheet)
3. Name it "Insurance Verification — [Today's Date]"

2. Set up your column headers

Type these headers in Row 1: Patient Name | Appt Time | Carrier | Policy ID | Verified? | Notes

3. Fill in tomorrow's patients from the schedule

Pull up tomorrow's appointments and type each patient's name, time, carrier, and policy ID in each row. Leave "Verified?" and "Notes" blank for now.

4. Use AI to add a dropdown for the Verified? column

1. Click on cell E2 (Verified? column, first patient row)
2. In the top menu, click Extensions → Apps Script (or look for a Explore / AI button in the lower-right corner)
3. Alternatively: select the entire "Verified?" column → Data → Data validation → Dropdown → add options: Verified, Pending, Issue, Called-Back
4. This creates a dropdown for every cell in that column

5. Add conditional formatting for color coding

1. Select the "Verified?" column (click the column letter E)
2. Click Format → Conditional formatting
3. Add rule: "Text is exactly" → "Verified" → fill color Green
4. Add rule: "Text is exactly" → "Pending" → fill color Yellow
5. Add rule: "Text is exactly" → "Issue" → fill color Red
6. Click Done

What you should see: Cells turn green, yellow, or red automatically as you update the dropdown.

Troubleshooting: If conditional formatting isn't working, make sure the text in your dropdown exactly matches the text in your rules (case-sensitive).

6. Ask Google Sheets AI to help with a formula

1. In a cell below your list, type: "=COUNTIF(E2:E20,"Verified")" to count verified patients
2. If you're unsure of a formula, click any empty cell, then click the Explore button (bottom right, looks like a star) → type "how many patients have Issue status" and Sheets AI will suggest the formula

Real Example

Scenario: You have 14 patients tomorrow and need to track who's been verified before 4 PM today.

What you type: Each patient from the schedule with their carrier and policy ID in 10 minutes

What you get: A live color board — 8 green (verified), 4 yellow (pending), 2 red (called but issue with coverage). You know exactly who still needs attention before end of day.

Tips

• Share the sheet with your co-worker at the front desk so you're not duplicating calls
• Save a copy as a template (File → Make a copy) so you reuse the format every day without rebuilding it
• Add a "Called at" column to log the time you called the carrier — helpful when you need to prove you attempted verification

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