No-show list in Excel: who missed the appointment
A no-show list is one row per appointment. You type the date, the time, a booking reference, the practitioner, and a status from a short list: booked, arrived, no-show, or cancelled. A callback column marks the word call only when the status is no-show. COUNTIFS counts those rows by practitioner and by day. This sheet is not a clinical record, a pharmacy stock list, or a staff absence rota. The reference is a booking code you already use. A diagnosis does not belong here. If the question is who did not come, the status word answers it each morning. The morning list is that filter, not a second notebook. Two codes for one visit become two rows, and the count doubles. Two bookings for one person on the same day are two rows: the count is of bookings marked no-show, not of people. Cancelled is not a no-show. Arrived is not a no-show. A blank status is neither: COUNTIFS ignores it when the criterion is the word no-show. Booked means the visit has not happened yet, so it is not a missed visit either. The share of no-shows, if you want it, is no-shows divided by arrived plus no-show for that practitioner. Cancelled and still-booked rows stay out of that denominator. A visit still marked booked has not happened. Putting it in the denominator makes a quiet afternoon look like a high miss rate. Label the share if you show it, so the meeting does not mix it with a count of every row. On Monday you filter to no-show and call from the reference. The count by practitioner is the number you read before you decide whether one list needs a second pass. You do not retype that number. If someone typed missed instead of no-show, that row stays invisible to the count until you correct the word. The sheet does not decide what share is too high. The trial lasts 7 days, with no card. Elyx can write the callback flag and the counts. You still set the status.
The Problem
The missed-visit list fails when it lives in a diary margin. A tick says someone did not come. The next day the tick has no practitioner, no date you can filter, and no way to separate a cancellation from an absence. Copying the diary into a cell without a status word does not fix that. A second failure is storing a clinical note in the same row you count. The morning call-back list should show a reference and a status. A diagnosis, a result, or a letter does not change the count, and it does not belong on a sheet that several people filter. A third failure is deleting the row once you have called. The count of no-shows for the week uses the status. Keep the row. If you called, say so in the note. Do not change no-show to arrived just because the call happened.
Benefits
Each appointment is one row with one status from a fixed list.
No-show, arrived, cancelled, and booked stay different words.
COUNTIFS counts no-shows by practitioner and by date.
The callback flag is the word call only on no-show rows.
A share, if you use one, excludes cancelled and still-booked visits.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Visits. In row 1 type: Date, Time, Reference, Practitioner, Status, Callback, Note. Date is a real date. Time is a time or a short label. Reference is the booking code, not a name if you can avoid one, and never a diagnosis. Practitioner is a short name you reuse. Status and Callback are constrained. Note is optional and no formula reads it. Freeze row 1. Do not merge cells.
If you turn the range into a table, new rows keep the formulas. A plain range works too.
Lock the status words
Select Status from row 2 down. Data, Data Validation, List. The source is a sheet named Lists with the four words booked, arrived, no-show, and cancelled. A synonym such as DNA, missed, or late will not match COUNTIFS. People must pick a word. Put the practitioner names on that Lists sheet too, the same way, so two spellings of one person do not split the count.
Waiting room delays are not a fifth status. If they sat down, the status is arrived.
Enter one visit you can check
On row 2 type a date, a time, a reference, one practitioner, and the status no-show. Leave Callback empty until the formula exists. Add a second row for the same day with status arrived, and a third with cancelled. You will predict the no-show count as 1 before you trust the sheet. Do not paste a letter or a result into Note.
Use the reference your booking book already uses. Do not invent a second code on this sheet.
Flag the rows to call
Callback is call when Status is no-show, and blank otherwise. With Status in E2, use =IF(E2="no-show","call",""). Filter Callback to call when you are making the calls. Do not type call by hand. If the status changes to arrived, the flag must clear itself. A cancelled row stays blank here. A booked row stays blank because the visit has not been missed yet.
=IF(E2="no-show","call","")The note can say that you called. It must not replace the status.
Count no-shows by practitioner and by day
On a small sheet named Morning, put a practitioner name in A2 and use =COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show"). For a date in C2, use =COUNTIFS(Visits!A:A,C2,Visits!E:E,"no-show"). The word must be no-show, with the hyphen. A blank status is not counted. Start at row 2 of Visits if you want to be certain the header is excluded. The header says Status, not no-show, so a whole-column count is usually safe. Write the rule next to the number.
=COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show")Do not count filtered rows by hand and type over the formula.
Optional share, with the right denominator
A share of missed visits among visits that were kept or missed is no-shows divided by arrived plus no-show. For the practitioner in A2: =IF((COUNTIFS(Visits!D:D,A2,Visits!E:E,"arrived")+COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show"))=0,"",COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show")/(COUNTIFS(Visits!D:D,A2,Visits!E:E,"arrived")+COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show"))). Format as a percentage. Cancelled rows are out. Booked rows are out. If the denominator is zero, the cell stays blank rather than showing a divide-by-zero error. This share is a description of the rows you typed. It is not a target.
If you do not need a share, skip this step. The count in step 5 is enough for the call list.
Sort the call list without losing history
Filter Status to no-show, or filter Callback to call. Sort Date oldest first if you call the oldest gaps first. Remove the filter before you close the file, or the next person will think rows vanished. Do not delete a row after the call. Type a short note. Leave the status as no-show so the week count stays true.
Conditional formatting on the word no-show makes the rows visible even before you filter.
Check three rows before you share the count
One no-show row must show call and be inside the practitioner count. One arrived row must show a blank callback and stay out of the no-show count. One cancelled row must do the same. If you built the share, two arrived and one no-show for the same practitioner is 1/3, shown as about 33 percent. A booked row is in neither the numerator nor the denominator. If a check fails, fix the status word before you send the count.
Write the three checks on the Morning sheet if more than one person updates statuses.
Template Features
One status per visit
Booked, arrived, no-show, or cancelled. The count depends on those exact words.
Callback only for no-shows
The flag is the word call, and it clears if the status changes.
=IF(E2="no-show","call","")Count by practitioner
COUNTIFS reads the practitioner and the status. You do not type the total.
=COUNTIFS(Visits!D:D,A2,Visits!E:E,"no-show")Count by day
The same count, with the date as the criterion.
=COUNTIFS(Visits!A:A,C2,Visits!E:E,"no-show")Share with cancelled excluded
Numerator is no-show. Denominator is arrived plus no-show. A zero denominator stays blank.
Concrete Examples
Three visits, one missed
Same practitioner, same day. You want the count before the call list goes out.
Row 1 status no-show. Row 2 status arrived. Row 3 status cancelled.
Result: The no-show count is 1. Callback is call only on row 1. The share is 1/(1+1), which is 50 percent. The cancellation is not in that share.
A blank status is not a no-show
Someone saved the row before picking a status. You do not want it to inflate the missed-visit count.
Status: empty. Practitioner and date are filled.
Result: COUNTIFS for no-show ignores the row. Callback is blank because the status is not the word no-show. Pick a status, then read the count again.
A call does not rewrite history
You phoned the patient after a missed visit. The week count should still show the miss.
Status stays no-show. Note: called, will rebook.
Result: The count still includes the row. Callback still says call, which is fine: clear the filter when the calls are done. Do not switch the status to arrived.
Pro Tips
Keep clinical text off this sheet
The reference identifies the booking. A result, a diagnosis, or a letter belongs in the record you already trust, not in a column that COUNTIFS never reads and that a filter will expose.
One practitioner spelling
The validation list stops Dr Lee and Lee from becoming two people. If a locum has no list entry yet, add the name on Lists before you type the row.
Do not mix staff absence here
A practitioner who is away is not a patient no-show. Close or move that day's bookings with the status you mean, cancelled or still booked. Do not mark them no-show to explain an empty clinic.
Copy the sheet by month if you archive
Copy Visits, name the copy with the month, and point Morning at the current sheet. Do not delete rows to make the call list look short.