Teacher Substitution Data Entry System in Excel

The Teacher Substitution Data Entry System in Excel is a single macro-enabled workbook that records every teacher absence and the cover that was arranged for it – the date, who was out, who took the class, which grade and subject, which period, what the cover was paid, and where the arrangement stands. One file, four sheets, an eight-field entry form, four live KPI cards and an eleven-column record table. The Add, Update, Delete and Reset buttons are real VBA, so nothing has to be copied down and no formula can be broken by a paste. Six sample records ship inside so you can see it working in about thirty seconds. Instant download, one-time payment of $6.99, no subscription and no account to create. Teacher Substitution Data Entry System in Excel

Key Features of the Teacher Substitution Data Entry System in Excel

• Eight-field entry form – Date, Absent Teacher, Substitute Teacher, Class/Grade, Subject, Period, Substitution Fee and Status, all in one panel at the top of the Data Entry sheet.
• Four live VBA buttons – Add, Update, Delete and Reset. Delete asks you to confirm and shows the Record ID first, so you cannot remove the wrong cover by accident.
• Automatic Record IDs – every entry is stamped TSL-0001, TSL-0002 and onwards, so two covers on the same day for the same grade never blur together.
• Double-click to edit – double-click any record to load it back into the form. The Record ID is held behind the scenes, so Update finds the right row wherever it has moved to; you never have to re-select it.
• Five-stage Status list – Pending, Confirmed, Completed, Cancelled and No Show. That covers a cover request from “we still need someone” through to “the substitute never arrived”, and the list is yours to edit.
• Twelve named substitutes – the Substitute Teacher List on the Setting sheet ships with twelve sample names; replace them with your own supply pool and the dropdown follows.
• Grades 1 to 12 and twelve subjects – Mathematics, English, Science, History, Geography, Biology, Chemistry, Physics, Computer Science, Physical Education, Art and Music, all editable.
• Eight teaching periods – Period 1 through Period 8, so a cover is logged against the exact slot it filled rather than a vague “morning”.
• Four KPI cards – Total Substitutions, Total Fees Paid, Pending Requests and Completed Covers. They live on the Setting sheet and appear on Data Entry as linked pictures, so they refresh on their own as you type.
• Absent Teacher is free text – deliberately. Your permanent staff list is not a dropdown, so you can log a cover for anyone, including a visiting or newly joined teacher, without editing the Setting sheet first.
• Entry timestamp column – each record stores the date and time it was saved, kept separate from the date of the absence itself.
• One .xlsm file – desktop Microsoft Excel for Windows, macros enabled, no add-ins, no cloud service, no monthly fee.
• Teacher Substitution Data Entry System in Excel

What’s Inside the Teacher Substitution Data Entry System in Excel

The download is a single zip holding one workbook with four sheets.Teacher Substitution Data Entry System in Excel

1. Data Entry. The working screen. The four KPI cards sit top left, the eight-field form sits top centre, and the Add, Delete, Update and Reset buttons sit top right. Below them the record table runs across eleven columns: S.No., Record ID, Date, Absent Teacher, Substitute Teacher, Class/Grade, Subject, Period, Substitution Fee, Status and Entry TimeStamp. Records begin on row 15.

2. Setting. Five editable lists and the four real KPI cards. Substitute Teacher List carries twelve names, Class/Grade List carries Grade 1 to Grade 12, Subject List carries twelve subjects, Period List carries Period 1 to Period 8 and Status List carries five stages. Every dropdown on the Data Entry sheet reads from here, so changing a list here changes the form and the table together.

3. Instructions. A one-page How To Use sheet covering entering records, updating without re-selecting the row, deleting safely, how the stat cards work, editing the dropdown lists, and enabling macros the first time you open the file.

4. Get More Templates. Links back to the NextGenTemplates store.

What the KPI Cards Actually Count

The four cards are ordinary Excel formulas on the Setting sheet, and it is worth knowing exactly what they add up before you quote a number to a head of department.

