Tracking student payments in Excel - and when to move to an app

The short answer: a table for tracking student payments needs a row for each student in each lesson, with ten columns: date, time, student, class, parent, phone, lesson type, rate, status and payment date. With one SUMIFS formula you get each student's debt. Excel is entirely enough for a tutor with few students who records from a computer. When recording moves to the phone and group lessons join, the table starts to break.

A recommended table template

The most important principle: one row for each student in each lesson, and not a row for each lesson. In a group lesson with three students there will be three rows, because each family pays separately, and each can pay at a different time.

A few practical notes:

• Uniform spelling of names. "Noa Levi" and "Noa Levi" (with a double space) are two different students as far as the formula is concerned. It is worth putting a drop-down list (data validation) in column C, taken from a students sheet.
• Status from a closed list. In column I as well it is better to have a drop-down list with only two values, so that "Paid.", "Paid up" and "V" do not appear.
• The rate in the row, not in a formula. If the lesson price was special (a double lesson, a sibling discount), write the actual amount. That way a future rate change does not retroactively change lessons that already took place.
• Private versus group. If you have two rates, take care to update columns G and H according to the number of participants. On the differences between the two types of lesson and how to price each, we wrote in private or group lessons?.

A formula to total the debt for each student

To know how much each student owes, open a second sheet, "Summary", with a student's name in column A. In column B put:

=SUMIFS(Sheet1!H:H, Sheet1!C:C, A2, Sheet1!I:I, "Unpaid")

The formula sums the rate column (H) in all the rows where the student's name (C) equals the name in cell A2, and the status (I) is "Unpaid". Drag the formula down for all the students.

To know how many lessons are open for each student, in column C:

=COUNTIFS(Sheet1!C:C, A2, Sheet1!I:I, "Unpaid")

An example (the numbers are for illustration only): Noa has in the table three private lessons at NIS 150 and two group lessons at NIS 100, and none of them was paid. SUMIFS will return 650, and COUNTIFS will return 5. When Ronit pays for the first three, you change their status to "Paid", fill in a payment date, and the debt drops to 200 automatically.

Want a debt by month? Add a condition on the date column to the formula, or use a pivot table with the student's name in the rows, the month in the columns and the sum of the rate in the values, and filter by "Unpaid".

Working habits that keep the table correct

A good formula will not help if the data is wrong. Three habits make the difference:

Record on the same day. Open the file right after the lesson and add a row. If that is not possible, at least write a note on the phone and transfer it to the table that evening. A lesson recorded after a week is a lesson that may be forgotten.

Update a payment on the day it was received. When a transfer arrives, change the status to "Paid" and fill in the payment date, even before issuing the receipt. If a parent paid for some of the lessons, update only the rows that were paid.

A monthly check. Once a month, before sending the breakdown to parents, filter the status column to "Unpaid" and go over the rows. Look for three things: names spelled differently, a group lesson where the rate does not match the number of participants, and a lesson that was cancelled and stayed in the table as a debt. Ten minutes of checking saves an awkward conversation with a parent.

A backup. If the file is saved on only one computer, a single fault wipes out a year of records. Saving in the cloud, or at least a weekly copy, is a must.

Where Excel breaks

Excel is an excellent tool, and many tutors have worked with it for years. But there are four places where it starts to cost money.

1. The phone. The easiest time to record a lesson is right when it ends, and at that moment you have a phone in your hand, not a computer. Editing a wide table on a small screen, with drop-down lists and formulas, is slow and error-prone. The familiar result: "I'll record it in the evening", and in the evening it no longer happens.

2. Several devices. A file on the computer at home, a copy on the phone, a version you emailed to yourself. Very quickly it is not clear which file is current. Saving in the cloud helps, but it is still easy to edit two copies in parallel.

3. Group lessons. Every group lesson requires several rows, the right rate for each one, and a correction of all of them if one student cancelled at the last minute and the lesson became private. This is exactly the point where one family's debt comes out wrong.

4. Forgetting and sending. The table knows how to calculate a debt, but it does not prepare a message for the parent. To send a breakdown you need to filter, copy dates, write and paste into WhatsApp. With ten students that is already half an hour of work every month.

If two of the four are familiar to you, it is probably time to look at an app. In the article an app for managing students for a private tutor: what it must have we gathered what to check before choosing.

Exporting to Excel from Teaching: the same columns, without typing

In Teaching you record a lesson from the phone: you choose an hour and mark who took part. One student is recorded as a private lesson at the private rate, and two or more as a group lesson at the group rate for each student. There are no columns to fill in and no formulas: each student's debt is the sum of their unpaid lessons, and it appears in the "Collection" screen. If a certain lesson had a special price, you edit it in the history, and the edited price is the one that counts. Every change is saved immediately, so there is no "old version" on another phone.

And what about Excel? In the "Collection" screen there is an "Export to Excel" button. It downloads a right-to-left file with all the lessons, in exactly the same columns as the template above: date, time, student, class, parent, phone, type, rate, status and payment date. The status column shows "paid" or "unpaid", and lessons that were cancelled stay in the file with the status "cancelled" (or "cancelled - credit" if they were already paid). Anyone who uses the packages of the Studio module also gets a separate "packages" tab.

That means you can keep using Excel for everything you like doing in it: pivot tables, an annual summary for the accountant, charts. Just without the typing. And if the subscription is ever in read-only mode, the export still works: your data always comes out.

Note: the export is a lesson and payment tracker, not an official income ledger and not receipts. What you need to manage for tax as a private tutor, we wrote about in exempt dealer for a private tutor, and in any case it is worth checking with an accountant.

For the full picture of the collection process, from setting a payment date to a reminder, see the full guide to collecting payment for private lessons.

Written by the Teaching team · Updated on 14 October 2026.