Skip to content
Tutorials Center
My learning

Topics

Lesson 84 of 93 ยท Real-World Projects

Project: Invoice and Quote Template

Build a reusable Excel invoice and quote template: clean layout, price-list lookups, discount and tax formulas, numbering, print setup and protection.

An invoice template is one of the most useful things you can build in Excel, and a great exercise because it combines layout, lookups, careful arithmetic, printing and protection. The goal is a sheet where you type only a few things (customer, item codes, quantities) and everything else appears by itself, prints on one page and cannot be damaged by a stray click.

You will use XLOOKUP for the price list, IF and ROUND for discount and tax, a counter for the invoice number, Page Layout settings for printing and sheet protection for safety. Excel cannot run on this site, so the Excel steps are text, and the formulas run in a small JavaScript spreadsheet engine stand-in (cells, references, ranges, a few functions). A quote is the same sheet with a different title and a validity date instead of a due date.

One sheet, three zones

Inputs Code, quantity, customer.
Calculations Price, totals, tax.
Output A clean one-page print.

Only the inputs are unlocked.

1 / 2

Step 1: layout

Plan the page on paper first. A simple, professional layout:

  1. Header block (rows 1-3): your business name, invoice number, date and due date, customer name and address.
  2. Line items (a table of about 15 rows): Code, Description, Quantity, Unit price, Line total.
  3. Totals block (right-aligned under the line totals): Subtotal, Discount, Tax, Total due.
  4. Footer: payment details, thank-you note, terms.

Layout rules that keep it tidy:

  • Do not merge cells inside the line-item table; use Center Across Selection for titles instead, so sorting, copying and formulas keep working.
  • Format money columns with a currency or accounting number format (see Formatting Cells); put units in headers, not in the numbers.
  • Turn off gridlines on the View tab for a clean look, add borders only around the table and totals, and shade input cells light yellow so users know where to type.
  • Keep the price list on a second sheet (or to the right of the invoice, outside the print area). Make it an Excel Table named Prices so that adding a product extends the lookup range automatically (see Excel Tables).

Step 2: lookups from the price list

Each line has a code and a quantity as inputs. Description and unit price come from the price list, so a price changes in exactly one place:

TEXT
Description   =IF(A5="","",XLOOKUP(A5,Prices[Code],Prices[Description],"Check code"))
Unit price    =XLOOKUP(A5,Prices[Code],Prices[Price],0)
Line total    =IF(A5="",0,ROUND(C5*D5,2))

The not-found text "Check code" appears if someone mistypes a code, and the unit price falls back to 0, so the invoice never shows an error value but makes the problem obvious. Better still, add a data validation list to the code column pointing at Prices[Code] (see Data Validation and Dropdown Lists), so wrong codes cannot be entered at all.

Step 3: discount and tax

Keep rates in labeled input cells, never inside formulas, so you can change them without hunting through the sheet (see best practices).

TEXT
B11  Discount rate (for subtotals of 200 or more)   5%
B12  Tax rate                                       20%

E10  Subtotal   =SUM(E5:E8)
E11  Discount   =-ROUND(E10*IF(E10>=200,$B$11,0),2)
E12  Tax        =ROUND((E10+E11)*$B$12,2)
E13  Total due  =ROUND(E10+E11+E12,2)

Two details matter. First, tax is computed on the discounted amount: (E10+E11), because the discount is stored as a negative number. (Check how your country or company calculates it; some rules differ.) Second, round at the same step you would round on paper: each line and each total to two decimals, so the printed numbers add up exactly (see Percentages, Rounding and Number Precision).

Run it: an invoice that really calculates

This TryIt builds the invoice from the formulas above in the engine. It supports SUM, IF, ROUND, XLOOKUP, a basic TEXT (zero-padding only, for "0000"-style formats) and & for joining text. Then it prints the invoice as text and runs a second scenario with a mistyped code.

JS An invoice evaluated by the engine

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the second scenario, the subtotal is below 200, so no discount applies, and the bad code shows Check code with a unit price of zero. In a finished template, validation prevents that, but a visible failure is still better than a wrong total.

Step 4: invoice numbering

