Health and family

Health claim status tracker: intimated, queried, settled

Build a health claim status tracker in Excel or Google Sheets with stage dates, a billed versus settled difference and a filter for claims still open.

Editorial illustration for Health claim status tracker: intimated, queried, settled

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:

ColHeadingNotes
AClaim IDFor example HC-2026-01
BMemberWho was treated
CHospitalName only
DIntimated onDate you informed the insurer
EDocuments sent onDate of posting or upload
FQuery received onBlank if none
GQuery answered onBlank until replied
HSettled onBlank until paid
IBilledTotal of the hospital bills
JSettled amountAs per the settlement letter
KDifference=IF(H2<>"", I2-J2, "")
LStageFormula 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 IDIntimatedDocs sentQueryAnsweredSettledBilledPaid
HC-2026-0112 Oct20 Oct28 Oct30 Oct18 Nov₹1,80,000₹1,59,000
HC-2026-023 Nov9 Nov22 NovNot yetNot yet₹48,000Not 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.

  1. Add the row the day you intimate.
  2. Fill each date as the event happens, not weeks later.
  3. Review the open list every week.
  4. After settlement, check the difference column against the letter.
SPENDIXAI JOURNAL

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

Explore your reports