GST Invoice Register in Excel: Formulas and GSTIN Check

A GST invoice register in Excel is a table with one row per invoice: invoice number, date, customer, GSTIN, supply type, taxable value, GST rate, CGST, SGST, IGST, invoice total and payment status. Build it as an Excel table, fetch each customer’s GSTIN with XLOOKUP, compare the first two digits of the GSTIN (the state code) with yours to choose CGST plus SGST or IGST, and round each tax with ROUND to 2 decimals. Since 22 September 2025 most items fall in the 5% or 18% slab, but your accountant decides each item’s rate.

How to build a GST invoice register in Excel, step by step

Step 1: a Settings sheet. In B1 type your GSTIN (format the cell as Text first). In B2 type =LEFT(B1,2) to get your state code and name that cell OurState (Formulas, Define Name). List rates 0%, 5%, 18% and 40% in A6:A9 and name the range RateList. List Unpaid, Part paid and Paid and name it StatusList. Add any other rate your accountant confirms.

Step 2: a Customers table. Headers: Name, GSTIN, State Code, City, Phone. Press Ctrl+T and name the table Customers. Format State Code as Text so 09 does not become 9. For registered customers, State Code can be =LEFT([@GSTIN],2); for unregistered ones, type their state’s code.

Step 3: the Register table. Headers: Invoice No, Date, Customer, GSTIN, Supply Type, Taxable Value, GST Rate, CGST, SGST, IGST, Invoice Total, Status, Paid Date, Days Outstanding. Convert to a table named Register. Type these formulas in the first data row; the table copies them down:

• GSTIN: =XLOOKUP([@Customer],Customers[Name],Customers[GSTIN],"Not in list")
• Supply Type: =IF(XLOOKUP([@Customer],Customers[Name],Customers[State Code],"")=OurState,"Intra-state","Inter-state")
• CGST and SGST (same formula): =IF([@[Supply Type]]="Intra-state",ROUND([@[Taxable Value]]*[@[GST Rate]]/2,2),0)
• IGST: =IF([@[Supply Type]]="Inter-state",ROUND([@[Taxable Value]]*[@[GST Rate]],2),0)
• Invoice Total: =[@[Taxable Value]]+[@CGST]+[@SGST]+[@IGST]
• Days Outstanding: =IF([@Status]="Paid","",TODAY()-[@Date]), formatted as Number

On Excel 2019 or earlier, XLOOKUP is not available. Use =IFNA(INDEX(Customers[GSTIN],MATCH([@Customer],Customers[Name],0)),"Not in list") instead.

Build the total from taxable value plus each rounded tax. Rounding CGST and SGST separately can differ by a paisa from a separately rounded GST figure, so do not compute the total from a different number.

GSTIN check: validation formulas that stop typing errors

A GSTIN has 15 characters: a two-digit state code, the holder’s 10-character PAN, then three more characters including a check character. Use Data, then Data Validation, Custom:

• Blank or exactly 15 characters (in the Customers table, GSTIN in column B): =OR(B2="",LEN(B2)=15)
• No duplicate invoice numbers (column A): =COUNTIF($A:$A,A2)=1
• Customer, GST Rate and Status: List, with sources =CustomerList, =RateList and =StatusList (define CustomerList as =Customers[Name]).
• Date: between the first and last day of the financial year, such as 01-04-2026 and 31-03-2027.

A length check catches typing slips, not wrong numbers. To confirm that a GSTIN is genuine and active, use the “Search Taxpayer” facility on the official GST portal. Pasted data skips validation, so use Data Validation, then Circle Invalid Data after any paste.

Test it with two invoices before real use

Suppose your GSTIN starts with 09 (Uttar Pradesh).

1. INV-201, customer in Kanpur (state code 09), taxable value ₹10,000 at 18%. Expected: Intra-state, CGST 900.00, SGST 900.00, IGST 0, total 11,800.00.
2. INV-202, customer in Delhi (state code 07), taxable value ₹20,000 at 5%. Expected: Inter-state, IGST 1,000.00, total 21,000.00.

Then enter a customer not in the list: the GSTIN column should show “Not in list”. If all three behave, the logic works.

The state-code comparison is a practical first check. Legal place of supply depends on the transaction and can differ, for example for some services or goods shipped to a third party, so let your accountant decide unusual cases.

Monthly summary, overdue alerts and protection

• Pivot table: Insert, PivotTable from Register; Date in Rows (grouped by month), GST Rate in Columns, and Taxable Value, CGST, SGST and IGST in Values.
• One month by formula (first day of month in P1): =SUMIFS(Register[Taxable Value],Register[Date],">="&P1,Register[Date],"<="&EOMONTH(P1,0))
• Outstanding by customer (name in R2): =SUMIFS(Register[Invoice Total],Register[Customer],R2,Register[Status],"<>Paid")
• Overdue highlight: conditional formatting rule =AND($L2<>"Paid",$B2<TODAY()-30), with Status in L and Date in B.
• Protect: unlock input columns, protect the sheet allowing sorting and filtering, and save a blank copy as an Excel template for each financial year.

Frequently asked questions

How do I make a GST invoice register in Excel?

Create Settings, Customers and Register sheets, convert lists to Excel tables, fetch GSTIN with XLOOKUP, decide CGST plus SGST or IGST by comparing state codes, and round each tax to 2 decimals. Test with invoices whose answers you know before using it.

How do I check if a GSTIN is valid in Excel?

Excel can check the format: 15 characters, using =LEN(B2)=15 in data validation, and that the first two digits are a state code. Whether the GSTIN is genuine and active can only be confirmed on the GST portal’s Search Taxpayer facility.

What is the formula for CGST and SGST in Excel?

With taxable value in D2 and rate in C2, each of CGST and SGST is =ROUND(D2*C2/2,2) for a supply within the same state. For an inter-state supply, IGST is =ROUND(D2*C2,2).

What are the GST rates after September 2025?

From 22 September 2025, most goods and services fall under the 5% and 18% slabs, with a 40% rate for a narrow list and some items exempt or at special rates. Confirm each item’s rate with your accountant and keep rates as inputs in your sheet.

Quick quiz

Sources

• Central Goods and Services Tax Act, 2017 and Integrated Goods and Services Tax Act, 2017 (intra-state and inter-state supply)
• GST rate notifications effective 22 September 2025, following the 56th GST Council meeting
• GST portal (gst.gov.in): Search Taxpayer by GSTIN
• Microsoft Support: XLOOKUP, SUMIFS, ROUND and data validation documentation

Build this register and two more templates in the Excel and AI for Office Work certificate course.

This page is for education and general information, not legal advice for a specific case. Law as on 29 October 2026.