Invoice numbers must be unique and sequential. A few approaches, from simplest to most robust:

  • A counter cell in a "Settings" area: the next number, say 42. The invoice shows ="INV-"&TEXT(B1,"0000"), which produces INV-0042. After you save a copy for the customer, you increase the counter by hand.
  • Include the year: ="INV-"&YEAR(TODAY())&"-"&TEXT(B1,"000"). Careful: TODAY() changes every day, so a saved invoice would show a different year next January. Use a typed date (=B3 where B3 is the invoice date) instead of TODAY() for anything that must stay fixed.
  • A macro that copies the sheet as a PDF named after the number and adds 1 to the counter (see VBA Basics); it removes the "forgot to increment" risk.

For a business with legal numbering rules, keep a register of issued numbers (a separate sheet or accounting software) rather than trusting one file.

Step 5: print setup

Set up printing once, so every invoice comes out right:

  1. Page Layout > Print Area > Set Print Area for just the invoice block (not the price list).
  2. Page Layout > Orientation: Portrait. Size: your paper (A4 or Letter).
  3. Page Setup > Page tab > Fit to 1 page wide by 1 tall (or by blank tall, if you allow longer invoices).
  4. Margins tab: tick Center on page horizontally.
  5. Header/Footer: a footer with "Page &[Page] of &[Pages]" for multi-page invoices.
  6. Use View > Page Break Preview to see the page edges, then Print Preview (Ctrl+P) to check.

To save a PDF for the customer, use File > Save As > PDF (or Export), which captures only the print area.

Run it: scale to fit and protect the inputs

Fit-to-page just calculates a scale factor: the printable width divided by the sheet's content width, never above 100%. This stand-in shows that arithmetic, and a protection check where only the input cells accept edits.

JS Fit-to-page scale and input-only protection

Press Run (or Ctrl + Enter) to see the console output here.

No output.

(B12, the tax rate, is locked in this model: only you, as the template's owner, change it after unprotecting the sheet. If your users should set it per invoice, unlock it too.)

Step 6: protect the template

Select the input cells (customer, dates, item codes, quantities), open Format Cells > Protection and untick Locked. Then Review > Protect Sheet, tick Select unlocked cells only if you want to keep users from clicking on formulas, and add a password you will remember. Now people can type in the yellow cells and nothing else. You can also tick Hidden on formula cells so they do not show in the formula bar. Remember that this prevents accidents; it is not strong security (see Collaboration, Comments and Protecting Work).

When you are finished, Save As > Excel Template (.xltx). Opening a template creates a fresh copy, so the original never gets overwritten with one customer's data. Keep a quote version as a second sheet or file: same layout, "QUOTE" as the title, a "Valid until" date (=B3+30) and no payment due line.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the invoice store the discount as a negative number and compute tax on (E10+E11)?

    With the discount shown as a negative line, E10+E11 is the net amount, so tax applies to it and the total adds the three parts.
  2. 2. You use ="INV-"&YEAR(TODAY())&"-"&TEXT(B1,"000") in a saved invoice. What problem can appear?

    Volatile functions recalculate every time. A fixed invoice date typed in a cell keeps the number stable.
  3. 3. What is the role of the fourth argument in XLOOKUP(A5,Prices[Code],Prices[Description],"Check code")?

    The if_not_found argument returns your message instead of #N/A, so a mistyped code is visible and nothing breaks below it.
  4. 4. Which settings print a wide invoice on one page width without cutting columns?

    Fit to 1 page wide scales the print down by the needed factor, so columns are never split across pages. Merging cells would not help and causes other problems.
  5. 5. What does saving as .xltx give you?

    Double-clicking a template creates a new workbook based on it, so the master stays clean.

Summary

  • Plan the page: header, line items, totals and footer; keep inputs, calculations and output distinct.
  • Pull descriptions and prices from a Prices table with XLOOKUP, give a visible not-found message and validate item codes.
  • Keep rates in labeled cells; compute tax after discount and round at each step so printed figures add up.
  • Number invoices from a counter cell (TEXT(n,"0000")); avoid TODAY() in anything that must stay fixed.
  • Set a print area, fit to one page wide, center and preview; export to PDF to send.
  • Unlock only the input cells, protect the sheet, and save as an .xltx template.

Next: Project: Inventory and Reorder Tracker.