Fire drill headcount in Google Sheets: who is still missing at the assembly point

Schools run fire drills through the year, and every one ends the same way: classes lined up at an assembly point, teachers reading names from a class list, someone with a clipboard collecting the totals. The drill itself works. The record afterwards is the hard part: what time the alarm sounded, how long until the last class was complete, who was missing and why, and what went wrong, written up from scraps of paper by someone who was also outside in the rain.

This guide covers what a drill record usually shows, a free Google Sheets roster, where it breaks at a busy assembly point, and a setup where students' ID cards are scanned as they reach the assembly point, with formulas that list who has not been scanned since the alarm.

Is a scanned headcount a safety system?

No, and it is worth being plain about it before anything else. It is a drill record and a convenience copy. It does not raise an alarm, it does not know where anyone is, and it only knows about the cards that were scanned.

• Keep the paper class lists and your school's emergency procedure. The roll call from the paper list is the safety step; the scan is the record.
• Never delay an evacuation, or a roll call, to scan a card. If scanning slows the line, stop scanning and fill in the record afterwards.
• In a real emergency, assume there is no signal and do not rely on the spreadsheet. Print or screenshot lists as part of the drill, never during the real thing.
• A scan proves a code was presented, not who was holding it. A card scanned for a friend still looks like that student was there.
• Your fire authority, your district and your insurer decide what a drill must include, how often you run one and which records you keep. Nothing in this guide changes that.

If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back, each with its original scan time. The list in the sheet only changes once those scans arrive, so at an assembly point with weak signal it can show students as missing who are standing in line. That is fine for a record written up afterwards, and one more reason the paper roll call comes first.

What should a fire drill record show?

The rules that apply to your school decide this, so check with your fire authority or district. Most drill records carry the same core facts. OneTap's article on documenting emergency drills, written for US workplaces and read on 2026-10-12, lists the date and time of the drill, the type of emergency simulated, the total evacuation or response time, the names of all employees who participated, any issues observed during the drill, and the corrective actions planned. For a school, that translates to a Drill log tab with one row per drill:

• Date, drill type and the alarm time.
• The time of the last scan at the assembly point, and the minutes from the alarm to that scan.
• Students on the roster, students in school that day, and students not scanned when the drill ended, with how each one was resolved, such as at an appointment or in the nurse's office.
• Issues seen, such as a blocked exit, a class that went to the wrong assembly point or an alarm that could not be heard in one room, and the action agreed for each.

How often you run drills and how long you keep the log are set locally. This guide does not give a number for either.

How do I run the headcount free in Google Sheets?

With a status column on the roster. On the Roster tab, select column D from row 2 down, choose Insert, Dropdown, and add the options Here and Absent today. In the morning, the office sets Absent today for each student missing from the register. At the assembly point, each teacher opens the spreadsheet in the Google Sheets app, filters to their class, and sets Here for each student present. A blank status means not yet accounted for.

• Number with no status yet, on a Drill tab: =COUNTIFS(Roster!A2:A, "<>", Roster!D2:D, "")
• Their names: =FILTER(Roster!B2:B, Roster!A2:A<>"", Roster!D2:D="")
• After the drill, copy the figures into the Drill log, then select column D from row 2 down and press Delete, ready for the next drill.

Where does a shared roster break at the assembly point?

• Scrolling. A school of 400 is 400 rows, and finding your own students on a phone in the wind takes longer than reading a paper list.
• Many teachers editing one spreadsheet at once, on weak signal, cannot be sure they are seeing each other's changes.
• A status has no time. Afterwards you know a student was marked Here, not when, so the time the last class was complete has to be written down separately.
• Clearing the column for the next drill wipes this drill's record unless someone copies it first.
• Anyone can set Here for a student who is not there, and nothing shows it was a mistake.

Recording at the doors instead does not help. OneTap's drill article puts it this way: when 200 people pour out of a building through four stairwells in under three minutes, paper sign-out sheets at exit doors do not produce reliable records. Its heading for the alternative is capture attendance at the assembly point, not at the exits, and the same holds for a school.

How does scanning ID cards at the assembly point work?

This is the part QR to Sheets sells. An administrator signs in with Google once, connects the school's spreadsheet, and creates a scanner link that writes to a tab called Log. During the drill, a marshal at each assembly point opens the link on a phone. After the teachers' roll call, students file past and show their card, and the marshal scans each one, about a second per student once the line is moving. At that pace, 400 students past one phone take around seven minutes, which is why larger schools put a phone at each assembly point or with each year group. There is no app and no account for the marshal, and they never get access to the spreadsheet.

• Cards students already carry: the scanner reads QR codes (including Micro QR), Data Matrix, Aztec, PDF417, Code 128, Code 39, Code 93, EAN-13, EAN-8, UPC-A, UPC-E, ITF (including ITF-14) and GS1 DataBar. It does not read Codabar. Scanning student ID cards covers checking a card before relying on it.
• No cards: print a QR card per student holding only the ID with the free QR code ID card maker. It needs no signup and makes the cards in your browser.
• A forgotten card: the marshal types the ID into the scanner's manual entry field, which writes the same kind of row. Do it after the line has gone through, never instead of moving people.
• Each scan adds a row: the time in column A, the card's ID in column B and the link in column C, then the workspace name, the device type and a scan ID. The time is text in the form YYYY-MM-DD HH:MM:SS, in your workspace timezone, and Google Sheets stores it as text.
• Open the scanner link on each marshal's phone before the drill, inside the building where there is signal, and keep the page open. A page reloaded with no signal will not load.
• If your daily register is scanned on the same link, a student scanned that morning shows an amber Already scanned notice at the assembly point, and the row is saved anyway. Keep the link's Accept duplicate scans setting on: switched off, a link records each code once, ever, and would refuse every drill scan of a student scanned before.

Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. People being scanned never open the link and need nothing. Three assembly points with a phone each are three devices.

