No-shows and late cancellations are lost, unrecoverable chair time. The rate — missed appointments over booked — quantifies the leak and the revenue it costs.
The example
6 no-shows of 80 booked.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 6 / 80 | — |
| 3 | No-show rate | → 7.5% |
The formula
The formula:
How it works
How it works:
- Count no-shows and late cancellations with
COUNTIF(status, "No-show"). - Divide by booked appointments for the rate.
- Estimate the cost: no-shows × average ticket = revenue lost to empty chairs.
- Reduce it with deposits, reminders, and a cancellation policy — track the rate before and after.
A no-show is worse than an empty slot — it was promised time you turned others away for. Multiply the rate by your average ticket to size the leak: 7.5% of 80 weekly appointments at a $115 ticket is ~$690/week gone. That number justifies deposit requirements and automated reminders, and lets you prove they worked by comparing the rate month over month.
Try it: interactive demo
No-shows, booked, average ticket.
Variations
Count no-shows
From a log:
Revenue lost
No-shows × ticket:
Include late cancels
Both kinds:
Pitfalls & errors
Define late cancel. Decide the window (e.g. <24h) and count it with no-shows.
Over booked, not served. The denominator is appointments booked.
Zero booked. No appointments gives #DIV/0!.
Practice workbook
Frequently asked questions
How do I calculate no-show rate in Excel?
How much revenue do no-shows cost?
Should late cancellations count?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class