Best Invoice Tracker Spreadsheet for Small Business (Easy Setup), by SimStudioSheets on YouTube. 6 min 22 s.
- Video length
- 6 min 22 s
- Time to do it
- 30 minutes
- Difficulty
- Easy
- Steps
- 9
What you need
Steps
Open your spreadsheet app and create a new file. Add a Start tab with your business name, address, invoice number prefix and payment terms so the other tabs can pull these details.
Create a Client List tab. Add columns for client ID, name, email, phone and address, then fill in one row per client.
Add a Products and Services tab. List each item with a name, description, unit price and tax rate so you can pick them when building an invoice.
Make an Invoice tab with fields for client, invoice number, date, due date and line items. Use a lookup formula to pull the client details and prices from your other tabs.
Add rows for products, then a subtotal, tax and discount line. Let formulas calculate the total so you never add it up by hand.
Set up a print area and add a Print or Export button. Check that the invoice number, client name and totals fill in correctly before you send it.
Create a Payments tab with columns for invoice number, payment date, amount and method. Enter each payment as it arrives.
Add a balance column that subtracts payments from the invoice total. Use conditional formatting to flag invoices that are unpaid or past their due date.
Build a dashboard tab with totals for invoiced, paid and outstanding amounts. Add a client summary and a yearly overview so you can see your cash flow at a glance.
Tips and common mistakes
Save a copy of the blank tracker as your template so you can start each month clean.
Use a unique invoice number for every invoice and never reuse one, even if you cancel it.
Check your formulas on a test invoice before you send real ones to clients.
Back up the file to cloud storage so a lost laptop does not lose your payment records.
Questions
Can I use this in Google Sheets instead of Excel?
Yes. The same tabs and formulas work in Google Sheets, though some menu names and print settings differ slightly.
Do I need to know formulas to build this?
Basic lookups and sums help, but you can copy the formulas from a template and only change the cell references.
How do I handle a partial payment?
Record the amount received in the Payments tab and let the balance column show what is still owed on that invoice.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
