Walk into most small practices and ask where the patients are tracked, and you will get four answers at once. Some are on a clipboard at the front desk. Some are in the booking software. Some are in a manager's inbox, waiting for a callback that never got scheduled. And a few exist only in someone's memory, which is the most dangerous place of all.
That patchwork is where patients go missing. A referral comes in and nobody logs it. A post-op follow-up is "definitely going to happen" and then a week passes. A no-show never gets rebooked. None of this is a clinical failure. It is an operational one, and operational problems are exactly what a good patient tracking spreadsheet solves.
This guide walks through building a real one in Google Sheets: the columns that matter, a status pipeline you can actually run, a follow-up view that surfaces who needs a call today, and an Apps Script that nudges you when something goes overdue. By the end you will have a working patient management spreadsheet instead of four half-systems.
What a patient tracking spreadsheet actually needs
The temptation is to add forty columns. Resist it. A tracker that is painful to fill in does not get filled in. Start with the core fields below, and add specialty columns only when a real workflow demands them.
- Patient ID: a short unique code (like
P-0042) so you never rely on names alone. Two patients named Maria Garcia will eventually collide, and an ID lets your other sheets and formulas reference a patient unambiguously. - Name: full legal name, kept in one column or split into first and last if you sort by surname often.
- Contact: phone and email. This is the column your follow-up automation reaches into, so keep it clean.
- Status: where the patient sits in your pipeline right now (New, Scheduled, Seen, Follow-up, Discharged). This is the heart of the whole sheet.
- Last visit: the date you last saw them. Drives "who has gone quiet" reporting.
- Next action: the single most important column. One plain sentence: "Call to rebook," "Send lab results," "Schedule 6-week check." If this is blank, the patient is in limbo.
- Next action date: when that action is due. This is what turns the tracker from a list into a worklist.
- Owner: the staff member responsible. Shared accountability is no accountability, so every row gets a name.
- Notes: free text for context. Keep clinical detail out of here (more on that below) and use it for operational notes like "prefers afternoon appointments."
Nine columns. That is a medical tracker in Google Sheets a front desk will actually keep current, which beats a beautiful one nobody updates.
Build it step by step
1. Set up the columns and data validation
Open a new sheet, name the tab Patients, and put your headers in row 1, left to right: Patient ID, Name, Phone, Email, Status, Last Visit, Next Action, Next Action Date, Owner, Notes. Freeze row 1 (View, then Freeze, then 1 row) so headers stay visible as the list grows.
Now lock down the Status column so nobody invents "follow up?" or "FU" variants that break your formulas. Select column E (Status), open Data, then Data validation, and add a rule using a dropdown with these exact values: New, Scheduled, Seen, Follow-up, Discharged. Do the same for the Owner column pointed at your staff list. Consistent inputs are what make everything downstream possible.
To auto-generate Patient IDs instead of typing them, put this in a helper column (it builds a zero-padded ID from the row number):
="P-" & TEXT(ROW()-1, "0000")
2. Build the status pipeline with conditional formatting
Five statuses give you a clean pipeline: a patient moves from New (just entered the system) to Scheduled (has an appointment) to Seen (visit done) to Follow-up (needs another touch) and finally to Discharged (cycle complete). Color makes the state readable at a glance, which matters when the sheet has 300 rows.
Select the Status column, open Format, then Conditional formatting, and add one rule per status using "Text is exactly":
New: light blue background. Brand-new, untriaged.Scheduled: light yellow. Booked, waiting.Seen: light green. On track.Follow-up: orange. Needs attention soon.Discharged: gray. Closed out.
For an overdue flag, add one more conditional rule on the whole row using a custom formula. This highlights any patient whose next action date has already passed and who is not discharged:
=AND($H2<TODAY(), $H2<>"", $E2<>"Discharged")
Apply that to the range A2:J1000 with a red fill. Now overdue follow-ups light up automatically the moment they slip.
3. A QUERY view of patients needing follow-up
Scrolling the master list to find who needs a call is the slow way. Instead, build a live view on a second tab (name it Follow-up Worklist) that pulls only the patients who need action, sorted by urgency. Put this single formula in cell A1 of that tab:
=QUERY(Patients!A2:J,
"SELECT A, B, C, E, G, H, I
WHERE E = 'Follow-up'
OR (H <= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'
AND G IS NOT NULL
AND E <> 'Discharged')
ORDER BY H ASC
LABEL A 'ID', B 'Name', C 'Phone', E 'Status',
G 'Next Action', H 'Due', I 'Owner'", 0)
This returns every patient who is either explicitly in the Follow-up status, or whose next-action date is due or overdue and is not discharged, sorted so the most overdue sits at the top. The view updates itself as the master list changes. Your front desk now has a daily call list instead of a hunting expedition.
Automate the follow-ups
A worklist is good. A worklist that emails you when something is slipping is better. The Apps Script below scans the Patients tab, finds anyone whose next-action date is overdue (or who is flagged Follow-up with no date at all), and emails a single digest to the practice manager. Run it once and the rest is automatic.
To install it: open Extensions, then Apps Script, paste the function, replace the manager email, and click Run once to authorize. Then add a daily trigger under the clock icon (Triggers, then Add Trigger, time-driven, day timer) so it fires every morning.
function sendFollowUpDigest() {
const MANAGER_EMAIL = "manager@yourclinic.com";
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Patients");
const rows = sheet.getDataRange().getValues();
const today = new Date();
today.setHours(0, 0, 0, 0);
const overdue = [];
rows.forEach((row, i) => {
if (i === 0) return; // skip header
const status = row[4]; // E: Status
const nextAction = row[6]; // G: Next Action
const dueDate = row[7]; // H: Next Action Date
if (status === "Discharged") return;
const noPlan = status === "Follow-up" && !nextAction;
const isOverdue = dueDate instanceof Date
&& dueDate < today && nextAction;
if (noPlan || isOverdue) {
overdue.push(
`${row[0]} - ${row[1]}: ${nextAction || "NO NEXT ACTION SET"}`
+ (dueDate instanceof Date
? ` (due ${Utilities.formatDate(dueDate,
Session.getScriptTimeZone(), "MMM d")})`
: "")
+ ` [owner: ${row[8] || "unassigned"}]`
);
}
});
if (overdue.length === 0) return; // nothing to nag about
GmailApp.sendEmail(
MANAGER_EMAIL,
`${overdue.length} patient follow-ups need attention`,
"These patients are overdue or have no next action set:\n\n"
+ overdue.join("\n")
+ "\n\nOpen the tracker to action them."
);
}
If you would rather nudge the patients directly, swap MANAGER_EMAIL for row[3] (the patient's email) inside the loop and rewrite the message as an appointment-reminder note. Just be deliberate about it: only email patients who have agreed to be contacted that way, and keep clinical detail out of the message body.
Keep it private
Patient data carries obligations, and a spreadsheet does not change that. The good news is that Google Workspace is HIPAA-eligible: Google offers a Business Associate Agreement (BAA) covering Sheets, Drive, Gmail, and Calendar on its Business, Enterprise, and Education editions. A free consumer Gmail account is not covered, so this only holds inside a paid Workspace tenant with the BAA signed.
The BAA covers Google's side. Your side still matters just as much:
- Restrict sharing. Share the file with named staff only, never "anyone with the link." Disable external sharing by default in the admin console.
- Enforce 2-Step Verification for every user touching the sheet.
- Minimize what you store. Keep the tracker operational. Diagnoses, treatment notes, and other sensitive clinical detail belong in your clinical system, not a notes column.
- Use the audit log. Workspace records who opened, edited, and shared the file, which is part of demonstrating reasonable safeguards.
Done this way, a patient tracking spreadsheet is a legitimate place for operational data. Done carelessly, any tool is a liability. The discipline is the point.
When you have outgrown a spreadsheet
Be honest about the line. A spreadsheet is excellent for tracking where patients are in your process: who needs a call, who is overdue, who has gone quiet. It is the wrong tool for the clinical record itself.
Graduate to a proper EHR when you need structured clinical documentation, e-prescribing, lab interfaces, or insurance claim submission. Those are regulated workflows that require certified systems, and no amount of clever formulas substitutes for them. The healthy pattern most small practices land on: a clinical system for records, and Google Sheets for the operational layer that the clinical system handles poorly. That is not a downgrade. It is the right tool in each lane.
Want a head start? We packaged everything above into a ready-to-run sheet. Grab our free patient intake and tracking template, make a copy into your own Workspace, and you will have the columns, dropdowns, conditional formatting, follow-up view, and the Apps Script wired up on day one. Replace the sample rows with your patients and you are tracking in minutes.