Templates: Attendance, Marks & GST Invoice

Three sheets turn up in almost every Indian school, office and shop: an attendance register, a marks sheet and a GST invoice. Building them yourself is the best practice there is, because each one combines skills from earlier lessons: COUNTIF, IF, IFS, RANK.EQ, data validation, conditional formatting and careful rounding.

In this lesson you will build all three, cell by cell. Every template is small enough to type in a few minutes and is laid out so it can grow to a full class or a long invoice.

Template 1: Attendance register

Ms Iyer marks attendance for the week of 2 to 7 November 2026 (a six-day week). The dates go across row 1 and the students down column B:

Dates. Type =DATE(2026,11,2) in C1 and =C1+1 in D1, then fill right to H1. Give C1:H1 the custom format dd ddd to show "02 Mon". For a full month, fill right to the last date and use the Sunday rule from Conditional Formatting to shade holidays.

Entries. Select C2:H5 and add a drop-down list with Data > Data Validation > List, Source P,A,L. Nobody can now type "p", "Pr" or "present".

Counts. In I2:L2, then fill down:

=COUNTIF(C2:H2, "P")
=COUNTIF(C2:H2, "A")
=COUNTIF(C2:H2, "L")
=I2/COUNTA(C2:H2)

Aarav: 5  1  0  83.3%
Priya: 6  0  0  100.0%
Kabir: 2  3  1  33.3%
Meera: 5  0  1  83.3%

COUNTA(C2:H2) counts the days that have been marked, so the percentage is correct mid-month too. Format column L as Percentage with one decimal. Here leave is counted as "not present"; follow your own institution's rule.

Warnings. Many colleges require 75% attendance. Select A2:L5 and add a formula rule =$L2<75% with a light red fill, so Kabir's whole row stands out. A second rule on C2:H5, Cell Value equal to "A", colours every absence.

Template 2: Marks sheet with result and rank

A Class 9 sheet with five subjects, each out of 100. The pass mark of 33 and the grade boundaries below are example rules; use your board's or school's own.

Protect the marks with Data Validation > Whole number between 0 and 100 on C2:G6. Then, in row 2, filled down:

=SUM(C2:G2)
=H2/500
=IF(COUNTIF(C2:G2, "<33")=0, "Pass", "Fail")
=IF(J2="Fail", "Needs support", IFS(I2>=0.9,"A", I2>=0.75,"B", I2>=0.6,"C", TRUE,"D"))
=RANK.EQ(H2, $H$2:$H$6)

Aarav: 364  72.8%  Pass  C              2
Priya: 456  91.2%  Pass  A              1
Kabir: 205  41.0%  Fail  Needs support  5
Meera: 262  52.4%  Fail  Needs support  4
Rohan: 310  62.0%  Pass  C              3

The result checks every subject: COUNTIF(C2:G2, "<33") counts subjects below the pass mark, and only zero means Pass. Meera's total is higher than Kabir's, but both failed one subject. RANK.EQ gives 1 to the highest total; tied totals share a rank. The range $H$2:$H$6 is locked so it does not move when filled down.

Two more useful rows under the table: the class average of each subject with =AVERAGE(C2:C6) in C8, and the subject topper in C9:

=INDEX($B$2:$B$6, MATCH(MAX(C2:C6), C2:C6, 0))

Filled right, row 9 shows Priya as the topper in every subject, which is correct for this data. Add a fail-mark rule (=C2<33, red text) on C2:G6 so the problem subjects are easy to see.

Template 3: GST tax invoice

This simplified invoice shows how the tax is calculated. A real tax invoice must carry all the details required under GST rules (such as HSN or SAC codes, and buyer details for registered buyers), so have your format checked by a chartered accountant.

The header. The supply type decides which taxes apply. When the seller's state and the place of supply are the same (an intra-state supply), the tax is split equally into CGST and SGST. When they differ (inter-state), the whole tax is IGST.

Make B6 and B7 drop-down lists of state names so spellings always match. A GSTIN has 15 characters, and its first two digits are the state code (06 for Haryana), so a Text length equal to 15 validation rule on the GSTIN cell catches typing slips.

The items. Headings in row 10, items in rows 11 to 13:

Row 11 formulas, filled down to row 13:

=C11*D11
=IF($B$8="Intra-state", ROUND(E11*F11/2, 2), 0)
=G11
=IF($B$8="Inter-state", ROUND(E11*F11, 2), 0)
=E11+G11+H11+I11

These go in E11, G11, H11, I11 and J11. Each tax is rounded to paise on its own line, so the printed amounts always add up. Then the totals in row 14 (=SUM(E11:E13) and so on across to J14), a round-off and the grand total:

Change the place of supply in B7 to Delhi and the invoice switches by itself: CGST and SGST become 0, IGST becomes 2032.5, and the amount payable stays ₹15,383, because the total tax is the same; only its split changes.

Finish with the Indian number format on all amount columns, a print area of A1:J16 on one A4 page, and protection on every formula cell, as in Protecting & Printing. Save it as a template with File > Save As > Excel Template (.xltx), so each new invoice starts as a fresh copy.

Common mistakes

• Typing tax amounts by hand. One wrong digit and the invoice does not add up. Let formulas calculate every tax.
• Rounding only the grand total. Round each tax line to paise, then round the payable amount separately as a visible round-off.
• Free-typed state names. "Haryana" and "Haryana " do not match, so an intra-state sale becomes inter-state. Use drop-down lists.
• Pass or fail from the total alone. A good total can hide a failed subject. Check each subject with COUNTIF.
• Treating example rates as real. Check every GST rate and every exam rule with an official source.

Key points

• The attendance register uses a P/A/L drop-down, COUNTIF for counts and COUNTA for days marked.
• The marks sheet combines SUM, a COUNTIF pass check, IFS grades and RANK.EQ with a locked range.
• On a GST invoice, intra-state supplies split the tax into CGST and SGST; inter-state supplies use IGST.
• Round each tax line with ROUND(..., 2) and show a separate round-off to the nearest rupee.
• Drop-down lists, validation and protection keep templates safe to reuse; save them as .xltx.

=IF(AND(J2="Pass", I2>=0.75), "Distinction", "")
=COUNTIF(J2:J6, "Pass")
=INDEX(B2:B6, MATCH(MIN(H2:H6), H2:H6, 0))

M3 (Priya): Distinction  (all other rows are empty)
3
Kabir