Spreadsheets

GST purchase register in Excel for tracking input bills

A GST purchase register in Excel for input bills: supplier, GSTIN, bill number, taxable value, tax split, paid status and checks for missing GSTIN.

Editorial illustration for GST purchase register in Excel for tracking input bills

A GST purchase register is a list with one row per supplier bill, recording the supplier, GSTIN, bill number, date, taxable value, tax split, total, whether the bill has reached you and whether it is paid. A few check columns flag a missing GSTIN, a duplicate bill number or a total that does not add up, so the records are tidy when your accountant asks.

What columns go in the register?

ColumnHeadingNotes
ABill dateAs printed on the bill
BSupplierName
CSupplier GSTINAs printed; blank if the bill has none
DBill no.Exactly as printed
ETaxable valueAmount before tax
FCGSTAs printed
GSGSTAs printed
HIGSTAs printed
ITotal=E2+F2+G2+H2
JBill received?Yes or No
KPaid?Paid, Part or Unpaid
LCheckFormula below

Copy the tax columns exactly as printed. A bill carries either CGST and SGST together or IGST, so for each row, two of the three tax columns will usually be filled. Do not work out the split yourself; take it from the bill.

How do the check formulas work?

Put the expected GSTIN length in a cell, say P1, after counting the characters in one genuine GSTIN on your bills. Then in L2:

  • =IF(C2="", "Missing GSTIN", IF(LEN(C2)<>$P$1, "GSTIN length differs", IF(COUNTIFS($B$2:$B$500, B2, $D$2:$D$500, D2)>1, "Possible duplicate", "OK")))

The order matters: the first problem found is the one shown. A length check cannot tell you that a GSTIN is genuine or active, so verify an important supplier's GSTIN through the official channel.

What does a few bills look like?

The amounts are made up and the tax figures are those printed on each bill, not a rate.

SupplierTaxableCGSTSGSTIGSTTotalCheck
Local stationer₹10,000₹900₹900₹0₹11,800OK
Courier agency₹7,000₹0₹0₹1,260₹8,260OK
Roadside repair₹2,500₹0₹0₹0₹2,500Missing GSTIN

The first total is 10,000 + 900 + 900 = ₹11,800, and the second is 7,000 + 1,260 = ₹8,260. The third has no tax and no GSTIN, and the Check column says so. That is not a verdict; it is a prompt to ask whether the supplier should have given a proper bill.

How do I use the register at month end?

  • Filter column K for Unpaid to see what is still to be paid.
  • Filter column J for No to chase suppliers for missing bills.
  • Filter column L for anything other than OK and fix or note each one.
  • Total taxable value for a month: =SUMIFS($E$2:$E$500, $A$2:$A$500, ">="&P2, $A$2:$A$500, "<="&EOMONTH(P2,0)), with P2 as the first of the month.
  • Total tax for a supplier: =SUMIFS($F$2:$F$500, $B$2:$B$500, "Local stationer") plus the same for G and H.

Does having a bill mean I can use the credit?

Not necessarily. Whether you can use input tax credit on a purchase depends on the nature of the purchase, your registration, the supplier's filings and rules that keep changing. The register helps by keeping every bill findable and flagged. Decisions about credit belong with your CA, and rules and limits change, so confirm the current position with them or the official source.

Where does an app fit?

The register records bills. SpendixAI looks at spending from statements: you can set GST rate, ITC-eligible and tax-deductible settings on custom categories, and reports show an estimate of the GST inside your spending, which you confirm against invoices and your CA. There are also export templates for Tally and Zoho Books.

Weekly routine

  1. Enter each new bill as it arrives, copying every number from the bill
  2. Mark Bill received as Yes only when you hold the bill
  3. Review the Check column
  4. Update Paid status when you pay and write the date in a note
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports