Build an Invoice Template in Excel or Google Sheets with Automatic Totals

You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.

Prompt

Prompt
Act as a spreadsheet specialist who builds billing templates for small businesses.

Your job: design a one-page invoice template with automatic totals that I can build in Excel or Google Sheets.

Before you start, ask me these questions in one message and wait for my answers:
1. Excel or Google Sheets?
2. What sales tax or VAT rate do I charge, and does it apply to every item?
3. Do I offer discounts: none, a percentage, or a fixed amount?
4. What are my payment terms (for example, Net 30) and currency?

Rules:
- Give every element an exact cell address and every formula exactly as I would type it, using only functions that work in my chosen app.
- Empty line-item rows must show blank, never 0 or an error like #VALUE!.
- Calculate the due date from the invoice date and payment terms with a formula.
- Include an invoice-numbering method that prevents duplicates, plus a check formula that flags a repeated number.
- Round line totals and tax to 2 decimals so my totals match what a client calculates.

Output format:
1. Layout table with columns: Cell | Label or content | Formula | Format
2. Invoice numbering: method, formula, and duplicate check
3. Protection and data validation steps (numbered)
4. How to reuse the template for the next invoice (3-5 steps)

End with one line listing your assumptions. Note that this is not tax advice and I should confirm tax rules with an accountant.

Example output

Example for: a freelance photographer using Google Sheets, 8% sales tax on all items, optional percentage discount, Net 30, USD.

1. Layout (sheet named Invoice)

CellLabel or contentFormulaFormat
A1Lena Ortiz PhotographynoneBold, 16 pt
D3 / E3Invoice #See section 2Text
D4 / E4Invoice dateType the dateDate
D5 / E5Due date=IF(E4="","",E4+30)Date
A10:D10Item, Qty, Unit price, Line totalnoneBold header
D11:D20Line total=IF(OR(B11="",C11=""),"",ROUND(B11*C11,2))Currency
C22 / D22Subtotal=SUM(D11:D20)Currency
C23 / D23Discount % (type in B23)=ROUND(D22*N(B23),2)Currency
C24 / D24Taxable amount=D22-D23Currency
C25 / D25Sales tax 8% (rate in B25)=ROUND(D24*B25,2)Currency
C26 / D26Total due=D24+D25Currency, bold

2. Invoice numbering

Method: Keep a second sheet named Log. Column A holds every invoice number you have sent. Format: INV-2025-001.

Next number (put in G3, then copy into E3 as a value):
="INV-"&TEXT(E4,"yyyy")&"-"&TEXT(COUNTIF(Log!A:A,"INV-"&TEXT(E4,"yyyy")&"-*")+1,"000")

Duplicate check (F3):
=IF(COUNTIF(Log!A:A,E3)>0,"DUPLICATE - change number","OK")

Add conditional formatting to F3: red fill when the text contains DUPLICATE.

3. Protection and data validation

  1. Data > Data validation on B11:B20: number greater than 0.
  2. Same on C11:C20: number greater than or equal to 0.
  3. B23: number between 0 and 1, displayed as a percentage.
  4. Data > Protect sheets and ranges: protect D5, D11:D26 and G3 so formulas cannot be overwritten.

4. Reuse for the next invoice

  1. Right-click the Invoice tab and choose Duplicate; rename it with the client name.
  2. Type the new invoice date in E4.
  3. Copy G3 and paste as value only (Ctrl+Shift+V) into E3; confirm F3 says OK.
  4. Clear A11:C20 and enter new items.
  5. After sending, add the number from E3 to the next empty row in Log column A.

Assumptions: tax applies after discount and to all items; 10 line rows are enough. This is not tax advice; confirm with an accountant whether discounts reduce your taxable amount locally.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for app, tax, discount and terms first means formulas match the user's setup instead of a generic guess.
  • Requiring exact cell addresses and blank-on-empty formulas produces a sheet that works on first build without error cells.
  • A logged numbering scheme plus a COUNTIF check stops duplicate invoice numbers, a common bookkeeping problem.

Use this when you bill clients and want a reusable invoice without paid software. Know your tax rate, discount policy and payment terms. Works in ChatGPT, Claude or Gemini.

Common edits

  • Ask for a fixed-amount discount line instead of a percentage.
  • Request a version with two tax rates, for example taxable services and tax-exempt items.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets