Tuition Centre Management System in Excel: Groups, Monthly Fees, Test Marks, Progress Card and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: tuition-centre-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Learners, Fees, Tests, Progress card, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Shraddha Tuition Centre, Nashik, as of 17 October 2026 (made-up data). Groups: M10 (Maths, Std 10, ₹1,800 a month), S10 (Science, Std 10, ₹1,800), E09 (English, Std 9, ₹1,200) and M09 (Maths, Std 9, ₹1,500).

12 learners have paid ₹73,080 so far. For grade letters from marks, see IFS grades.

2. Build it sheet by sheet

Step 1. Setup: groups and fees

1. In B3 type the as-of date and in B4 the centre name.
2. In A7:D10 type Group code, Group, Tutor, Monthly fee. Short codes like M10 make every other sheet easier to type.
3. List payment modes in F7:F8.

Step 2. Learners: months due and balance

1. Headers in row 4: Learner ID, Name, Group, Joined (month), Discount %, Monthly fee, Months due, Fee due, Paid, Balance, Average test %, Rank.
2. F5 fee from the group =XLOOKUP(C5,Setup!$A$7:$A$10,Setup!$D$7:$D$10,0).
3. G5 months due, counting the joining month: =(YEAR(Setup!$B$3)-YEAR(D5))*12+MONTH(Setup!$B$3)-MONTH(D5)+1 (date functions). Joined June, as-of October = 5 months.
4. H5 =ROUND(G5*F5*(1-E5),0), I5 paid with SUMIFS on Fees, J5 balance =H5-I5.

Step 3. Fees and Tests: the entry sheets

1. Fees: Date, Learner ID, Name, Amount, Mode, For month. C5 looks up the name.
2. Tests: Date, Test, Learner ID, Group, Marks, Out of, %, Key. G5 =E5/F5 keeps tests of different totals comparable.
3. H5 builds a key: =C5&"-"&COUNTIF($C$5:C5,C5), so T03's tests become T03-1, T03-2 and so on (joining text).

Step 4. Average and rank

1. Back on Learners, K5 =AVERAGEIFS(Tests!$G$5:$G$500,Tests!$C$5:$C$500,A5) (AVERAGEIFS).
2. L5 =RANK(K5,$K$5:$K$16) (RANK). Colour K5:K16 red below 60% with conditional formatting.

Step 5. Progress card

1. Type a learner ID in B4. B5 shows the name, D5 the group name through a nested XLOOKUP.
2. Rows 8 to 13 use the key: B8 =XLOOKUP($B$4&"-"&A8,Tests!$H$5:$H$500,Tests!$B$5:$B$500,"") returns test 1's name; the same key returns the date, marks and %.
3. B15 average, B16 rank as "4 of 12", B17 fee balance. Print it for the guardian meeting (print area).

Step 6. Report and dashboard

1. Report rows 5 to 8, one per group: learners (COUNTIF), fee due, paid, balance (SUMIFS) and average test % (AVERAGEIFS on Tests by group).
2. Dashboard cards: learners, collected, balance, learners with balance, average test %, top learner =XLOOKUP(1,Learners!$L$5:$L$200,Learners!$B$5:$B$200) and learners below 60%.
3. Chart 1 at A8: average test % by group. Chart 2 at A25: fee balance by group.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Forgetting the +1 in months due. A learner who joined in October owes October. Without +1 the sheet says 0 months.
2. Averaging marks, not %. A 20-mark and a 50-mark test cannot be averaged as marks. Average the % column.
3. Typing the progress card by hand. It goes stale the day another test is added. Use the key and XLOOKUP.
4. Using names as the key. Two learners named Zoya break every lookup. Use the learner ID.
5. Ranking with blanks. A learner with no tests gives an error in AVERAGEIFS. Wrap it in IFERROR or log a test first.

5. More real examples

Example 1: Coaching for competitive exams. Add a Mock test type and a separate average for mocks with AVERAGEIFS on two conditions.

Example 2: Home tutor with 6 learners. Drop the group report; keep Learners, Fees and the progress card. Months due works the same.

Example 3: Quarterly fees. Change months due to quarters: =INT(G5/3)+1 and the fee to a quarterly amount.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate months between two dates in Excel?

Use =(YEAR(end)-YEAR(start))*12+MONTH(end)-MONTH(start)+1 to count both the first and the current month.

How do I rank learners by average marks?

Work out the average with AVERAGEIFS and use =RANK(K5,$K$5:$K$16); the highest average gets rank 1.

How can a progress card pick up all tests of one learner?

Number each learner's tests with a key like =C5&"-"&COUNTIF($C$5:C5,C5) and look up ID-1, ID-2 and so on.

Can I show fee balance on the progress card?

Yes, B17 looks it up from Learners. Remove it if you prefer to talk about fees separately.

How do I add a new group?

Add a row in Setup A7:D10 (extend the range) and a row on Report; drop-downs and lookups pick it up.

7. Quick quiz

Ten questions on this page. Sign in to save your score.