If you run a small business, you already know that chasing invoices, remembering who’s paid, and keeping your accountant happy can eat up hours you don’t have. In this tutorial – Part 1 of a 4-part bookkeeping series from DCP Web Designers – you’ll learn how to build a simple but powerful Excel invoice tracker from scratch, no expensive accounting software required.
This spreadsheet system has been used in a real business for over 20 years to track hundreds of invoices a year, and it can do the same for you.
Why Track Invoices in a Spreadsheet?
Before you touch a single cell, it’s worth understanding why this matters. A well-maintained invoice tracker lets you instantly answer:
- Which invoices have been paid, and which haven’t?
- How did each client pay (bank transfer, check, cash)?
- How much have you invoiced in total, and how much is still outstanding?
- What work did you actually bill for, and when?
Handing this spreadsheet to your accountant at year-end, instead of a shoebox of paper invoices saves them time, which saves you money on accounting fees. It also means no client ever “forgets” to pay, because you always know exactly who owes you what.
Step 1: Set Up Your Spreadsheet and Column Headers
00 - Invoice Tracker - 26.07.2026
The “00” prefix keeps your master spreadsheet at the top of the folder, and adding the date helps you track versions over time. Rename the sheet tab to match the year (e.g., “Invoice Tracker 2026”) – you’ll create a fresh tab or file for each new year.
In row 1, add the following column headers:
- Invoice Date
- Invoice No
- Date Paid
- Payment Type
- Reference
- Description
- Company Name
- Location
- Cost
- Paid
Bold the header row and double-click between column borders to auto-fit the width so everything is readable.
Step 2: Format Your Columns Correctly
Formatting each column properly now saves headaches later:
- Invoice Date & Date Paid → Format as Date (select the format matching your country, e.g., DD/MM/YYYY in the UK).
- Invoice No, Payment Type, Reference, Description, Company Name, Location → Leave as General.
- Cost → Format as Currency with two decimal places, matching your local currency.
This ensures Excel treats dates as dates and costs as currency, which keeps your totals and sorting accurate.
Step 3: Enter Your Invoice Data
Now start entering real (or example) invoice data row by row:
- Invoice number convention: Use something consistent and easy to reference, like DCP20190001 – a company acronym, the year, and a four-digit sequence. This supports up to 9,999 invoices per year.
- Date shortcuts: Click the small square in the bottom-right corner of a cell and drag down to auto-fill sequential dates, or copy/paste a date down when multiple invoices go out the same day.
- AutoComplete: Start typing a company name you’ve used before, and Excel will suggest it automatically – just press Enter to accept it. This keeps company names consistent across your sheet.
- Descriptions: Keep them short – “Logo Design x3,” “Website Design,” “SEO 1 Day Work” – just enough detail to jog your memory later. Detailed specs belong in the invoice/email you send the client, not the tracker.
- Reference numbers with leading zeros: If a payment slip or reference starts with zeros (e.g., 00001), format that cell as Text first, or Excel will strip the leading zeros and treat it as a number.
Step 4: Add Colour-coded Payment Tracking
This is the heart of the system. As soon as you send an invoice, highlight that row blue – this is your visual signal for “unpaid.” When the client pays:
- Enter the Date Paid.
- Enter the Payment Type (bank transfer, check, or cash – bank transfer is recommended, since it’s typically free and easy to trace).
- Enter the Reference – ideally copied directly from your online banking statement so it matches exactly.
- Change Paid to “Yes.”
- Recolour the row green.
Now a quick glance at the spreadsheet tells you everything: green = paid, blue = still owed. No need to reread every line – your eyes go straight to the blue rows that need chasing up.
Step 5: Track Deposits and Partial Payments
For new clients or large projects, it’s smart to invoice a 50% deposit up front before starting work, then bill the remaining 50% on completion. To track this cleanly:
- Duplicate the relevant row underneath your main data.
- Split the total cost in half for the deposit and final invoice lines.
- Label them clearly (“Deposit Invoice” and “Final Invoice”).
- Track each stage’s payment status separately using the same blue/green system.
This is also the point where the video notes that VAT calculations get added in Part 4 of the series for VAT-registered businesses – this tutorial keeps things simple first.
Step 6: Use a Consistent File-Naming and Backup Convention
To avoid ever losing your data:
- Store your working file in a proper folder (Documents, not the Desktop).
- Use version numbers in the filename, e.g., 00 Invoice Tracker v1.
- Keep an “00 Archive” subfolder, and copy the file there periodically before making major edits.
If something ever goes wrong – a crash, a bad edit, a power cut – you always have a recent backup to fall back on.
Step 7: Use AutoFilters and AutoSum to Analyse Your Data
Once your data is in, Excel’s built-in tools do the heavy lifting:
- AutoFilter: Select your header row, then go to Data → Filter. Now you can filter by “Paid” (Yes/No), by company, by payment type, or combinations of these – instantly seeing, for example, every unpaid invoice, or every invoice for one specific client.
- AutoSum: Click into a blank cell below your Cost column and use AutoSum to instantly calculate:
- Total invoiced (all rows)
- Total paid (filtered to “Yes”)
- Total outstanding (filtered to “No”)
You can combine filters with AutoSum to answer very specific questions, like “How much has Company X paid me this year?” or “What’s outstanding from everyone except Company Y?”
Step 8: Clean Up the Look
Select the whole sheet and apply All Borders so every cell is clearly delineated – this makes the spreadsheet much easier to read at a glance, especially once it grows to hundreds of rows.
Final Thoughts
By the end of this tutorial, you’ll have a fully functional invoice tracker that can scale from a handful of invoices to several hundred (or thousand) per year – all colour-coded, filterable, and instantly summarisable.
This single habit – updating the sheet weekly and colour-coding as you go – is what keeps your books “audit-ready” year-round and keeps your accountant’s bill down, since you’re doing a chunk of the reconciliation work yourself.
