Spreadsheets

Medical expense tracker in Excel by family member and claim status

Build a medical expense tracker in Excel that logs every bill by family member, tracks each insurance claim and shows your real out-of-pocket cost.

Editorial illustration for Medical expense tracker in Excel by family member and claim status

A medical expense tracker in Excel needs one row per bill and nine columns: date, family member, type of expense, provider, amount, covered by insurance (Yes or No), claim number, claim status and amount received. Two SUMIFS formulas then give you what each person has cost the family and what is still waiting on the insurer. Set it up once and each new bill takes about thirty seconds to add.

The columns to set up on the Bills tab

Keep everything on a single tab called Bills, starting in row 1 with headers. Resist one tab per person; it makes the family total hard to see.

  • A: Date of the bill
  • B: Member (Papa, Amma, Riya, Self) - use a dropdown so spellings never drift
  • C: Type - Consultation, Tests, Medicines, Procedure, Other
  • D: Provider, such as the clinic, lab or pharmacy
  • E: Amount in rupees, the full bill
  • F: Covered by insurance - Yes or No
  • G: Claim number, left blank until you file
  • H: Claim status - Not filed, Filed, Settled, Rejected
  • I: Amount received, left blank until money actually lands
  • J: Out of pocket, calculated

For the dropdowns, select B2:B500, open Data, then Data Validation, choose List and type the names separated by commas, and repeat for C, F and H. In J2 enter =E2-N(I2) and fill it down. The N() wrapper treats a blank cell as zero, so a bill with no claim money yet shows its full amount as out of pocket.

How do I see out-of-pocket cost for each person?

On a second tab called Summary, list the family members down column A. Then use these formulas, one row per person, reading from the Bills tab:

  • Total billed: =SUMIFS(Bills!$E$2:$E$500, Bills!$B$2:$B$500, A2)
  • Received from insurer: =SUMIFS(Bills!$I$2:$I$500, Bills!$B$2:$B$500, A2)
  • Out of pocket: =B2-C2
  • Claims still pending: =COUNTIFS(Bills!$B$2:$B$500, A2, Bills!$H$2:$H$500, "Filed")
  • Amount tied up in pending claims: =SUMIFS(Bills!$E$2:$E$500, Bills!$B$2:$B$500, A2, Bills!$H$2:$H$500, "Filed")

If you prefer a PivotTable, select the Bills data, choose Insert, then PivotTable, and drop Member in Rows and Amount and Amount received in Values. The formulas are easier to keep correct when you add a row at the bottom, because a pivot needs a manual refresh.

A worked example with made-up numbers

Say Amma has a consultation of ₹800, blood tests of ₹2,400 and medicines of ₹1,350 in one month, and the tests are filed as an insurance claim. The insurer pays ₹1,900 against the tests. Papa has a consultation of ₹600 with no claim.

MemberBilledReceivedOut of pocket
Amma₹4,550₹1,900₹2,650
Papa₹600₹0₹600
Family total₹5,150₹1,900₹3,250

Amma: 800 + 2,400 + 1,350 = 4,550, and 4,550 - 1,900 = 2,650. The family total of ₹5,150 less ₹1,900 gives ₹3,250. If the claim were still marked Filed with nothing received, Amma's out of pocket would show the full ₹4,550 until the money arrives, which is the honest picture of cash that has already left your account.

What should I do when a claim is rejected or part-paid?

Never overwrite the original amount. Change the status to Rejected or Settled and put in only what was actually received. The gap between the bill and the received amount then stays visible, and you can see which kinds of bills your insurer pays less on.

Which bills should I record, and what do I keep?

  1. Record every bill the day you pay it, even small pharmacy ones; they add up over a year.
  2. Keep the original bill, prescription and test report together, and photograph them on the day.
  3. Write the claim number in column G the moment you file, so you can find the claim later.
  4. Review the Filed rows once a month and follow up on anything that has been sitting for weeks.
  5. Use a separate row for each bill rather than combining a month's medicines into one line.

When a sheet stops being enough

A sheet works well for medical bills because there are few of them. If you also want your regular spending in one place, SpendixAI can import bank or card statements and lets you attach receipts to transactions, so a pharmacy bill photo sits next to the payment. The claim columns above are better kept in the sheet.

Monthly routine in five minutes

  • Add new bills and update any claim that changed status.
  • Look at the pending-claims amount on the Summary tab.
  • Save a copy of the file with the month in its name before you start the next one.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports