A claim tracker needs one row per claim and a column for each date in its life: intimated, documents sent, query received, query answered, settled. Add the billed and settled amounts, a difference column, and one formula that names the current stage. Filter on that stage and you see only claims still open.
Which columns should the sheet have?
Put headings in row 1 and start data in row 2:
| Col | Heading | Notes |
|---|---|---|
| A | Claim ID | For example HC-2026-01 |
| B | Member | Who was treated |
| C | Hospital | Name only |
| D | Intimated on | Date you informed the insurer |
| E | Documents sent on | Date of posting or upload |
| F | Query received on | Blank if none |
| G | Query answered on | Blank until replied |
| H | Settled on | Blank until paid |
| I | Billed | Total of the hospital bills |
| J | Settled amount | As per the settlement letter |
| K | Difference | =IF(H2<>"", I2-J2, "") |
| L | Stage | Formula below |
What formula gives the stage?
In L2, enter: =IF(H2<>"","Settled",IF(AND(F2<>"",G2=""),"Query open",IF(E2<>"","Under review",IF(D2<>"","Intimated","Not started")))). It works from the latest event backwards. If there is a settled date, the claim is Settled. If a query came but has not been answered, it is Query open. If documents were sent, Under review. If only intimated, Intimated. Drag it down the column. It works the same way in Excel and Google Sheets.
How do I see only claims that are still open?
Turn on a filter for the heading row. In Excel choose Data, then Filter. In Google Sheets choose Data, then Create a filter. In column L untick Settled. Your screen now lists the claims that need attention, and nothing else.
To count them in one cell, use =COUNTA($A$2:$A$200)-COUNTIF($L$2:$L$200,"Settled"). That counts every claim ID and subtracts the settled ones.
What does a filled row look like?
| Claim ID | Intimated | Docs sent | Query | Answered | Settled | Billed | Paid |
|---|---|---|---|---|---|---|---|
| HC-2026-01 | 12 Oct | 20 Oct | 28 Oct | 30 Oct | 18 Nov | ₹1,80,000 | ₹1,59,000 |
| HC-2026-02 | 3 Nov | 9 Nov | 22 Nov | Not yet | Not yet | ₹48,000 | Not yet |
These are hypothetical rows. Keep real dates and amounts in cells formatted as dates and numbers, not as text like Not yet, or the formulas will misbehave; leave those cells empty instead. For the first row, the difference is 1,80,000 minus 1,59,000, which is ₹21,000: the amount you carry yourself.
Can one sheet cover the whole family?
Yes. The Member column lets you filter by person, and the Claim ID keeps each episode separate. If you want yearly totals per member, add =SUMIFS($I$2:$I$200, $B$2:$B$200, "Self") for billed and the same with column J for settled. Use the same spelling of each name every time, or the totals will split.
When a sheet stops being enough
A tracker keeps the paperwork's timeline. The money side, such as hospital payments and the credit when the claim is paid, belongs in your expense records. SpendixAI can hold those with receipts, and you can create a category for medical costs awaiting claim.
What about claims that look stuck?
Sort by the days-open column and look at the oldest Under review or Query open rows first. For each, check the last date in the row against your call log. If nothing has moved since that date, call or write, quote the claim ID and the dates in the sheet, and add a note column entry for what you were told. A tracker earns its keep when it makes you act on the quiet claims, not only the noisy ones.
- Add the row the day you intimate.
- Fill each date as the event happens, not weeks later.
- Review the open list every week.
- After settlement, check the difference column against the letter.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


