Excel for medical and dental office administration

For front desk staff, office managers and billing coordinators in medical and dental practices. Your practice software holds the records, but the reports you actually need on a Monday morning (how many patients no-showed, which claims are getting old, which supplies run out before the next delivery) often end up in Excel. This page shows the formulas for those jobs on a real-looking appointment log, with practice exercises and two free templates.

Front desk and reception staff
Practice and office managers
Billing and A/R coordinators
Dental office administrators
Schedulers

Office administration only. Not clinical or medical advice; follow your practice’s privacy rules for patient data.

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	Patient ID	Date	Provider	Visit type	Status	Wait (min)	Open balance	Days outstanding
2	P-1042	Oct 5, 2026	Dr. Kim	New patient	Kept	18	240	12
3	P-1107	Oct 5, 2026	Dr. Kim	New patient	No-show		0
4	P-0988	Oct 5, 2026	Dr. Ortiz	Follow-up	Kept	7	0
5	P-1213	Oct 6, 2026	Dr. Ortiz	New patient	Kept	26	410	45
6	P-0871	Oct 6, 2026	Dr. Kim	Follow-up	Cancelled		0
7	P-1150	Oct 6, 2026	Hygiene	Cleaning	Kept	4	95	33
8	P-1042	Oct 7, 2026	Dr. Kim	Follow-up	Kept	12	0
9	P-1301	Oct 7, 2026	Hygiene	Cleaning	No-show		0
10	P-1222	Oct 7, 2026	Dr. Ortiz	Follow-up	Kept	31	180	72
11	P-0954	Oct 8, 2026	Hygiene	Cleaning	Kept	9	120	95
12	P-1189	Oct 8, 2026	Dr. Ortiz	New patient	Kept	15	365	21

What Excel does for medical office

Work out your no-show rate

COUNTIF counts the rows marked No-show. The question is what to divide by. Dividing by every booked appointment mixes in cancellations, which freed the slot ahead of time and are a different problem. Most offices divide by appointments that were still expected on the day: everything booked minus cancellations.

Keep the Status words consistent (Kept, No-show, Cancelled). COUNTIF is not case-sensitive, but "No show" without the hyphen is a different word and will not be counted. A drop-down list (Data > Data Validation > List) stops that.

=COUNTIF(E2:E12,"No-show")/(COUNTA(E2:E12)-COUNTIF(E2:E12,"Cancelled"))

Result: 0.2 shows as 20% with Percent format

2 no-shows out of 10 appointments that were still on the books: a 20% no-show rate. Dividing by all 11 bookings would understate it at 18%.

Average wait time per provider

Log wait time as plain minutes (18, not 0:18) so it averages cleanly. AVERAGEIFS averages only the rows for one provider, and it skips empty cells, which is exactly what you want: a no-show has no wait time, and leaving the cell blank keeps it out of the average.

This is the main gotcha with wait times. Typing 0 for a no-show tells Excel the patient was seen instantly and drags the average down. Leave it empty.

=ROUND(AVERAGEIFS(F2:F12,C2:C12,"Dr. Ortiz"),1)

Result: 19.8

Dr. Ortiz's four kept visits waited 7, 26, 31 and 15 minutes: 19.8 minutes on average, well above the 15.3-minute office average.

Share of patients who waited too long

An average can hide a few very long waits. A better front-desk measure is the share of visits over your target, here 20 minutes. COUNTIF with ">20" counts the long waits, and COUNT (which counts numbers only) gives the number of visits that have a wait logged.

Put the target in its own cell and use ">"&J1 instead of ">20" if you want to try different targets without editing the formula.

=COUNTIF(F2:F12,">20")/COUNT(F2:F12)

Result: 0.25 shows as 25% with Percent format

2 of the 8 logged waits (26 and 31 minutes) were over 20 minutes: 25% of visits.

Total the open balance in an aging bucket

Billing teams group unpaid balances by how long they have been outstanding: 0-30, 31-60, 61-90 and over 90 days. SUMIFS with two conditions on the same column adds up one bucket: more than 30 days AND no more than 60.

Be careful at the edges. Use > for the low end and <= for the high end on every bucket, so a balance at exactly 60 days lands in one bucket and never in two.

=SUMIFS(G2:G12,H2:H12,">30",H2:H12,"<=60")

Result: 505

$505 sits in the 31-60 day bucket: P-1213 at 45 days ($410) and P-1150 at 33 days ($95).

See how much of your A/R is getting old

