Invoice Generator with Tax, Auto-Number & PDF Export

An invoice generator produces a clean bill from a list of items, with automatic tax and total, and a button to start a new invoice. You build it in Excel and use Copilot and ChatGPT for the tax math and VBA.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It lists line items, computes subtotal, tax, and grand total, and can create a fresh invoice number.
• Where it is used: Freelancers, shops, and service businesses that bill clients.
• What you learn: line totals, SUM, tax formulas, VBA for new invoices, and PDF export.

Features

• Auto line totals (qty × price)
• Subtotal, tax, and grand total
• Auto-increasing invoice number
• A "New Invoice" VBA button
• Save to PDF steps

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A tax rate (for example 10%)
• Developer tab enabled

Folder Structure

invoice-generator.xlsx
│
├── Sheet: Invoice   (header, line items, totals)
├── Sheet: Items     (catalog of products)
└── Module: InvMacros (VBA)

• Invoice: the bill the client sees.
• Items: optional product catalog.
• InvMacros: code for new invoice and numbering.

Step-by-Step Development

Step 1 – Line item totals

What are we building?

A table where each row shows qty × price.

Why do we need it?

The total per item feeds the subtotal.

How do we build it?

Name the table Inv. In the Amount column:

=[@Qty]*[@Price]

• What it does: multiplies qty by price for that row.
• Why needed: each line gets its own amount.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel table formula that multiplies Qty by Price for each row."

Step 2 – Subtotal, tax, and grand total

What are we building?

Cells that sum the amounts, add tax, and show the final total.

Why do we need it?

The client pays the grand total.

How do we build it?

=SUM(Inv[Amount])
=ROUND(Subtotal*0.1, 2)
=Subtotal+Tax

• First formula: adds all line amounts.
• Second formula: tax at 10%, rounded to 2 decimals.
• Third formula: subtotal plus tax.
• Why needed: gives the final amount due.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Add subtotal, 10% tax rounded to 2 decimals, and grand total below a table."

Step 3 – Auto invoice number

What are we building?

A number that increases each time you make a new invoice.

Why do we need it?

Every invoice needs a unique ID.

How do we build it?

Store the last number in a cell (say Settings!B1). The invoice header shows:

="INV-" & TEXT(Settings!B1, "0000")

• What it does: builds a label like INV-0012 from the stored number.
• Important line: TEXT(...,"0000") pads with zeros to four digits.
• Why needed: readable, sortable invoice numbers.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel formula that shows INV- and a 4-digit zero-padded number from cell B1."

Step 4 – VBA new invoice button

What are we building?

A button that clears the lines and bumps the invoice number.

Why do we need it?

One click starts the next bill.

How do we build it?

Sub NewInvoice()
    Sheets("Settings").Range("B1").Value = Sheets("Settings").Range("B1").Value + 1
    Sheets("Invoice").Range("B10:B20").ClearContents
    MsgBox "New invoice " & Sheets("Invoice").Range("B2").Value
End Sub

• What it does: adds 1 to the number, clears old line items, and confirms.
• Important line: Range("B1").Value + 1 increments the counter.
• Why needed: fast reset without manual edits.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write VBA that increases an invoice counter in Settings!B1 and clears line items in Invoice!B10:B20."

Complete Source Code

Invoice formulas

=[@Qty]*[@Price]
=SUM(Inv[Amount])
=ROUND(Subtotal*0.1, 2)
=Subtotal+Tax
="INV-" & TEXT(Settings!B1, "0000")

VBA new invoice

Sub NewInvoice()
    Sheets("Settings").Range("B1").Value = Sheets("Settings").Range("B1").Value + 1
    Sheets("Invoice").Range("B10:B20").ClearContents
    MsgBox "New invoice " & Sheets("Invoice").Range("B2").Value
End Sub

AI prompts

ChatGPT: "Table formula Qty times Price."
Copilot: "Subtotal, 10% tax rounded, grand total."
ChatGPT: "INV- with 4-digit zero-padded number."
ChatGPT: "VBA increment counter and clear line items."

Running the Project

1. Open invoice-generator.xlsx (save as .xlsm).
2. Enter line items; totals update.
3. Click New Invoice to start the next bill.
4. File > Save As > PDF to send it.
5. Expected output: a numbered invoice with correct totals.

Testing

Test Case 1

Input: Qty 2 Price 50; Qty 1 Price 100. Tax 10%. Expected Output: Subtotal 200, Tax 20, Total 220. Explanation: SUM adds lines; tax is 10% of subtotal.

Test Case 2

Input: Counter B1 = 7. Expected Output: Header shows "INV-0007". Explanation: TEXT pads to four digits.

Test Case 3

Input: Click New Invoice. Expected Output: Counter becomes 8, lines cleared, message shows INV-0008. Explanation: VBA increments and clears.

Common Errors

Tax wrong by a cent

Problem: Total off slightly. Reason: Tax not rounded. Solution: Use ROUND on the tax as shown.

Number not padded

Problem: Shows INV-8 not INV-0008. Reason: TEXT format missing. Solution: Wrap with TEXT(...,"0000").

PDF cuts off rows

Problem: Export misses items. Reason: Print area not set. Solution: Page Layout > Print Area > Set Print Area around the invoice.

Improvements

• Pull prices from an Items catalog with XLOOKUP
• Add multi-rate tax (state vs federal)
• Email the PDF with Power Automate
• Ask ChatGPT to write a thank-you note
• Add a discount column

Challenge Exercises

1. Add a discount column that reduces the subtotal before tax.
2. Use XLOOKUP to fill price automatically from the Items catalog.
3. Use ChatGPT to write VBA that saves each invoice as its own PDF.
4. Build a summary sheet of all invoice totals by month.
5. Connect ChatGPT to draft a payment reminder email from overdue invoices.