GST Monthly Summary (Excel)
List your sales invoices and purchase bills in two simple sheets and pick a month: the Summary sheet totals taxable value and tax by GST rate, separates B2B (customer has a GSTIN) from B2C sales, adds up eligible input tax credit and shows the tax left to pay. It is the quick check accountants do before preparing GSTR-1 and GSTR-3B.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
Summary
| Month | November 2026 | 01-11-2026 | |||
| Outward supplies (sales) by GST rate | |||||
| GST rate | Taxable value | IGST | CGST | SGST | Total tax |
| 0% | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 |
| 5% | ₹ 12,800.00 | ₹ 0.00 | ₹ 320.00 | ₹ 320.00 | ₹ 640.00 |
| 12% | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 |
| 18% | ₹ 60,500.00 | ₹ 3,330.00 | ₹ 3,780.00 | ₹ 3,780.00 | ₹ 10,890.00 |
| 28% | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 |
| 40% | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 |
| Total | ₹ 73,300.00 | ₹ 3,330.00 | ₹ 4,100.00 | ₹ 4,100.00 | ₹ 11,530.00 |
| Sales by type of customer | |||||
| Type | Taxable value | IGST | CGST | SGST | Total tax |
| B2B (buyer has GSTIN) | ₹ 70,100.00 | ₹ 3,330.00 | ₹ 4,020.00 | ₹ 4,020.00 | ₹ 11,370.00 |
| B2C (no GSTIN) | ₹ 3,200.00 | ₹ 0.00 | ₹ 80.00 | ₹ 80.00 | ₹ 160.00 |
| Input tax credit (eligible purchases) | |||||
| Taxable value | IGST | CGST | SGST | Total ITC | |
| Eligible ITC | ₹ 37,000.00 | ₹ 350.00 | ₹ 2,700.00 | ₹ 2,700.00 | ₹ 5,750.00 |
| Tax position (estimate) | |||||
| IGST | CGST | SGST | Total | ||
| Output tax | ₹ 3,330.00 | ₹ 4,100.00 | ₹ 4,100.00 | ₹ 11,530.00 | |
| Less: eligible ITC | ₹ 350.00 | ₹ 2,700.00 | ₹ 2,700.00 | ₹ 5,750.00 | |
| Net (output − ITC) | ₹ 2,980.00 | ₹ 1,400.00 | ₹ 1,400.00 | ₹ 5,780.00 |
What's inside
- Sales sheet: date, invoice number, customer, GSTIN, state, taxable value and GST rate; IGST, CGST and SGST calculate themselves
- Purchases sheet: the same for supplier bills, with an "ITC eligible" Yes/No column
- Summary sheet: month selector, tax by rate (0, 5, 12, 18, 28 and 40 percent), B2B and B2C totals, output tax, eligible ITC and net tax
- 500 ready rows in each list, sample rows to show how it works
How to use it
- Open the Settings sheet and pick your state. Sales to the same state get CGST + SGST, others get IGST.
- Delete the sample rows on the Sales and Purchases sheets (select the yellow cells and press Delete).
- Type or paste each sales invoice on the Sales sheet: date, number, customer, GSTIN (leave empty for unregistered buyers), customer state, taxable value and GST rate.
- Do the same for purchase bills on the Purchases sheet and mark whether the input tax credit is eligible.
- On the Summary sheet, type the first day of the month you want (for example 01-04-2026). All totals change to that month.
- Compare the totals with your GST return figures before you file.
Tips
- Paste values only (right-click › Paste Special › Values) when copying from billing software so the grey formula columns stay intact.
- The net tax is output tax minus eligible ITC in total. The GST portal applies credit in a fixed order (IGST first, then CGST and SGST), so the cash you pay under each head can differ; use the portal's figures for payment.
- From 22 September 2025 most goods moved to the 5 and 18 percent slabs with a 40 percent rate for a few items; older invoices may still show 12 or 28 percent, so both are kept in the summary.
Questions and answers
Is this a replacement for GSTR-1 or GSTR-3B?
No. It is a working sheet to check your numbers. Returns are filed on the GST portal, which has its own rules and tables.
What counts as B2B here?
Any sales row that has a customer GSTIN. Rows without a GSTIN are counted as B2C.
Can I keep a whole year in one file?
Yes. Each sheet has 500 ready rows; the month selector picks out one month at a time. Insert more rows above the last row if you need them and copy the grey formulas down.
More in the Accountants & GST toolkit
GST Invoice Template (Excel)Free GST tax invoice template for Excel. Pick a customer and items; it fills HSN, rates, CGST+SGST or IGST and the amoun…Excel templateClient Dues & Ageing (Excel)Free Excel receivables tracker: list bills and receipts, see each client's balance by age (not due, 1-30, 31-60, 61-90, …Excel templateBank Reconciliation Statement (Excel)Free Excel bank reconciliation statement: start from the cash book balance, add and subtract uncleared cheques, bank cha…Excel templateOnline GST Invoice MakerMake a GST tax invoice online in a minute: CGST/SGST or IGST by state, HSN, amount in words. Print to PDF or download Ex…Online tool
Also useful
Stock Register (Excel)Free Excel stock register for shops: items with opening stock, daily stock in and out, closing stock, stock value and a …Excel templateDaily Cash Book (Excel)Free Excel daily cash book: receipts and payments with a running balance, a day-wise summary for the month and totals by…Excel templateStudent Attendance Register (Excel)Free monthly student attendance register for Excel: mark P, A or L for up to 60 students, Sundays shaded, totals and att…Excel template