Build a GSTR-1 Summary Sheet in Excel

Last updated: 27 June 2026 · For the GST 2.0 rates (Nil / 5% / 18% / 40%).

To build a GSTR-1 summary in Excel, keep one row per invoice in a sales register, then use SUMIFS and a PivotTable to roll it up into the tables GSTR-1 needs: B2B (invoice-wise), B2C (rate-wise), and HSN summary (Table 12). Each summary block must tie back to the same register total, so a single grand total proves nothing was dropped before you file by the 11th.

Key takeaways

• One clean register, many summaries. Capture every sales invoice once with GSTIN, place of supply, taxable value and rate; build all GSTR-1 tables from it.
• B2B is invoice-wise, B2C (small) is rate-wise, and Table 12 (HSN) is summarised by HSN code and rate.
• SUMIFS rolls up taxable value, CGST, SGST and IGST by any condition (GSTIN, rate, state, B2B vs B2C).
• Place of supply decides CGST+SGST vs IGST — store the buyer's state code so totals split correctly.
• HSN reporting is mandatory: 4 digits if turnover ≤ ₹5 crore, 6 digits above; Table 12 now uses a dropdown on the portal.
• Reconcile every block to one grand total before filing — the portal will reject mismatched figures.

Fact box. GSTR-1 is the monthly return of outward supplies (sales), due by the 11th of the following month for monthly filers (or the 13th of the month after the quarter under QRMP). It feeds your buyers' GSTR-2B, so errors here block their Input Tax Credit. (Source: CBIC; GSTN return calendar.)

What columns does a GSTR-1 register need in Excel?

Start with a flat sales register — one invoice per row — holding every field the portal asks for. A reliable layout is:

Derive the tax heads with one IF on the state code (your state in $B$1): CGST =IF(POS=$B$1, Taxable*Rate/200, 0), SGST the same, IGST =IF(POS=$B$1, 0, Taxable*Rate/100).

How do I build the B2B table?

The B2B table is invoice-wise: every invoice to a registered buyer (a row with a GSTIN) is reported individually. Filter or copy all rows where GSTIN is not blank into the B2B block — the columns are already in invoice-wise form, so no aggregation is needed.

To check your B2B totals, use the complement approach: total the whole register and total B2C separately, then B2B = register total − B2C total. This is simpler than a SUMIFS with a non-blank GSTIN condition.

How do I build the B2C (rate-wise) summary?

B2C small supplies are reported rate-wise and state-wise, not invoice-by-invoice. Build a small grid of rates (5, 18, 40) down the side and use SUMIFS to total each:

Taxable  =SUMIFS(Reg!Taxable, Reg!GSTIN, "", Reg!Rate, A2, Reg!POS, B2)
IGST     =SUMIFS(Reg!IGST,    Reg!GSTIN, "", Reg!Rate, A2, Reg!POS, B2)

Here Reg!GSTIN, "" picks only the blank-GSTIN (B2C) rows, A2 is the rate and B2 the place-of-supply code. Add CGST and SGST the same way.

Fact box. Under GSTR-1, B2B supplies are reported invoice-wise while B2C (small) supplies are reported rate-wise, grouped by place of supply. Large inter-state B2C invoices above the notified value are reported separately (B2C Large). (Source: GSTN GSTR-1 format.)

How do I build the HSN summary (Table 12)?

Table 12 totals taxable value and tax by HSN code and rate. A PivotTable is the fastest route: rows = HSN, then Rate; values = Sum of Taxable, CGST, SGST, IGST and quantity. Or use SUMIFS against an HSN-and-rate grid.

HSN digits required depend on turnover:

On the portal, Table 12 now requires dropdown selection of HSN (no free typing) and splits into separate B2B and B2C tabs, so your Excel HSN totals must reconcile with the rest of GSTR-1. For B2C, reporting 4-digit HSN is optional below ₹5 crore turnover but mandatory at 6 digits above ₹5 crore — confirm the current advisory on tutorial.gst.gov.in before filing.

How do I reconcile the summary before filing?

Add a check row: the sum of B2B taxable + B2C taxable should equal the register's total taxable value, and the same for each tax head. Put =B2B_total + B2C_total - Register_total in a cell — it must read 0.

1. Total the full register (taxable, CGST, SGST, IGST).
2. Total each summary block (B2B, B2C, exports, credit notes).
3. Subtract — any non-zero means a row was missed or double-counted.
4. Fix in the register, not the summary, then refresh.

How Ankeshan helps: Ankeshan builds the B2B, B2C and HSN tables from your invoice register automatically and flags any block that doesn't tie to the grand total — inside Excel, before you upload. It's launching soon; join the waitlist.

Frequently asked questions

What is the due date for GSTR-1? The 11th of the following month for monthly filers, or the 13th of the month after the quarter for QRMP (turnover up to ₹5 crore) filers.

Can I file GSTR-1 from an Excel file? You prepare and reconcile in Excel, then upload via the GST portal's offline tool (which imports from Excel/CSV) or through a GSP. The portal validates the figures, so your Excel totals must match.

What is the difference between B2B and B2C in GSTR-1? B2B (sales to registered buyers, with a GSTIN) is reported invoice-wise; B2C (sales to consumers) is reported rate-wise and state-wise as a summary.

How many HSN digits do I need in GSTR-1? Four digits if your turnover is up to ₹5 crore, six digits above ₹5 crore. Table 12 now uses a dropdown rather than manual entry.

Why must my GSTR-1 summary reconcile to the register? Because every block (B2B, B2C, HSN) is built from the same invoices. If they don't add back to one grand total, you have either dropped or double-counted invoices, which the portal flags.

Sources

• GSTN: GSTR-1 format, tables and offline tool — gst.gov.in.
• CBIC: return due dates and QRMP scheme — cbic-gst.gov.in.
• GSTN advisory: Table 12 HSN reporting (dropdown, B2B/B2C split) — tutorial.gst.gov.in.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: GST in Excel — complete guide » · Fix GSTR-1 vs GSTR-3B mismatch » · HSN & SAC code finder » · Calculate CGST/SGST/IGST in Excel »