• Total Substitutions counts filled Date cells in the record table. A row saved without a date is stored but not counted, so keep Date filled on every entry.
• Total Fees Paid is an unfiltered sum of the whole Substitution Fee column. It includes rows still marked Pending and rows marked Cancelled or No Show. In the shipped sample it reads $800, of which only $230 sits against Completed covers. Treat it as “total fees logged”, and filter the table by Status when you need the amount actually payable.
• Pending Requests counts rows with Status “Pending”.
• Completed Covers counts rows with Status “Completed”. Teacher Substitution Data Entry System in Excel

Because only two of the five statuses have their own card, Pending plus Completed will not add up to Total Substitutions whenever a record sits at Confirmed, Cancelled or No Show. That is arithmetic, not a fault – but it is the sort of thing worth knowing on day one rather than in a meeting. Teacher Substitution Data Entry System in Excel

Teacher Substitution in Excel vs. a Paper Cover Book vs. a Timetabling System – Where This Fits

What This Workbook Is Not

It is a log, and it is honest about that. It does not build or read a timetable, and it does not choose or suggest a substitute for you – you decide who covers the class and then record it here. It does not check whether the substitute you picked is free that period, does not warn you about clashes, does not send an email or SMS to anybody, and has no approval workflow. It is not a payroll system: the Substitution Fee column is a number you type, and the workbook never calculates pay, tax or deductions, nor produces a payslip. It is not an HR system of record, and it does not connect to a school MIS, SIS or Google Classroom. It is one desktop file on one computer at a time. Teacher Substitution Data Entry System in Excel

Who This Template Is For – and Who It’s Not For Teacher Substitution Data Entry System in Excel

It is for the cover coordinator, deputy head, school office administrator or department head who arranges day-to-day cover in a single school and wants a clean, searchable record of who was absent, who stood in, and what it cost. It suits small and mid-size schools, coaching centres, language schools and tuition academies that have outgrown a paper cover book but do not want a licensed system.

It is not for a multi-academy trust that needs one shared live record across sites, a school that wants substitutes auto-assigned from an availability matrix, anyone who needs the data on a phone, or a team where several people must edit at the same moment. If any of those describe you, a shared cloud tool or your MIS is the better answer.

How to Use the Teacher Substitution Data Entry System in Excel

1. Unzip and open the file in desktop Excel for Windows. Click Enable Content on the yellow security bar – the four buttons are VBA and will not run without it. If the file arrived by email, right-click it, choose Properties and tick Unblock first.
2. Open the Setting sheet and make the lists yours. Replace the twelve sample substitute names with your own supply pool, trim Grade 1 to 12 to the grades you actually teach, and edit the subject and period lists to match your school day.
3. Delete the sample data. Rows 15 to 20 on the Data Entry sheet hold six demo records. Clear them before you start logging your own.
4. Log your first cover. Fill the form – Date, Absent Teacher, Substitute Teacher, Class/Grade, Subject, Period, Substitution Fee, Status – and click Add. The record drops into the table with its own TSL number and a timestamp, and the cards update.
5. Update as the day resolves. A cover usually starts at Pending, becomes Confirmed, and ends Completed, Cancelled or No Show. Double-click the record, change the Status, click Update.
6. Report at the end of the week. Filter the table by Status or by Absent Teacher to see who has needed the most cover and what the term is costing.

Real-World Use Cases

• Daily cover desk. Log each absence as it is phoned in, mark it Confirmed once a substitute agrees, and see at a glance how many requests are still Pending before first period.
• End-of-term cover cost. Sum the Substitution Fee column for Completed rows and hand the finance office one number with the records behind it.
• Absence patterns. Sort by Absent Teacher over a term to see where cover keeps being needed, and by Subject to see which departments are stretched.
• No-show accountability. The No Show status gives you a written record when a booked substitute did not arrive – useful when reviewing an agency.
• Handover. When the cover coordinator is away, the file is the handover: one table, one ID per cover, no shorthand to decipher.

Frequently Asked Questions

Does it decide who should cover a class?

