How to Create an Automatic Invoice Tracker in Excel | Step-by-Step Tutorial, by Excel Your Mind on YouTube. 19 min 50 s.
- Video length
- 19 min 50 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Open a blank workbook and set up a table with columns for invoice number, client, invoice date, due date, invoice amount, amount paid and balance due.
Format the header row in bold with a fill colour, then select the whole table and press Ctrl+T to turn it into an Excel table so new rows extend formulas.
In the balance due column, enter a formula that subtracts amount paid from invoice amount, for example =E2-F2, and fill it down the column.
Add a status column and build a nested IF formula that returns Paid when the balance is zero, Partially Paid when some money is received, and Open otherwise.
Extend the status formula with a check on the due date so any unpaid invoice past its due date shows Overdue instead of Open.
Add a days overdue column using a formula like =MAX(0,TODAY()-D2) so each late invoice shows how many days it is past due.
Build summary cards above the table with SUM formulas for total invoiced, total paid and total outstanding, plus a COUNTIF for invoices needing attention.
Select the status column and add conditional formatting rules: red fill for Overdue, amber for due soon, green for Paid.
Add a rule on the balance column that flags negative values so accidental overpayments stand out.
Turn on filters for the table header, then insert a bar or column chart comparing amount paid against balance due for each invoice.
Tips and common mistakes
Use absolute references or table structured references so formulas keep working when you add new invoice rows.
Test the status formula with a few sample rows before relying on it, including a fully paid invoice and one past its due date.
Keep the invoice amount and amount paid in separate columns so partial payments are easy to record.
Save a copy of the finished tracker as a template so you can start each month with a clean sheet.
Questions
Do I need any add-ins or macros?
No. The tracker uses built-in functions like IF, SUM, COUNTIF and TODAY plus conditional formatting, so it works in standard Excel.
Why does my status show Overdue when the invoice is paid?
The due date check probably runs before the paid check. Order the IF formula so a zero balance returns Paid first.
Can I use this in Google Sheets?
Most formulas carry over, but conditional formatting rules and table behaviour differ, so you may need to rebuild those parts.
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.
