A GST invoice tracker in Excel is a register with one row per invoice: number, date, customer, GSTIN, type of supply, taxable value, CGST, SGST, IGST, total, payment status and received amount. A small summary tab then shows the tax collected for a month and which invoices are still unpaid. This is a record-keeping sheet; your CA remains the person who confirms rates and returns.
What columns does the register need?
- A: Invoice number, running and unique
- B: Invoice date
- C: Customer
- D: Customer GSTIN, left blank for unregistered customers
- E: Supply - Same state or Other state, from a dropdown
- F: Taxable value
- G: CGST
- H: SGST
- I: IGST
- J: Invoice total
- K: Status - Unpaid, Part or Paid
- L: Date received
- M: Amount received
Put the GST rate in a Settings tab, cell B1. Use it as an input, not hard-coded in formulas, and confirm the right rate for your service with your CA. If different invoices carry different rates, add a Rate column and point the formulas at it.
Which formulas fill the tax columns?
- CGST in G2: =IF(E2="Same state", ROUND(F2*Settings!$B$1/2, 2), 0)
- SGST in H2: =IF(E2="Same state", ROUND(F2*Settings!$B$1/2, 2), 0)
- IGST in I2: =IF(E2="Other state", ROUND(F2*Settings!$B$1, 2), 0)
- Total in J2: =F2+G2+H2+I2
- Status in K2: =IF(M2=0, "Unpaid", IF(M2<J2, "Part", "Paid"))
A same-state sale splits the tax into equal CGST and SGST halves, and an other-state sale carries the whole amount as IGST. Which one applies depends on the place of supply, so check it against the rules for your kind of service.
A worked example with sample invoices
Using 18 percent purely as a sample input, say three invoices go out in one month.
| Invoice | Supply | Taxable | CGST | SGST | IGST | Total | Received |
|---|---|---|---|---|---|---|---|
| INV-001 | Same state | ₹10,000 | ₹900 | ₹900 | ₹0 | ₹11,800 | ₹11,800 |
| INV-002 | Other state | ₹25,000 | ₹0 | ₹0 | ₹4,500 | ₹29,500 | ₹15,000 |
| INV-003 | Same state | ₹8,000 | ₹720 | ₹720 | ₹0 | ₹9,440 | ₹0 |
The tax collected is 900 + 900 + 4,500 + 720 + 720 = ₹7,740, which equals 18% of the ₹43,000 taxable value. The invoice totals add to ₹50,740 and ₹26,800 has come in, so ₹23,940 is outstanding. INV-002 shows Part and INV-003 shows Unpaid.
How do I build the monthly summary?
On a Summary tab, put the first day of the month in B1, for example 1-Nov-2026. Then:
- Tax collected: =SUMIFS(Invoices!$G$2:$G$500, Invoices!$B$2:$B$500, ">="&B1, Invoices!$B$2:$B$500, "<="&EOMONTH(B1,0)) plus the same formula for columns H and I
- Money outstanding: =SUM(Invoices!$J$2:$J$500)-SUM(Invoices!$M$2:$M$500)
- Invoices pending: =COUNTIF(Invoices!$K$2:$K$500, "Unpaid")+COUNTIF(Invoices!$K$2:$K$500, "Part")
Roughly thirty invoices a month is easily handled in 500 rows for a year or more. Freeze the header row and keep invoice numbers in strict order, because gaps in numbering are the first thing a reviewer notices.
How do I chase unpaid invoices?
Add an Age column with =IF(K2="Paid", "", TODAY()-B2) to count the days since each unpaid or part-paid invoice was issued. Then add conditional formatting that turns the row amber after the payment terms you agreed with the customer, say 15 days, and red after 30. Sort by that column on the first working day of each week and send a polite reminder for the oldest. A part-paid invoice deserves the same treatment as an unpaid one, since the balance is still money you are owed.
If an invoice is cancelled or corrected, do not delete its row. Mark it clearly in a Notes column and enter the replacement as a new invoice, so the numbering history stays honest and your CA can see what happened.
Checklist when an invoice goes out
- Enter it the same day, with the correct customer GSTIN if they have one.
- Confirm the supply type, then check the tax columns calculated.
- Follow up on any invoice marked Unpaid after the agreed days.
- Record the received date and amount as soon as the money shows in the bank.
For the purchase side, SpendixAI lets categories carry a GST rate and its reports estimate the GST inside your spending. Those are estimates to check against the actual invoices with your CA, and SpendixAI does not issue invoices or file returns.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


