Fully Automatic Invoice in Excel: Free Download with GST Formula
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.
On this page
Key takeaways
- Excel can automate GST calculation, invoice numbering and customer auto-fill, but not GSTR-1 export, payment tracking or e-invoicing.
- Use ROUND(taxable value x rate / 200) for CGST and SGST, and / 100 for IGST, toggled by a supply-type dropdown.
- Sequential numbering is legally required; a Settings-sheet counter works without VBA, and a short macro makes it automatic.
- After GST 2.0, the rate dropdown should list only 0, 5, 18 and 40; the 12% and 28% slabs are abolished.
What 'Fully Automatic' Actually Means in Excel
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.
GST Auto-Calculation Formulas — Step by Step
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 pulled from the Products sheet. Column D: Quantity. Column E: Unit price (exclusive of GST). Column F: GST rate pulled from the Products sheet. Column G: Taxable value: =D10*E10.
CGST column (H): the template divides the GST rate by 200 (half of 100%) to get CGST.
SGST column (I): Same as CGST.
IGST column (J):.
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.
Auto Invoice Numbering in Excel — Two Approaches
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. 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 so it joins the financial-year prefix to the running number. This generates numbers like INV-2025-26-0042.
Customer Auto-Fill Using VLOOKUP and Dropdown
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: filled from the Customers sheet.
Address: filled from the Customers sheet.
City: filled from the Customers sheet.
State: filled from the Customers sheet.
State Code: filled from the Customers sheet.
Auto-determine supply type: Based on the customer's state code vs your state code (in Settings!B3), automatically set the supply type. This drives the CGST/SGST vs IGST calculation without manual selection.
Where Excel Reaches Its Limits for GST Invoicing
Our Honest Recommendation: When to Use Excel vs Software
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.
Frequently asked questions
- How do I make GST calculate automatically in Excel?
- To auto-calculate GST in Excel, set up a column for taxable value (quantity x unit price), a column for GST rate, and separate columns for CGST, SGST, and IGST. Use the formula =ROUND(taxable_value * gst_rate / 200, 2) for CGST and the same for SGST (both are half the total GST rate for intra-state). For inter-state, use =ROUND(taxable_value * gst_rate / 100, 2) for IGST. Use an IF statement based on a supply-type cell to toggle between intra and inter-state calculation automatically. This way, when you change the supply type dropdown, all tax values update instantly.
- Can I auto-number invoices in Excel without VBA?
- You can semi-automate invoice numbering without VBA by maintaining a counter cell in a Settings sheet that you manually increment after each invoice. Reference this counter in your invoice number formula: ='INV-'&TEXT(Settings!B1,'0000'). After creating each invoice, go to the Settings sheet and add 1 to the counter. This is not fully automatic but prevents duplicates since you control the counter centrally. For true automation — where the number increments when you click a button — you need a simple VBA macro as described in this guide. The macro is beginner-friendly and requires no programming knowledge.
- What GST rates should my Excel invoice template use in 2026?
- After GST 2.0 reform effective September 22, 2025, set up your Excel GST rate dropdown to show only four values: 0, 5, 18, and 40. Remove 12 and 28 — those slabs are abolished. The 5% rate covers essential goods, packaged food, medicines, and basic clothing. The 18% rate is the standard for most goods and services including consumer electronics, furniture, and professional services. The 40% rate applies only to tobacco, aerated drinks, luxury vehicles, and sin goods. Update your Products sheet with the new rates for all items that previously fell under 12% or 28%.
- Is an Excel invoice legally valid for GST in India?
- An Excel-generated invoice is legally valid for GST as long as it contains all mandatory fields under Rule 46 of the CGST Rules: supplier GSTIN, invoice number, date, buyer GSTIN (for B2B), place of supply, HSN/SAC code, description, quantity, unit price, GST rate, CGST/SGST/IGST amounts, and total value. The government does not mandate any specific software. However, for businesses above Rs 5 crore turnover, e-invoicing through the Invoice Registration Portal is mandatory — which Excel cannot support without API integration.
- What is the SpellNumber VBA function for Indian Rupees?
- SpellNumber is a popular open-source VBA function that converts a numeric amount into words in English. For Indian Rupees, you need a version that handles lakhs and crores correctly — not the US version that handles millions and billions. Search for 'SpellNumber Indian Rupees VBA' and you will find several free versions. Paste the function code into the Excel Visual Basic Editor (Alt+F11, Insert Module, paste code). Then use it in your invoice as =SpellNumber(B32) where B32 is your grand total cell. It outputs text like 'Rupees Eleven Thousand Eight Hundred Only.'
- How many invoices can I realistically manage in Excel before it becomes unmanageable?
- Most businesses find Excel invoice management workable up to about 100-150 invoices per year. Beyond that, searching old invoices, tracking unpaid bills, preparing GST summary data for filing, and managing customer credit limits becomes time-consuming and error-prone. The practical limit also depends on how many customers and products you have — 200 invoices for 5 regular customers is manageable, but 200 invoices for 80 different customers with 50 products each creates significant lookup and tracking complexity. If you spend more than 30 minutes per week on invoice administration in Excel, it is time to consider a dedicated billing tool.
