Appointment Tracker in Excel for Clinics and Small Teams

An appointment tracker in Excel is a simple booking log: one row per appointment, with the date, time, client ID, provider and status. Add a few COUNTIFS formulas and it tells you how busy each day is, who is double-booked and how many people didn't show up. This guide builds one step by step for a small clinic or team, and every formula works on the sample data below.

What an appointment tracker in Excel needs#

A good tracker is one flat list, not a calendar grid. One row per booking makes it easy to sort, filter and count. Use these columns:

Here is a week of sample data in A1:E8. All the formulas below use it:

Dates are shown day/month/year. If you plan staff rather than clients, the same layout works; see the shift schedule template guide for a rota version.

Step 1: set up the log as a table with drop-down lists#

Turn the list into an Excel table so new rows pick up formats and formulas automatically. Click any cell in the list and press Ctrl+T (Mac: ⌘T), then confirm that the table has headers.

Type dates as real dates, such as 9/11/2026, and times with a colon, such as 9:30. Excel stores a time as a fraction of a day, so 12:00 is 0.5. If a time shows as a decimal, format the column as Time with Ctrl+1 (Mac: ⌘1). Don't type "9.30" with a full stop: Excel reads that as the number 9.3, not a time. Use a colon, and add a space before AM or PM if you use them, such as 2:00 PM.

Add drop-down lists for provider and status#

Drop-down lists stop typos like "No show" and "Noshow", which COUNTIFS would count as different values.

1. Select the Status column below the header.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In Source, type Booked,Attended,No-show,Cancelled and click OK.

Do the same for Provider, pointing Source at a short list of names elsewhere in the workbook, so you only update it in one place when staff change.

Step 2: count appointments per day and per provider#

COUNTIFS counts rows that meet every condition you give it. To count all bookings on the date in H2:

=COUNTIFS($A$2:$A$8,H2)

With 9 November 2026 in H2, the result is 2. To count only Dr Lee's bookings that day, add a second condition:

=COUNTIFS($A$2:$A$8,H2,$D$2:$D$8,"Dr Lee")

That returns 1. For a whole week, use two date conditions joined with &:

=COUNTIFS(D2:D8,"Dr Lee",A2:A8,">="&DATE(2026,11,9),A2:A8,"<="&DATE(2026,11,13))

The result is 4: Dr Lee has bookings on 9, 10, 11 and 13 November, and the 16 November booking falls outside the week. The article on COUNTIFS with a date range explains why the operator must sit inside quotes and join the date with &. The COUNTIFS function guide lists the full syntax.

Step 3: flag double bookings#

Excel can't block a double booking by itself, but a helper column can flag one the moment it is typed. In F2, count the rows with the same date, time and provider:

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$D$2:$D$8,D2)>1

Fill it down. Every row on the sample data shows FALSE: there are no clashes. If you added a second Dr Lee booking at 9:00 on 9 November, both of those rows would turn TRUE. Note that C-1041 appears twice on different days, which is fine; the check only looks at date, time and provider together.

Step 4: show today's and upcoming appointments with TODAY#

TODAY() returns the current date and updates each time the file recalculates, so the same formula is correct every morning. To count today's bookings that are still open:

=COUNTIFS(A2:A200,TODAY(),E2:E200,"Booked")

To count bookings in the next seven days, including today:

=COUNTIFS(A2:A200,">="&TODAY(),A2:A200,"<"&TODAY()+7)

In Excel 2021 or Microsoft 365, FILTER can list them, sorted by date: =SORT(FILTER(A2:E200,A2:A200>=TODAY()),1). The TODAY function guide covers how it recalculates.

Past bookings still marked Booked need follow-up: either the client attended or didn't. Highlight them with Home > Conditional Formatting > New Rule > Use a formula, and the rule =AND($A2<TODAY(),$E2="Booked"). The guide to highlighting overdue dates shows the setup screen by screen.

Step 5: work out the no-show rate#

The no-show rate is the share of appointments that should have happened where the client didn't come. Cancelled and future bookings don't count:

=COUNTIF(E2:E8,"No-show")/(COUNTIF(E2:E8,"Attended")+COUNTIF(E2:E8,"No-show"))

On the sample data that is 2 ÷ (3 + 2) = 0.4. Format it as a percentage with Ctrl+Shift+% (Mac: ⌃⇧%) to show 40%. Add a provider condition with COUNTIFS to compare staff, for example =COUNTIFS(D2:D8,"Dr Lee",E2:E8,"No-show") returns 1.

Keep client data safe#

Appointment data about health or personal matters is sensitive. Store a client ID rather than full names, and keep medical details out of the tracker. Protect the file with a password, share it only with people who need it, and follow your organisation's privacy and data protection rules. If many people need to book at once, or clients book online, a dedicated scheduling system is a better tool, and Excel stays useful for the reports.

The same build-a-log approach powers the employee leave tracker, and Excel skills for healthcare admin covers the other tasks clinic office roles use.

Practise it#

Every report in this tracker is a COUNTIFS with the right conditions. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and it checks it instantly. Then practise dates in criteria for weekly and monthly ranges, and try the hospital admissions case study to work out length of stay, readmission rates and a by-ward summary on healthcare data.