How do I list who has not been scanned since the alarm?

Add a Drill tab. Type the date and time of the alarm into B1, for example 2026-10-14 10:15; Google Sheets recognises that as a date and time. Only scans at or after that time count, so if you are unsure of the exact minute, a minute early is safer than late. Put labels in A2 to A5 and these formulas beside them, and the list in A6:

• Not scanned since the alarm, in A6: =IFERROR(FILTER(Roster!A2:C, Roster!A2:A<>"", ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0))), "Everyone on the roster has been scanned")
• It lists the ID, name and class of every student on the roster with no scan at or after B1. VALUE() turns each text timestamp into a real date and time, and IFERROR skips blank rows. Before the first scan it lists everyone, which is correct.
• Count, in B2: =SUMPRODUCT((Roster!A2:A<>"")*ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0)))
• Count for one class, in B3: =SUMPRODUCT((Roster!C2:C="7B")*ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0)))
• Last scan since the alarm, in B4, formatted as Format, Number, Date time: =MAX(FILTER(IFERROR(VALUE(Log!A2:A), 0), IFERROR(VALUE(Log!A2:A), 0)>=$B$1)). It shows #N/A until the first scan.
• Minutes from the alarm to that scan, in B5: =ROUND((B4-$B$1)*1440, 1)

The last scan is only the time the last student reached you if the list in A6 is empty. Until then it is simply the latest scan. MAX takes the latest time, not the bottom row, so scans that reached the sheet late still count correctly.

Leaving out students who are absent today

If your morning register is scanned on the same link into the same Log tab, the list can skip students who were never in school that day. This version keeps only students with at least one scan on the alarm's date:

• Not scanned since the alarm, in school today: =IFERROR(FILTER(Roster!A2:C, Roster!A2:A<>"", COUNTIFS(Log!B:B, Roster!A2:A, Log!A:A, TEXT($B$1,"yyyy-mm-dd")&"*")>0, ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(VALUE(Log!A2:A), 0)>=$B$1), 0))), "Everyone in school today has been scanned")
• TEXT($B$1,"yyyy-mm-dd")&"*" builds a pattern such as 2026-10-14*, which matches every text timestamp from that day; Google's COUNTIF help confirms that * matches zero or more characters. A student who arrived after the register still counts as in school once they are scanned at the assembly point.
• A student who left early but was scanned in the morning still appears. Check the list against the office's sign-out record before treating anyone as missing.

The guide to finding who is absent has the same roster-minus-scans idea by date, a version for Google Form timestamps, and fixes for a list that shows everyone.

What about visitors, staff and contractors?

Students are a known roster. Adults come and go, so the question for them is who signed in and has not signed out. The sign-in sheet guide covers who is still in the building with a sign-in and sign-out log, and the visitor log page shows reception scanning a pass on the way in and on the way out. The same honesty applies: print or screenshot the list during the drill, and keep the paper fallback.

Which fits: paper, a shared roster, a drill app, or scanned cards?

Dedicated tools exist for this. As of 2026-10-12, OneTap's attendance tracker page lists real-time headcounts for emergency drills, field trips, and campus safety, and its pricing page lists a free plan with up to 20 profiles and a Basic plan at 49.99 USD per month billed yearly with up to 250 profiles.

Where each one wins. Paper wins whenever there is no signal or no battery, which is why it stays in the procedure. A status dropdown wins for a small school with few classes. A drill or attendance app wins if you want headcounts and reports without building formulas. QR to Sheets fits a school that already has student cards or a scanned daily register, wants a time on every record, and wants the drill log in its own Google Sheet.

What does it cost?

The free plan is one scanner link, one Google Sheet, one phone and 300 scans in total, not per month. That is enough to try a drill with one class or a small school. A school of 400 cannot record even one full drill on the free plan, and several assembly points need several phones. Premium is 7 USD per phone per month with unlimited scans and links, so three assembly points cost 21 USD a month whatever the number of students. The guide to several phones scanning into one sheet covers sharing one link across phones.

Step by step

1. 1

   Keep a Roster tab

   One row per student: the ID in column A, the name in column B and the class in column C. The same tab serves the daily register, so it is usually there already.

2. 2

   Plan one phone per assembly point

   Decide who scans at each assembly point, open the scanner link on that phone before the drill, and test it with one card.

3. 3

   Note the alarm time

   As soon as the alarm sounds, someone types the date and time into B1 of a Drill tab, or does it straight after the drill. The formulas only count scans made after it.

4. 4

   Roll call first, then scan

   Teachers take their usual roll call from the paper list. Students then file past the marshal, who scans each card, about a second per student once the line is moving.

5. 5

   Read the list and log the drill

   The Drill tab lists students not scanned since the alarm and the time of the last scan. Copy the figures to a Drill log tab with any issues and actions.

Frequently asked questions