Billing is where revenue quietly leaks out of a practice. A claim gets submitted and never followed up on. A denial lands in an inbox and never gets reworked. A patient balance sits for ninety days until it is functionally uncollectible. None of this shows up as a single dramatic loss. It is a slow drip, and most small practices never see the full picture because the data lives in five places at once.
A clearinghouse portal tells you what was submitted. The payer's remittance tells you what was paid. A sticky note tells you which claim someone meant to appeal. The result is that nobody can answer the one question that matters: how much money is owed to us right now, and how old is it?
A well-built medical billing spreadsheet answers that question on demand. This is not a replacement for your clearinghouse or your EHR. It is the tracking layer on top of them, the place where every claim's life cycle is visible in one row, and where accounts receivable aging is one formula away instead of one all-nighter away.
What to track
The whole system rests on getting the columns right. Each one earns its place by answering a question you will eventually need to ask. Here is the core layout for a medical billing tracking spreadsheet:
- Claim ID: your unique reference for the claim. Without it you cannot match payments back to the right submission or talk to a payer about a specific item.
- Patient: who the claim is for. Keep this minimal if the sheet is shared widely, and remember any PHI belongs inside a BAA-covered Google Workspace account.
- Payer: the insurance company or self-pay flag. You will slice your whole denial and aging picture by payer, so it has to be consistent.
- Date of service: when care was delivered. This is the clock that drives timely-filing deadlines and, combined with the submit date, your aging buckets.
- Billed: the amount you charged.
- Allowed: the contracted amount the payer agreed to. The gap between billed and allowed is the contractual adjustment, not money you lost.
- Paid: what actually hit the bank, from the payer plus any patient responsibility collected.
- Balance: allowed minus paid. This is the real money still owed, and it is what your A/R aging is built on.
- Status: where the claim is in its life cycle. This is the heartbeat of the sheet (more below).
- Denial reason: when a claim is denied, the reason or CARC code. A denial with no reason logged is a denial nobody will ever rework.
Two date columns are worth adding from day one: a submit date and a last action date. Aging should usually be measured from when you submitted, not from the date of service, and the last action date tells you which claims have gone cold.
Build the workbook
Once the columns are in place, three things turn a flat list into a working system: a controlled status pipeline, an aging calculation, and a denial view you can actually work from.
Claim status pipeline
Every claim moves through a predictable set of states. Lock those states down so the column stays clean. A practical set:
- Submitted: sent to the payer, awaiting adjudication.
- Paid: paid in full, balance at or near zero.
- Partially Paid: some payment received, a balance remains.
- Denied: rejected, needs a decision on whether to rework.
- Appealed: reworked and resubmitted, awaiting a second decision.
Add data validation on the Status column (Data, then Data validation, then "Dropdown") with exactly these five values. That keeps someone from typing "denyed" or "partial pmt" and breaking every formula and filter downstream.
Then add conditional formatting so the pipeline reads at a glance: green for Paid, amber for Partially Paid, red for Denied, blue for Appealed. Set each rule to "Text is exactly" the status value and color the whole row. A biller scanning the sheet should be able to find every red row in two seconds.
A/R aging buckets
Accounts receivable aging is the single most useful number this sheet produces. You want every open balance sorted into how long it has been waiting: 0 to 30 days, 31 to 60, 61 to 90, and 90 plus. The older the bucket, the lower your odds of ever collecting, so this is where you point your effort.
Assume your data lives on a sheet called Claims with these columns: A Claim ID, B Patient, C Payer, D Date of Service, E Submit Date, F Billed, G Allowed, H Paid, I Balance, J Status. The aging label for each row, measured from the submit date, is a single bucket column:
=ARRAYFORMULA(
IF(ROW(Claims!E2:E)>COUNTA(Claims!E2:E)+1, "",
IF(Claims!I2:I<=0, "Paid",
IFS(
DATEDIF(Claims!E2:E, TODAY(), "D")<=30, "0-30",
DATEDIF(Claims!E2:E, TODAY(), "D")<=60, "31-60",
DATEDIF(Claims!E2:E, TODAY(), "D")<=90, "61-90",
TRUE, "90+"
)
)
)
)
Drop that formula once into K2 and it fills the whole column. Any row with a zero or negative balance reads "Paid" and drops out of aging, and everything else lands in a bucket based on how many days have passed since you submitted it.
To turn that into a dashboard summary, sum the open balances per bucket with a single QUERY:
=QUERY(
{Claims!I2:I, Claims!K2:K},
"SELECT Col2, SUM(Col1)
WHERE Col1 > 0
GROUP BY Col2
ORDER BY Col2
LABEL Col2 'Aging Bucket', SUM(Col1) 'Open Balance'",
0
)
That gives you four lines, dollars owed in each bucket, refreshed every time the sheet opens. When the 90 plus number climbs, you know exactly where to send your collections effort before those claims age out entirely.
Denial tracking
Denials are recoverable revenue, but only if someone works them, and only if they are visible. A large share of denied claims are never resubmitted, not because they were truly uncollectible, but because they fell out of view. The fix is a dedicated working view.
On a tab called Open Denials, pull every denied claim that has not yet been appealed, oldest first, so the most time-sensitive reworks rise to the top:
=QUERY(
Claims!A2:L,
"SELECT A, B, C, I, K, L
WHERE J = 'Denied'
ORDER BY E ASC
LABEL A 'Claim ID', B 'Patient', C 'Payer',
I 'Balance', K 'Aging', L 'Denial Reason'",
0
)
This assumes the denial reason lives in column L. Group your denial reasons into a short, consistent list rather than free text. When you can see that one payer drives most of your denials, or that a single reason code keeps recurring, you stop reworking claims one at a time and start fixing the upstream cause.
Automate the chase
The hardest part of A/R is not the math, it is the follow-up. Claims that sit untouched past a payer's expected turnaround need a human to pick up the phone, but nobody can remember which ones crossed the line today. Apps Script can watch the sheet for you and email a daily list of claims that have gone past a threshold.
This script scans the Claims tab, finds anything still in Submitted or Partially Paid status with a balance owed and a submit date older than thirty days, and emails the biller a digest. Open the spreadsheet, go to Extensions, then Apps Script, paste it in, and set a daily time-based trigger.
const BILLER_EMAIL = "billing@yourclinic.com";
const DAYS_THRESHOLD = 30;
function flagAgingClaims() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Claims");
const rows = sheet.getDataRange().getValues();
const today = new Date();
const overdue = [];
rows.forEach((row, i) => {
if (i === 0) return; // skip header
const submitDate = row[4]; // col E
const balance = Number(row[8]); // col I
const status = row[9]; // col J
if (!submitDate || balance <= 0) return;
if (status !== "Submitted" && status !== "Partially Paid") return;
const days = Math.floor(
(today - new Date(submitDate)) / 86400000
);
if (days > DAYS_THRESHOLD) {
overdue.push({
claim: row[0], patient: row[1], payer: row[2],
balance: balance, days: days
});
}
});
if (overdue.length === 0) return;
overdue.sort((a, b) => b.days - a.days);
const lines = overdue.map(c =>
`${c.claim} | ${c.patient} | ${c.payer} | ` +
`$${c.balance.toFixed(2)} | ${c.days} days`
);
const body =
`${overdue.length} claims unpaid past ` +
`${DAYS_THRESHOLD} days:\n\n` +
`Claim | Patient | Payer | Balance | Age\n` +
`${lines.join("\n")}`;
GmailApp.sendEmail(
BILLER_EMAIL,
`A/R follow-up: ${overdue.length} aging claims`,
body
);
}
Adjust DAYS_THRESHOLD to match your typical payer turnaround. Some practices run two versions, one at thirty days for a soft check and one at sixty for escalation. The point is that the chase becomes a routine that runs on its own, not a task someone has to remember.
The KPIs that matter
Once the data is clean, a handful of metrics tell you whether your billing operation is healthy. Treat the benchmark numbers below as rough industry ranges, not hard targets, since they vary widely by specialty and payer mix.
- Net collection rate: payments received divided by the amount you were allowed to collect, after contractual adjustments. This measures how much of the money you were actually owed you managed to bring in. Many practices aim somewhere in the mid-to-high 90s percent range, and a falling rate usually points to unworked denials or write-offs.
- Days in A/R: roughly your average receivables balance divided by average daily charges. It answers how long, on average, money sits before you collect it. Lower is better, and figures in the range of a few weeks to around forty days are commonly cited as reasonable.
- Denial rate: the share of claims denied on first submission. Even a modest denial rate is expensive once you count the cost of rework, so trending this by payer and reason is where the real savings hide.
- Clean-claim rate: the share of claims that sail through and get paid without any edits, rejections, or rework. A high clean-claim rate is the upstream fix that makes every other number better.
You can compute all four from the same workbook with SUMIF, COUNTIF, and the status column you already built. The spreadsheet stops being a list and becomes a scorecard.
If you would rather start from something already wired up, grab our free clinic billing and revenue dashboard template. It comes with the claim columns, the status pipeline, A/R aging, and a revenue summary built in, so you can drop your own data in and see the picture the same day.
You do not collect more by working harder on each claim. You collect more by making every aging claim impossible to ignore.