How to combine clinic appointment exports into one spreadsheet
By the Analistable team · Updated · 5 min read
Export only the fields you need (date, time, clinic, clinician, appointment type, status, duration and a pseudonymous patient ID), map each system's status words to one list, then append the exports with a Clinic column. Utilisation is booked minutes divided by available minutes; no-show rate is no-shows divided by appointments that were due to happen.
Part of our guide: How to answer questions across multiple spreadsheets
Start with the minimum data
Appointment exports are health data. Before combining anything, decide which columns the analysis actually needs and leave the rest in the booking system. For utilisation and no-show analysis you rarely need names, dates of birth, contact details or clinical notes.
| Keep | Why | Leave out |
|---|---|---|
| Appointment date and start time | Trends by day and hour | Patient name |
| Clinic and clinician | Comparison across sites | Date of birth, address |
| Appointment type and duration | Utilisation in minutes | Phone and email |
| Status (attended, DNA, cancelled) | No-show rate | Clinical notes, reason for visit |
| Pseudonymous patient ID | Repeat no-shows, new vs returning | NHS or national ID number |
Follow your organisation's data protection policy and, in the UK, your Caldicott and UK GDPR obligations. If you are unsure whether a tool may process patient data, ask your data protection officer before using it.
Step 1: line up the exports
Two clinics on different systems will name things differently: "DNA", "No show" and "Did not attend" mean the same; one system records cancellations as a status, another deletes the appointment. Build a small status map and apply it to every export:
| System status | Standard status |
|---|---|
| Attended / Arrived / Seen | Attended |
| DNA / No show / Did not attend | No-show |
| Cancelled by patient (under 24h) | Late cancel |
| Cancelled by patient / by clinic | Cancelled |
- Load each clinic's export into Power Query and rename columns to a shared set.
- Add a Clinic column with a fixed value per export.
- Merge with the status map to add Standard status.
- Convert dates and times to real date/time types; systems differ on day-month order.
- Append the clinics into one table.
The mechanics are the same as any multi-source append, described in combining data from multiple sources.
Worked example: one week across two clinics
| Clinic | Available mins | Booked mins | Utilisation | Due appts | No-shows | No-show rate |
|---|---|---|---|---|---|---|
| Riverside | 6000 | 5100 | 85.0% | 240 | 18 | 7.5% |
| Hillcrest | 4800 | 3600 | 75.0% | 160 | 20 | 12.5% |
| Both | 10800 | 8700 | 80.6% | 400 | 38 | 9.5% |
Utilisation = booked minutes ÷ available clinician minutes. No-show rate = no-shows ÷ appointments that were due to happen (attended plus no-shows), excluding cancellations made in advance. Hillcrest's lower utilisation and higher no-show rate together suggest slots are being lost twice: some never booked, some booked but not attended. Overall rates must be recalculated from the totals (38 ÷ 400), not averaged from the two clinic rates.
Available minutes come from clinician rotas, not the appointment export, so this is a join between two sources: appointments summed by clinic and day, and rota hours by clinic and day.
Questions the combined table answers
- No-shows by weekday and hour: a pivot of No-show rate with day in rows and hour in columns often shows a clear pattern, such as early Monday slots.
- By appointment type: follow-ups frequently have higher no-show rates than first appointments.
- Repeat no-shows: count no-shows per pseudonymous patient ID across all clinics; a patient who misses at one site may also miss at another, which a single-clinic report can't see.
- Lead time: if the export has a booking date, appointments booked far in advance tend to be missed more often. Group by lead-time bands to check this in your own data.
SELECT clinic, strftime(appt_start, '%A') AS weekday,
COUNT(*) FILTER (WHERE status = 'No-show') * 1.0
/ COUNT(*) FILTER (WHERE status IN ('Attended', 'No-show')) AS no_show_rate
FROM appointments
GROUP BY ALL
ORDER BY clinic, no_show_rate DESC;Keeping patient data on your machine
Uploading appointment exports to an online merge service sends patient-related data to a third party. A local route avoids that: Power Query in desktop Excel, a script on a managed laptop, or a tool that processes files in the browser. Analistable works the last way — exports are loaded into DuckDB in your browser and are not uploaded or stored on its servers — and you can ask "no-show rate by clinic and weekday" in plain English. See merging Excel files without uploading them for the options, and still check that any tool fits your organisation's policy. The guide to answering questions across spreadsheets has more worked questions of this kind.
Pitfalls that distort the numbers
- Double-booked slots: some systems export both appointments in an overbooked slot. Booked minutes can then exceed available minutes; cap utilisation at 100% or report overbooking separately.
- Group sessions: a class with eight patients is one slot of clinician time but eight appointments. Count minutes once per session for utilisation and per patient for no-shows.
- Time zones and daylight saving: exports that store times in UTC shift by an hour for part of the year, which moves appointments between hourly bands. Convert to local time first.
- Status recorded late: an appointment from this morning may still show as Booked because the clinic hasn't updated it. Analyse complete days only, and exclude statuses that are still open.
- Different week definitions: reporting periods should be the same calendar dates for every clinic before they are compared.
Frequently asked questions
- How do I calculate a no-show rate across several clinics?
- Sum no-shows and due appointments (attended plus no-shows) across all clinics, then divide. Don't average each clinic's rate, because clinics with few appointments would count as much as busy ones.
- Do I need patient names to analyse no-shows?
- No. A pseudonymous patient ID is enough to find repeat no-shows and new versus returning patients. Leave names and contact details in the booking system.
- What if one system deletes cancelled appointments?
- Then cancellation rates aren't comparable between clinics. Report utilisation and no-show rates, which don't depend on cancellations, and note the difference rather than mixing the figures.
- How do I get available minutes for utilisation?
- From clinician rotas or session templates, summed by clinic and day. Join them to booked minutes from the appointment exports on clinic and date.
- Can I combine exports from different booking systems?
- Yes. Rename each export's columns to a shared set, map every system's status words onto one standard list, convert dates to one format, then append with a Clinic column. Check a few appointments by hand afterwards.