Use Google Sheets to Compare Fee Schedules and Spot Underpayments

What This Does

Builds a quick-reference fee schedule comparison table that checks what different payers are supposed to pay for your top procedures — and lets you instantly spot when a payment doesn't match. A 5-minute setup that can surface hundreds of dollars in missed reimbursements.

Before You Start

• Google account (free)
• Contracted rates for your top payers — even 15-20 CPT codes is enough to start
• ERA or payment data to compare against (can be pasted in manually or imported from billing software export)

Steps

1. Create a fee schedule reference sheet

Go to sheets.google.com → new blank sheet → name it "Fee Schedule Comparison."

Create two tabs:

• "Rates" — your contracted rates by payer
• "Payments" — actual ERA/payment data to verify

2. Set up the Rates tab

Column headers in Row 1:

• A: CPT Code
• B: Medicare Rate
• C: Aetna Rate
• D: BCBS Rate
• E: UHC Rate
• (add more payers as needed)

Enter your top 20-30 CPT codes in Column A and fill in contracted rates for each payer.

Tip: Start with the codes you bill most often. Even 15 rows of data is valuable.

3. Set up the Payments tab

Column headers:

• A: Date of Service
• B: CPT Code
• C: Payer
• D: Paid Amount
• E: Expected Amount (formula)
• F: Difference (formula)
• G: Underpaid? (formula)

4. Add the Expected Amount formula

In cell E2 of the Payments tab:

=IFERROR(INDEX(Rates!B:E, MATCH(B2,Rates!A:A,0), MATCH(C2,Rates!B1:E1,0)),"Rate not found")

This looks up the CPT code AND the payer name to find the correct contracted rate.

Simpler alternative: If the formula feels complex, ask Google Sheets Explore: "Write a formula to look up the value in column B (CPT code) against the Rates sheet and return the rate for the payer in column C."

5. Add the Difference and flag columns

In F2: =D2-E2 (Difference — negative means underpayment)

In G2: =IF(F2<-5,"CHECK","OK") (flags any payment more than $5 below expected)

6. Apply conditional formatting to the "CHECK" column

Select column G → Format → Conditional Formatting → Text contains "CHECK" → red fill.

Real Example

Scenario: You paste in 3 weeks of BCBS payments. The sheet immediately flags 8 rows in red — all 99215 visits paid at $165 instead of the contracted $189. Total shortfall: $192. You file a corrected remittance inquiry with BCBS and recover the underpayments.

Without this tool: You'd have to manually compare each EOB line against a printed fee schedule — most billers skip this and the underpayments go undetected.

Tips

• Update the Rates tab when contracts renew — underpayments often start right after a contract update when the payer's system is misconfigured
• Use Google Sheets Explore to ask "Which payer has the highest total underpayment this month?" — it analyzes your Payments tab automatically
• Share this sheet with your billing manager — it becomes a live underpayment dashboard
• For Medicare, the correct rate is the Medicare Physician Fee Schedule — download it free from CMS.gov annually

Tool interfaces change — if the Explore button has moved, look for the sparkle/star icon at the bottom right of your Google Sheet.