No. There is no assignment engine, no availability matrix and no suggestion list. You arrange the cover the way you do now and record the outcome here. The workbook keeps the record straight; it does not make the decision.

Is it a timetable?

No. It does not hold, read or build a timetable, and it cannot tell you whether the substitute you chose is teaching elsewhere that period. Period 1 to Period 8 are labels on a dropdown, not a schedule the workbook understands.

What exactly does the Total Fees Paid card add up?

Every value in the Substitution Fee column, with no filter on Status. In the shipped sample it shows $800, which includes a $125 Cancelled cover and two Pending covers worth $310. Only $230 of that is against Completed covers. If you need the payable figure, filter the table by Status and total the column yourself.

Why don’t Pending Requests and Completed Covers add up to Total Substitutions?

Because only two of the five statuses have a card. Records sitting at Confirmed, Cancelled or No Show are counted in Total Substitutions but appear on neither of the other two cards. The counts are each correct on their own terms.

What counts as a record for the Total Substitutions card?

A filled Date cell. The card counts the Date column rather than the Record ID column, so a row saved with the date left blank is stored in the table but not counted. Keep Date filled on every entry and the two always agree.

Can I add more substitute teachers, subjects or statuses?

Yes, and the way to do it matters. The dropdowns read from named ranges that are fixed to the rows the lists ship with – the substitute, grade and subject lists cover twelve rows each, periods eight and statuses five. Typing a thirteenth name directly under the last one will not appear in the dropdown. Either overwrite an entry you do not use, or extend the range in Formulas > Name Manager. The Instructions sheet says the dropdowns grow on their own; that is the one line on it to disregard.

Does it handle pay for the substitutes?

No. Substitution Fee is a number you type so the record is complete. The workbook does no pay calculation, applies no rates or tax, produces no payslip and sends nothing to payroll.

It holds teachers’ names – what about data protection?

The file stores whatever you type, on your own computer, and sends nothing anywhere. That also means the staff data in it is yours to look after: keep the workbook where your school’s policy says staff records belong, share it only with people entitled to see it, and delete records when your retention policy says so. We never see the file and cannot recover it for you.

Will it work on my Mac, my phone, or in Excel for the web?

The workbook opens, but the buttons will not run. VBA macros need desktop Microsoft Excel for Windows. Excel for the web, Excel for iPad and Android, and Google Sheets do not run the code, so Add, Update, Delete and Reset will do nothing there.

Can two people use it at the same time?

No. It is a single .xlsm file, not a shared database. Put it on one machine and have one person own the entries. If two people must log covers simultaneously, you want a shared cloud tool instead.

Do I need to know VBA to change it?

Not for normal use. Renaming a field, editing the lists, recolouring the sheet and adding columns to the right of the table are all ordinary Excel work. Only changing what the buttons do requires opening the code.

About the Author

Built by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets, and Power BI experience. Founder of NextGenTemplates, reaching 300K+ subscribers across YouTube channels. Every template is hand-built and tested before release.

Explore Related Templates

• Student Attendance Data Entry System in Excel – the pupil-side twin of this file: who was in class, rather than who taught it.
• Class Schedule Data Entry System in Excel – record the timetable this log sits alongside.
• Homework Submission Data Entry System in Excel – same form-and-cards build, aimed at assignments.
• Exam Marks Entry Data Entry System in Excel – marks entry with the same Add / Update / Delete workflow.
• School Admin Dashboard in Google Sheets – when you want charts and a shareable link instead of a desktop log.
• School Management System Web App – the full multi-user system, for when one file on one desk is no longer enough.

More in Excel VBA Tools and Education Templates. Teacher Substitution Data Entry System in Excel

Get Your Copy

Download the Teacher Substitution Data Entry System in Excel for $6.99 instead of $11.99, pay once, and keep the file. Add it to your cart above, and the zip is yours to download the moment checkout finishes. A full walkthrough of how the workbook is built – including exactly what each KPI card counts – is on the PK-AnExcelExpert blog. Teacher Substitution Data Entry System in Excel

Last updated: 29 August 2026.

Watch the demo video: