Use Google Sheets' AI to Catch Billing Discrepancies

What This Does

Google Sheets has a built-in Gemini AI assistant that helps you write formulas in plain English. Instead of manually hunting through carrier invoices and HRIS exports for mismatches, you describe what you're trying to find and Gemini suggests the exact formula — so your billing reconciliation becomes a near-automated monthly process.

Before You Start

• You have Google Sheets open (any Google account)
• You've exported carrier invoice data (employee ID, name, plan, premium) into one sheet tab
• You've exported HRIS enrollment data into a second sheet tab
• Time needed: 15 minutes to set up; under 5 minutes to run each month after that
• Cost: Free (Google account required)

Steps

1. Find the AI feature

Open your Google Sheet. Click the "Help me" button at the top right of the toolbar — it looks like a small sparkle/star icon next to the formula bar. Alternatively, go to Extensions → Gemini in Google Sheets (if your Google Workspace has it enabled). A sidebar will open on the right.

What you should see: A chat-style sidebar appears on the right side of your screen with a text box where you can type instructions in plain English.

Troubleshooting: If you don't see the sparkle icon or Gemini option, try Format → Help me organize or check if your Google account has Gemini features enabled under your Google account settings.

2. Describe what you need

In the Gemini sidebar, type your formula request in plain English. For billing reconciliation, use language like:

"Column A (Sheet1) has carrier invoice employee IDs. Column B (Sheet2) has HRIS enrolled employee IDs. Write a formula for column C (Sheet1) that shows YES if the Sheet1 employee ID is NOT found in Sheet2, and NO if it is found."

What you should see: Gemini suggests a formula, usually a variation of =IF(ISNA(MATCH(A2,Sheet2!B:B,0)),"YES - Missing from HRIS","OK")

3. Insert and review the formula

Click Insert to add the formula to the active cell. Drag the formula down column C to cover all invoice rows. Review the results — any row showing "YES - Missing from HRIS" is an employee the carrier is billing for who isn't in your enrollment system.

What you should see: Most rows should show "OK." Any "YES" rows need investigation — either the employee was terminated and the carrier wasn't notified, or there's a name/ID mismatch.

4. Build the reverse check

Repeat the process for the other direction: employees in your HRIS who are NOT on the carrier invoice. These employees may not be receiving coverage despite being enrolled.

"Column A (Sheet2) has HRIS employee IDs. Column B (Sheet1) has carrier invoice employee IDs. Write a formula for column C (Sheet2) that flags anyone in Sheet2 not found in Sheet1."

5. Save as your monthly reconciliation template

Once your formulas work, save this sheet as a template. Each month, clear the data tabs and paste in the new carrier invoice and HRIS export — the formulas automatically recalculate your reconciliation.

Real Example

Scenario: You've received your monthly Cigna dental invoice with 347 covered employees. Your HRIS shows 351 employees enrolled in dental.

What you type: "Sheet1 is the Cigna invoice (employee IDs in column A). Sheet2 is the HRIS enrollment export (employee IDs in column A). Flag any IDs in Sheet2 not found in Sheet1."

What you get: A formula that flags 4 employees enrolled in your HRIS who don't appear on the Cigna invoice — possibly employees who enrolled mid-month and whose enrollment hasn't been processed by the carrier yet. You now have the exact records to investigate instead of manually cross-referencing 347 rows.

Tips

• Name your sheet tabs clearly ("Cigna Invoice March" and "HRIS Enrollment March") so the formulas are easy to audit later
• Add a "Notes" column next to your flag column to document the resolution for each discrepancy — this creates your audit trail
• Ask Gemini to "format column C to highlight YES cells in red" to make discrepancies visually obvious without scrolling

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