📌 Stage 2 · The Invoice Register · Invoicing & AR · The Invoice Register · 40–50 min

Thirteen columns that make every later step automatic

Learning goals

  • Walk the register column by column and separate typed facts from computed columns
  • Apply invoice numbering discipline: sequential, never reused, duplicates caught by formula
  • Record customer PO numbers and descriptions the way accounts-payable departments need them

Concepts

Anatomy of the register

The Invoices sheet is one row per invoice, oldest first, in columns B through N. Seven columns are typed facts: Invoice #, Date, Customer Code, PO #, Description, and Amount - the who, when, what, and how much of the bill. Six columns are formulas (pale green): Customer, Due Date, Paid Amount, Open Balance, Status, Days Overdue, and the Check column you will build in a moment.

The discipline is simple: type the facts once, on the day the invoice is raised, and let the formulas do everything after that. The register currently holds 13 invoices, INV-1082 through INV-1119, totaling $54,350.00 - five raised in September, the rest carried in from earlier months.

💡 Tips:

  • Register the invoice the day it is raised. An invoice that lives in your email outbox collects nothing.
  • Amounts are always positive; refunds and credits are a separate conversation (Lesson 8).

Numbering discipline

US small-business invoicing runs on a strict series: a prefix (INV-), a number, and nothing else. Numbers move forward, gaps are fine - INV-1082 jumps to INV-1094 because drafts in between were voided before sending - but a number is never reused and never backdated. Clients' AP systems key on the invoice number; a duplicate makes two invoices look like one and gets both stuck.

The gaps teach something real: a voided invoice keeps its number and simply never exists in the register. What must never happen is two live rows sharing a number, and the Check column polices exactly that.

=IF(COUNTIF($B$5:$B$1004,$B5)>1,"DUPLICATE","OK")

COUNTIF counts how many times this row's invoice number ($B5) appears in the whole number column ($B$5:$B$1004). One occurrence means OK; two or more means DUPLICATE - and because the formula lives on every row, both offenders light up.

PO numbers and descriptions

Many customers - Pinewood Legal Group LLC sends PO-3120, PO-3188 - will not pay an invoice that lacks the PO number their buyer issued. It is the match key inside their AP system, and an invoice without it goes to the bottom of the pile. Type it in the PO # column exactly as the customer wrote it.

Descriptions deserve the same respect, because they are read months later by people who were not in the meeting. 'Website redesign - phase 2' survives; 'project work' does not. Put the phase, the month, or the deliverable in the line.

One more convention: amounts exclude sales tax. US sales-tax rules vary by state and by service type - confirm the treatment for your services with your state Department of Revenue before invoicing, and add tax lines only if your state requires them.

💡 Tips:

  • No PO yet? Ask for one before starting work - it is also evidence the buyer actually approved the spend.
  • In Excel 365 you could format the register as a Table for auto-extending formulas; this course sticks to plain ranges so every Excel version since 2010 behaves identically.

Practice

Open on Invoices. Column N (Check) is blank in row 5 - you will type the duplicate-number guard yourself, then break it on purpose.

  1. Read the register — Scan the 13 rows: typed columns B through H (with formula column E in between), then the computed Due Date through Status columns. Confirm the numbering gaps and that every row carries a PO number.
  2. Type the check formula — Cell N5 is blank. Type =IF(COUNTIF($B$5:$B$1004,$B5)>1,"DUPLICATE","OK") and press Enter. Fill the formula down through N17 if your exercise lets you copy, or compare against the ready rows below - the logic is identical on every row.
  3. Break it on purpose — In cell B18 (the first empty row), type INV-1082 and press Enter. N18 shows DUPLICATE - and so does N5, because the duplicate guard caught both. This is the register protecting itself.
  4. Clean up — Delete your B18 entry (or click Reset Data) so all 13 rows show OK again. Leave N5 filled - it is correct now.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the COUNTIF duplicate check into N5 and saw OK on all 13 rows
  • ☐ Watched a second INV-1082 flip both rows to DUPLICATE, then cleaned it up
  • ☐ Confirmed every invoice carries a customer PO number and a description that names the deliverable
  • ☐ Noted the baseline: 13 invoices INV-1082 - INV-1119 totaling $54,350.00, with five raised in September 2026