Build an automatic invoice in Excel with GST calculation formulas, auto invoice numbering, and customer data lookup. Includes VBA macros and a guide on when Excel reaches its limits.
When small business owners search for a 'fully automatic invoice in Excel,' they typically want three things: GST calculates automatically when they enter price and quantity, the invoice number increments on its own, and customer details fill in automatically when they select a customer name. This guide shows you how to build exactly that.
Fair warning upfront: Excel can get you 70-80% of the way to fully automatic invoicing. The remaining 20-30% — like syncing invoices with your GSTR-1, sending invoices via WhatsApp without exporting to PDF first, or tracking which invoices are paid — requires either very complex VBA code or a proper billing tool. We will show you both paths.
This guide assumes you have basic Excel knowledge — you know what a formula is and how to reference cells. You do not need to know VBA to implement most of what is covered here. The VBA macros for advanced features are provided as copy-paste ready snippets.
If you want to skip Excel entirely, the myBillPlease invoice generator is a free online tool that does everything described in this guide without any setup. But if you prefer Excel for offline use or for customization, read on.
The foundation of an automatic GST invoice is the tax calculation formula. Here is how to build it from scratch for the 2026 GST rate structure (0%, 5%, 18%, 40%).
Sheet structure: Name your invoice sheet 'Invoice' and create a separate sheet called 'Settings' where you store: company name, GSTIN, address, and state code. Another sheet called 'Customers' stores customer GSTIN, address, and state. A 'Products' sheet stores product names, HSN codes, and default GST rates.
The Supply Type cell: In cell B3 on the Invoice sheet, create a dropdown using Data Validation: Allow → List → Source → 'Intra-State,Inter-State'. This one cell controls whether CGST+SGST or IGST is calculated across the entire invoice.
Line item rows (rows 10-25):
Column B: Item description. Column C: HSN code (VLOOKUP from Products sheet: =IFERROR(VLOOKUP(B10, Products!$A:$C, 2, 0), "")). Column D: Quantity. Column E: Unit price (exclusive of GST). Column F: GST rate (VLOOKUP from Products sheet: =IFERROR(VLOOKUP(B10, Products!$A:$C, 3, 0), "")). Column G: Taxable value: =D10*E10.
CGST column (H): =IF(AND($B$3="Intra-State", G10>0), ROUND(G10*F10/200, 2), 0) — divides the GST rate by 200 (half of 100%) to get CGST.
SGST column (I): Same as CGST: =IF(AND($B$3="Intra-State", G10>0), ROUND(G10*F10/200, 2), 0)
IGST column (J): =IF(AND($B$3="Inter-State", G10>0), ROUND(G10*F10/100, 2), 0)
Row total (K): =G10+H10+I10+J10
Invoice totals (rows 27-32): Taxable total: =SUM(G10:G25). CGST total: =SUM(H10:H25). SGST total: =SUM(I10:I25). IGST total: =SUM(J10:J25). Grand total: =SUM(K10:K25).
Amount in words: Excel does not have a built-in currency-to-words function. You can use the open-source SpellNumber VBA function — paste it into a module and call it as =SpellNumber(K32). We include the full code in the downloadable template.
Sequential invoice numbering is legally required for GST. The CGST Act requires invoices to be numbered sequentially without gaps or duplicates. Manual numbering is error-prone — people forget to increment, duplicate, or accidentally reuse numbers.
Approach 1 — Settings sheet counter (simple, no VBA): In the Settings sheet, create a cell called 'Last Invoice Number' with an initial value (e.g., 100). On the Invoice sheet, the invoice number field references this cell: ="INV-"&TEXT(Settings!B1+1,"0000"). This shows the next number to be used. After you save and print the invoice, you manually update the Settings!B1 cell to the used number. Simple but requires discipline.
Approach 2 — VBA macro auto-increment (recommended): This approach automatically saves the invoice to a folder and increments the counter. Add this VBA code to a module in your workbook:
Sub SaveInvoice()
Dim newNum As Long
newNum = Sheets("Settings").Range("B1").Value + 1
Sheets("Settings").Range("B1").Value = newNum
Sheets("Invoice").Range("B5").Value = "INV-" & Format(newNum, "0000")
Dim savePath As String
savePath = "C:\Invoices\" & Sheets("Invoice").Range("B5").Value & ".pdf"
Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath
MsgBox "Invoice " & Sheets("Invoice").Range("B5").Value & " saved to " & savePath
End Sub
Assign this macro to a 'Save & Print' button on your invoice sheet. When clicked, it increments the number, saves a PDF, and shows a confirmation. The number is now permanently recorded.
Resetting at financial year start: Indian businesses reset invoice sequences at April 1. In your Settings sheet, add a 'FY Prefix' cell with value '2025-26'. Change the invoice number formula to: ="INV-"&Settings!B2&"-"&TEXT(Settings!B1,"0000"). This generates numbers like INV-2025-26-0042.
Typing customer GSTIN, address, and state code from scratch every invoice is tedious and error-prone. Here is how to build a customer auto-fill system in Excel.
Build the Customers sheet: Create columns: Customer Name (A), GSTIN (B), Address Line 1 (C), City (D), State (E), State Code (F), Email (G), Phone (H). Fill in your regular customers — one row per customer. Sort the list alphabetically by name for easier lookup.
Customer dropdown on Invoice sheet: Click the customer name cell (e.g., B8). Go to Data → Data Validation → List. In Source, enter: =Customers!$A:$A. Now you have a searchable dropdown of all customers.
Auto-populate GSTIN and address: When a customer is selected from the dropdown in B8, these cells auto-fill:
GSTIN: =IFERROR(VLOOKUP($B$8, Customers!$A:$F, 2, 0), "")
Address: =IFERROR(VLOOKUP($B$8, Customers!$A:$F, 3, 0), "")
City: =IFERROR(VLOOKUP($B$8, Customers!$A:$F, 4, 0), "")
State: =IFERROR(VLOOKUP($B$8, Customers!$A:$F, 5, 0), "")
State Code: =IFERROR(VLOOKUP($B$8, Customers!$A:$F, 6, 0), "")
Auto-determine supply type: Based on the customer's state code vs your state code (in Settings!B3), automatically set the supply type: =IF(VLOOKUP($B$8, Customers!$A:$F, 6, 0)=Settings!$B$3, "Intra-State", "Inter-State"). This drives the CGST/SGST vs IGST calculation without manual selection.
Real problems that Excel cannot solve without significant engineering effort
The GST portal accepts GSTR-1 data as a specific JSON format. Generating this from Excel requires complex VBA that most users cannot build or maintain. myBillPlease exports GSTR-1 JSON directly — upload to portal without re-entering data.
Find invoice 2025-26-0089 in your Excel files. How long does it take? With proper billing software, invoice history is searchable by customer, date, amount, or status in 2 seconds. Excel folders do not scale beyond 200 invoices.
Sending an Excel-generated invoice requires exporting to PDF, opening WhatsApp or email, attaching the file, and sending. Proper tools like myBillPlease send invoices via WhatsApp and email from the invoice screen in one click.
Which of your 50 outstanding invoices have been paid? Excel requires a separate tracking sheet. Billing software shows you paid, unpaid, and overdue invoices on a dashboard. Follow-up reminders are automated.
If two people need to create invoices simultaneously, Excel on a shared drive causes version conflicts and overwritten data. Cloud billing tools handle concurrent access natively — no shared file management needed.
Businesses above Rs 5 crore turnover must generate invoices via the IRP to get an IRN and QR code. Excel cannot do this — it requires direct API integration with the government portal. Proper billing software handles this automatically.
We built myBillPlease primarily for businesses that have outgrown Excel, but we are honest about when Excel still makes sense. If you issue fewer than 10 invoices per month, have only a few customers, and do not need GSTR-1 export, a well-built Excel template works fine.
But if you are creating 20+ invoices per month, have 50+ customers, need GSTR-1 data for filing, send invoices by email and WhatsApp regularly, or need to track outstanding payments — the Excel setup described in this guide will save you time for 3 months and then become a burden.
The myBillPlease invoice generator is free for the first 30 days with no invoice limit. It has every feature described in this guide — GST auto-calculation, auto invoice numbering, customer auto-fill, and GSTR-1 export — without any formulas or VBA. It also works on mobile, which Excel does not.
We have a free plan with 30 days full access. Start here — you can always go back to Excel if it does not fit your workflow.
myBillPlease does everything in this guide automatically — GST calculation, sequential numbering, customer lookup, and GSTR-1 export. Free for 30 days, no credit card.
Try Free Invoice Generator