Use Excel to Flag Underpayments in Your ERA Data

What This Does

Builds an automated underpayment detector in Excel — compare what payers actually paid against your contracted rates and flag any line where the payer paid more than 10% below what you're owed. Catches revenue leakage that's invisible without systematic comparison.

Before You Start

• Microsoft Excel (any version — Copilot optional but helpful for formula generation)
• ERA/EOB data exported from your billing software or clearinghouse (as CSV or Excel)
• Your fee schedule data for the relevant payer(s) — even a partial list of your top 20-30 CPT codes works
• Basic comfort with Excel (can open a file and click cells)

Steps

1. Set up two sheets

Open Excel and create a new workbook with two tabs:

• Tab 1: "ERA Data" — paste your ERA/EOB export here
• Tab 2: "Fee Schedule" — enter your contracted rates

Fee Schedule tab format: Column A = CPT Code, Column B = Payer Name, Column C = Contracted Rate

2. Add a "Expected Amount" column to your ERA Data

In your ERA Data tab, add a new column header called "Expected Amount" next to your existing "Paid Amount" column.

3. Write the VLOOKUP formula (or use Copilot)

In the first data row of "Expected Amount," use VLOOKUP to pull the contracted rate from your Fee Schedule tab:

=VLOOKUP([@[CPT Code]],FeeSchedule!$A:$C,3,FALSE)

If you have Copilot, click the cell and ask: "Write a formula that looks up the CPT code in the ERA tab against the fee schedule tab and returns the contracted rate."

Troubleshooting: If VLOOKUP returns #N/A, the CPT code format may differ (e.g., "99213" vs. "99213 " with a space). Use =TRIM() around your CPT code column to fix spacing issues.

4. Add a "Variance" column

Add a column called "Variance %" with this formula:

=([@[Paid Amount]]-[@[Expected Amount]])/[@[Expected Amount]]

5. Flag underpayments with conditional formatting

Select your Variance % column → click Home → Conditional Formatting → Highlight Cell Rules → Less Than → enter -0.10 (negative 10%) → choose red fill.

What you should see: Any row where the payer paid more than 10% below your contracted rate turns red automatically.

6. Sort by variance to prioritize recovery

Click Data → Sort → sort by Variance % from smallest to largest. The worst underpayments bubble to the top.

Real Example

Scenario: You're reviewing last month's Medicare payments and suspect some E&M codes are being paid under the fee schedule.

What you do: After setting up the formula, you sort by Variance % and discover 15 claims for 99214 were paid at $98 instead of the contracted $115 — a 15% underpayment totaling $255 in missed revenue. You submit a reopening request with CMS.

Time saved: Without this tool, you'd have to manually compare each line. With it, you spot the entire pattern in 60 seconds.

Tips

• Update your fee schedule tab each January (Medicare) and when payer contracts renew
• Use a separate fee schedule tab per payer if contracted rates differ significantly
• Flag underpayments over $25 as worth the time to appeal; smaller amounts may cost more to pursue than they're worth
• If Copilot is available, ask it: "Summarize the total underpayment by payer and identify which payers have the most underpaid claims"

Tool interfaces change — if conditional formatting has moved, look for it in the Home ribbon under the formatting section.