How to build an appointment tracker and follow-up system in Google Sheets or Excel

Why you need a list alongside your calendar

Your calendar shows when something happens. But many requests are not an appointment yet: someone is waiting for a price, needs to be called back or didn't show up. None of those are on the calendar, and they are exactly the ones that get lost.

A follow-up list solves that. Each request gets one row, with a status, a person responsible and a date on which someone looks at it again. The rule of thumb: the calendar is the schedule, the list is the memory. You need both.

Step 1: Set up the columns

Open a new, blank spreadsheet and name the first tab Appointments. In row 1, type these thirteen headings, from column A to column M. Keep this order, because the formulas later on depend on it.

Step 2: Agree on fixed statuses

A status says in one word where a request stands. Use only these seven, so everyone means the same thing and you can filter.

Step 3: Freeze the header row and create drop-down lists

Drop-down lists keep one person from typing “confirmed,” another “ok” and a third “all set.” First create a second tab called Lists. In row 1, type the headings Status, Source and Consent. Below them, starting in row 2, put the seven statuses in column A, the five sources (Phone, WhatsApp, Website/email, Walk-in, Other) in column B and the words Yes and No in column C.

In Google Sheets

1. Freeze the header row

   Click in row 1 and choose View → Freeze → 1 row. The headings now stay in place when you scroll down.

2. Drop-down list for the status

   Select H2 to H500 and choose Data → Data validation → Add rule. Under the criteria, choose a drop-down list from a range (“Dropdown (from a range)”) and enter Lists!A2:A8.

3. Reject invalid input

   In the advanced options, set invalid input to be rejected, and click Done.

4. Repeat for Source and Consent

   Do the same for B2:B500 with Lists!B2:B6 and for K2:K500 with Lists!C2:C3.

5. Date columns

   Select columns A and J and choose Format → Number → Date.

In Excel

1. Freeze the header row

   Choose View → Freeze Panes → Freeze Top Row. In some versions the button has a slightly different name.

2. Drop-down list for the status

   Select H2 to H500 and choose Data → Data Validation. On the Settings tab, under Allow, choose List, and under Source enter =Lists!$A$2:$A$8.

3. Repeat for Source and Consent

   Do the same for B2:B500 with =Lists!$B$2:$B$6 and for K2:K500 with =Lists!$C$2:$C$3.

4. Date columns

   Select columns A and J, right-click, choose Format Cells and then Date.

Step 4: See at a glance what needs to be done today

The simplest way is a filter. In Google Sheets choose Data → Create a filter, in Excel Data → Filter. In the filter for column H, uncheck the statuses Completed and Canceled, and sort column J from oldest to newest. What has been waiting longest is now at the top.

If you also want a counter, put this formula in cell O1 (leave column N empty, so it falls outside the filter). It counts the open rows with a follow-up date of today or earlier.

A color makes it even clearer. Select A2:M500. In Google Sheets, choose Format → Conditional formatting and pick “Custom formula is” as the rule. In Excel, choose Home → Conditional Formatting → New Rule and pick “Use a formula to determine which cells to format” as the rule type. Enter the formula below and choose, for example, a light orange fill.

Step 5: Share the list safely with your team

• Google Sheets: click Share and add coworkers using their own email address. Give whoever makes appointments the Editor role, and whoever only needs to look the Viewer role. Leave General access set to “Restricted,” not “Anyone with the link.”
• Excel: put the file in OneDrive or SharePoint (with Microsoft 365) and share it with specific people using Share. Without online storage, keep the list on one computer, for example at the front desk.
• Don't email copies around: then nobody knows which version is right.
• Give each coworker their own access instead of one shared password. If someone leaves, you only remove their access.
• Agree on how long you keep completed rows, and delete them after that.

Step 6: Link the list to your calendar and prevent double bookings

Use one shared calendar for everything that can be booked, for example in Google Calendar or the Outlook calendar. If you have several chairs, treatment rooms or mechanics, create a separate calendar for each chair, room or staff member. Then these rules apply:

1. Check first, then commit. Nobody gives a customer a time without first checking the calendar.
2. Record it right away. Put the appointment on the calendar immediately. Only then fill in Confirmed time and set the status to Confirmed.
3. Holds are temporary. If you want to hold a time for a while, put “HOLD – [first name]” on the calendar and a follow-up date in the list. If the customer doesn't confirm in time, remove the hold.
4. Build in some slack. Leave time between appointments for running over, cleaning up or travel.
5. Rescheduling means changing two things. Change the calendar and the list: status Rescheduled, new Confirmed time.
6. Keep the calendar minimal. Put only the first name and the service in the calendar entry. Calendars are often shared more widely than the list.

Step 7: The ten-minute daily routine

A list only works if someone looks at it every day. Agree on who does that and when, for example right after opening.

1. Enter new requests (2 minutes)

   Put everything since yesterday in the list: WhatsApp, voicemail, notes on paper, walk-ins. Each request gets a row with the status New.

2. Follow up what is due today (4 minutes)

   Look at the counter or the filter. Carry out the next action for each row, and update the status, next action and follow-up date.

3. Wrap up yesterday (2 minutes)

   Set yesterday's appointments to Completed or No-show.

4. Prepare for tomorrow (2 minutes)

   Compare tomorrow's calendar with the list. Only send a reminder where Consent to contact is set to Yes.

Confirmations and reminders: only with consent

A confirmation and a reminder prevent misunderstandings and empty slots on the calendar. First ask whether the customer wants them, and note the answer in column K. If it says No, you don't send reminders or offers. You then only get in touch when it is needed for the appointment itself, for example if you have to reschedule.

Example: a morning at a hair salon

Checklist and next step

• Appointments tab with the thirteen columns in the right order
• Header row frozen
• Drop-down lists for Status, Source and Consent to contact
• Date columns set up and tested by sorting
• Counter or color rule for today's follow-up
• Shared with the right people, with individual access for each person
• Rules for the calendar, holds and rescheduling written down
• One person and one fixed time for the daily routine

Would you rather not start with a blank spreadsheet? Download the ready-made list with drop-down lists and example rows. In lesson 4 of the free course you'll learn how to roll out this system with your team and how to handle rescheduling, cancellations and no-shows.

Would you like to hand over part of this work later? Bits Please can set up Claudia to take over suitable, recurring tasks, such as recording requests or scheduling appointments, depending on the package and the supported integrations. Everything in this article also works without it.

Frequently asked questions