To track the claim status of each hospital bill, keep one table with a row per bill and columns for the mode (cashless or reimbursement), the amount, the date submitted, the status, the amount approved and the amount actually credited. The pending balance is then bill amount minus amount credited, and one formula can total it.
What is the difference between cashless and reimbursement for your records?
In a cashless claim the hospital deals with the insurer, and you usually pay only the items not covered. In a reimbursement claim you pay first and send documents afterwards, and money arrives later in your account. For records, the difference is simple: a cashless bill often has two parts to note, the amount the insurer settles and the amount you pay, while a reimbursement bill has a gap between your payment and the credit. Your policy and insurer decide what is covered, so rely on their communications.
What columns should the table have?
| Column | What to write |
|---|---|
| A: Bill no and provider | Bill number plus hospital, lab or pharmacy |
| B: Bill amount | Total on the bill |
| C: Mode | Cashless or Reimbursement |
| D: Submitted on | Date you sent the documents |
| E: Status | Open, Query raised, Approved, Paid or Closed |
| F: Amount approved | As per the insurer's letter |
| G: Amount credited | What reached your bank |
| H: Credit date | Matched with your bank statement |
| I: Pending balance | Formula: bill minus credited |
Which formulas keep the sheet honest?
These work in Excel and Google Sheets with rows 2 to 200 as data.
- Pending balance for each bill, in cell I2: =IF(B2="","",B2-G2)
- Total still pending: =SUM($I$2:$I$200)
- Pending only on open bills: =SUMIFS($I$2:$I$200,$E$2:$E$200,"<>Closed")
- Days since submission for an open claim, in J2: =IF(OR(D2="",E2="Closed"),"",TODAY()-D2)
Sort by the days column, highest first, and the claims that need a phone call rise to the top.
Before using the formulas, make sure the amount columns hold plain numbers, not text with the rupee sign typed in. Format the cells as numbers, and type 48000, not Rs. 48,000, or the sums will quietly return zero. Dates should be real dates too, otherwise the days-since formula will show an error.
How do you handle a query from the insurer?
Change the status to Query raised, add the date in a notes column, and write what was asked for. When you reply, add the date again. These notes build the history if a claim takes months, and they save you from repeating the whole story on the phone each time.
How do you match the credit with your bank statement?
When a claim is paid, find the credit in your statement, copy the date into column H and the amount into G. If the credit is smaller than the approved figure, write the difference in a notes column and ask the insurer for the settlement breakdown. Do not assume a gap is an error; deductions can be legitimate, and the letter should explain them.
When a sheet stops being enough
A sheet is perfect for claims. If your own spending is also tangled up with hospital payments, SpendixAI can take the bank or card statement as PDF, CSV or Excel, let you review the rows before saving, and keep receipts with each transaction, which makes the bank-credit match quicker. It does not file claims or talk to insurers.
Habits that make claims smoother
- Photograph every bill and prescription on the day you get it.
- Keep an original file and a scanned copy in one named folder.
- Update the status column the day anything changes.
- Review the open rows every Sunday.
- Close a row only when the money and the explanation are both recorded.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

