Automate Benefits Reconciliation with Google Sheets AI

What This Does

Google Sheets AI (powered by Gemini) helps you write the formulas and build the structure to automatically compare your monthly insurance invoice against your HRIS data — catching discrepancies without hours of manual row-by-row comparison.

Before You Start

• You have Google Workspace (Gmail, Google Sheets) — you can access Sheets at sheets.google.com
• You have access to export employee data from your HRIS as a spreadsheet
• You have a copy of your monthly insurance invoice (PDF or CSV from the carrier)

Steps

1. Set up your two data sheets

In Google Sheets, create a new spreadsheet. Add two tabs: one called HRIS Export and one called Carrier Invoice. Paste your HRIS employee roster into the first tab (columns: Employee Name, Employee ID, Plan Type, Employee Cost, Dependent Count). Paste your carrier invoice data into the second tab (same columns if possible).

What you should see: Two tabs with your data, each with consistent column headers.

2. Open Gemini AI in Sheets

Click the Gemini icon (a sparkle/star icon) in the top-right of Google Sheets, or click Extensions → Gemini for Google Workspace if you see it in the menu.

What you should see: A sidebar opens on the right with a chat interface. Troubleshooting: If you don't see Gemini, your Google Workspace plan may need it enabled. Ask your IT admin, or you can generate formulas manually with the help of a free chatbot (paste the prompt into ChatGPT instead).

3. Ask Gemini to build your comparison formula

In the Gemini sidebar, type: "I have two sheets: 'HRIS Export' with employee names in column A and their monthly plan costs in column D, and 'Carrier Invoice' with the same data. Write a formula I can put in a new 'Comparison' sheet that shows any employees where the costs don't match, and flags employees who appear in one list but not the other."

What you should see: Gemini writes the VLOOKUP or MATCH formula with an explanation.

4. Apply the formula and review discrepancies

Paste the formula into a new Comparison tab. Add conditional formatting (Format → Conditional formatting) to highlight rows where the discrepancy is greater than $0.

What you should see: Rows highlighted wherever the carrier invoice doesn't match your HRIS data.

5. Investigate and correct each discrepancy

For each flagged row: determine if it's a carrier billing error, a missed HRIS update, or a data entry mistake. Document the correction and submit to the carrier or payroll team as needed.

Real Example

Scenario: Your August insurance invoice shows 87 covered employees, but your HRIS shows 85 active benefits-eligible employees.

What you do: Run the comparison formula — it flags 2 employees on the carrier list who were terminated last month but whose coverage wasn't cancelled in time. You submit a correction request to the carrier for a retroactive credit.

Time saved: What previously took 2–3 hours of row-by-row checking now takes 20–30 minutes.

Tips

• Build this reconciliation template once and reuse it every month — just paste in fresh data
• If Gemini isn't available, use ChatGPT to generate the same VLOOKUP/MATCH formulas by pasting your column structure into the prompt
• Document any recurring discrepancies — if the same issue appears monthly, there's likely a process gap in how terminations are being communicated to HR

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