The number managers watch is the share of the open balance that is past 90 days, because the older a balance gets, the harder it is to collect. SUMIF adds the balances over 90 days and SUM gives the whole open balance.

Rows with no balance have an empty Days outstanding cell, so they never match ">90" and do not distort the result.

=SUMIF(H2:H12,">90",G2:G12)/SUM(G2:G12)

Result: 0.085106382979 shows as 8.5% with Percent format

$120 of the $1,410 open balance is over 90 days old: 8.5%, all from P-0954.

Count unique patients seen, not visits

P-1042 came in twice this week, so counting kept visits (8) overstates how many different patients you saw. FILTER keeps the patient IDs for kept visits, UNIQUE removes repeats, and COUNTA counts what is left.

This needs Excel 365 or Excel 2021 or later. In older versions, use Remove Duplicates on a copy of the column instead.

=COUNTA(UNIQUE(FILTER(A2:A12,E2:E12="Kept")))

Result: 7

7 different patients across 8 kept visits, because P-1042 was seen on both October 5 and October 7.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many appointments were no-shows?

Your formula for exercise 1

Show answer
=COUNTIF(E2:E12,"No-show") = 2. COUNTIF counts the cells in E2:E12 that say No-show: 2.
2What is the total open balance across all visits?

Your formula for exercise 2

Show answer
=SUM(G2:G12) = 1410. SUM adds every balance in G2:G12, giving $1,410; the zeros for paid visits add nothing.
3What is the average wait in minutes for all logged visits?

Your formula for exercise 3

Show answer
=AVERAGE(F2:F12) = 15.25. AVERAGE ignores empty cells, so only the 8 kept visits count: 15.25 minutes.
4How many Hygiene appointments were kept?

Your formula for exercise 4

Show answer
=COUNTIFS(C2:C12,"Hygiene",E2:E12,"Kept") = 2. COUNTIFS counts rows where both conditions are true: 2 kept Hygiene visits out of 3 booked.
5Which patient ID had the longest wait?

Your formula for exercise 5

Show answer
=XLOOKUP(MAX(F2:F12),F2:F12,A2:A12) = P-1222. MAX finds 31 minutes and XLOOKUP returns the ID on that row: P-1222.
6How much open balance is more than 60 days old?

Your formula for exercise 6

Show answer
=SUMIF(H2:H12,">60",G2:G12) = 300. Only the 72-day and 95-day balances match, so the result is $180 + $120 = $300.
7What share of new-patient appointments were kept?

Your formula for exercise 7

Show answer
=COUNTIFS(D2:D12,"New patient",E2:E12,"Kept")/COUNTIF(D2:D12,"New patient") = 0.75. 3 of the 4 new-patient bookings were kept: 75%. New-patient no-shows are worth tracking on their own because they often need a reminder call.
8What is the average age of the open balance, weighted by dollars? Round to 1 decimal.

Your formula for exercise 8

Show answer
=ROUND(SUMPRODUCT(G2:G12,H2:H12)/SUM(G2:G12),1) = 40.1. SUMPRODUCT weights each balance by its age, so the large 45-day balance counts more than the small 95-day one: 40.1 days. A plain AVERAGE of the days would treat a $95 balance the same as a $410 one.

Free templates for medical office

Guided project: Front office staff hours

Total each person's week, flag hours over target and find your thinnest day.

5 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate a no-show rate in Excel?

Count the no-shows with COUNTIF and divide by the appointments that were still booked on the day: =COUNTIF(E2:E12,"No-show")/(COUNTA(E2:E12)-COUNTIF(E2:E12,"Cancelled")). Format the cell as a percentage. Decide once whether late cancellations count as no-shows in your office, and use the same rule every month so the numbers compare.

How do I make an A/R aging report in Excel?

Work out days outstanding as an as-of date minus the date of service, put each balance into a bucket with a nested IF (0-30, 31-60, 61-90, over 90 days), and total each bucket with SUMIF. The free billing aging template on this page has all of it set up.

How should I enter wait times so Excel can average them?

As plain minutes (18, 26, 31), not clock times. Leave the cell empty for no-shows and cancellations; AVERAGE and AVERAGEIFS skip empty cells, but a 0 would count as a zero-minute wait and pull the average down.

Is it OK to keep patient information in a spreadsheet?

That depends on your practice's privacy policy and the rules that apply to you, so check with whoever handles compliance. For reports like these, patient or account IDs are usually all you need, and keeping names out of the file lowers the risk if it is shared by mistake.

Excel for other fields

Updated November 2, 2026