For small businesses, keeping track of the sales invoices and payments made by customers is a critical task. Use this Invoice Tracker template to keep track of all your sales invoices in one place. You can enter payments made by customers and let the template calculate balance outstanding amounts. Never miss an unpaid invoice. Download this invoice tracker spreadsheet free using the link below.
FEATURES
- Track all invoices in one place
- Identify which invoices are past due and how late they are
- Calculate how much amount is outstanding
- Calculate expected payments in immediate future
- Easy to identify due amounts by Customer
- Simple and easy to use
REQUIREMENTS
Microsoft Excel 2013 (or later) for Windows
FREE DOWNLOAD
VIDEO DEMO
HOW TO USE THE TEMPLATE
DATA ENTRY
- Enter each invoice in the Invoice table beginning from row 15. (If you are new to Excel Tables, please read this article on data entry)
- Enter Invoice Number, Customer, Invoice Date, Due Date and Invoice Amount
- When Customer makes payment, enter it in Paid Amount column.
- Green colored columns have formulas. Do not edit.
Outstanding Amount: This is calculated as Invoice Amount – Paid Amount
Status: There are five possible values for Status.
- ‘ERROR”, when at least one of the fields Invoice Amount, Invoice Date, Due Date is left blank. Also, when Due Date < Invoice Date.
- PAID IN FULL: Outstanding Amount is 0
- CURRENT: If Outstanding Amount >0 and Due Date > Today (not due yet)
- DUE TODAY: If Outstanding Amount > 0 and Due Date = Today
- PAST DUE: If Outstanding Amount is 0 and Due Date = Today
- OVERPAID: If Outstanding Amount < 0 (i.e., Paid Amount is > Invoice Amount)
Note: Error rows are not used in calculation of metrics.
Rows with errors will be highlighted with a red border for your assistance.
Past Due Age: If the Status is ‘Past Due’, then this represents the aging bucket (1 – 30 Days, 31 – 60 Days, 61 – 90 Days, 91+ Days). Otherwise, it will be blank.
Selected: This will display 1 if the record (or row) is not filtered by the slicers.
VIEW DASHBOARD
The top of the Invoices sheet will present the summary in a dashboard as shown below. This will update instantly as you add more invoices to the table.
Let’s break it down in 3 sections.
Current Invoices:
Number of current (Due Date is in the future) invoices will be shown along with outstanding amount due.
Past Due Invoices:
Number of Past due (Due Date is in the Past ) invoices will be shown along with outstanding amount due. Accounts Receivable (A/R) aging breakdown is also provided.
Expecting Payments:
Payments that are expected in the next 7 days (including today) and next 30 days (including today) will be displayed.
Slicers (Filters)
These metrics can be easily filtered using the 3 Slicers provided.
The invoice table will be filtered as well. One can use this feature to easily target selective invoices. For example, click on Status = Past Due, to see only invoices that are past due.
Related Templates
- Create printable Sales Invoices: Sales Invoice Template (Free)
- More Small Business Templates
Additional Tips
How to set up a different currency?
Press Ctrl+G and choose CURRENCY_CELLS.
Press Ctrl+1 and select your preferred currency symbol.
How to add a new column?
Adding columns is very easy. Just type any field name in cell K14. Then, type values for each invoice in column K from K15 down. You can add any number of columns.
How to sorting invoices by Due Dates?
It’s easy to use the default sorting capabilities within Excel. For example, right click on the due dates column and choose Sort option.
You can use sorting on any of the fields.
How to track partial payments from customers?
Sometimes your customer may make an invoice payment in installments. For example, a customer pays $300 invoice in 2 payments $100 and $200. Or a customer makes a payment for two invoices together. Please watch this video tutorial below that explains how to address these scenarios.
How to calculate Due Dates automatically?
By default the template allows manual entry of due dates. If, in your small business, there is a standard term used such as Net 30 or Net 45, then we can automate this calculation. To create Due Date always as (Invoice Date + 30), just type =[@[INVOICE DATE]]+30 in cell D15.
Dear Sir,
Can we have similar kind of free template for accounts payable. That would be great for people just started tracking suppliers bills.
Thanks & regards
Thanks for your message.
We will try to develop a template for accounts payable soon.
Best wishes
sir i hve been using your free retail inventory management excel sheet… i am facing a problem in report i am not able to see the total inventory value…
Thanks for using our template.
Please ensure that none of the formulas or the links are broken. You may download the file again. In case it still does not work, please email the file with the list of issues to contact@indzara.com
Best wishes
This template is so helpful, thank you.
I’m having trouble with the status and past due age. The invoice date is correct and the due date is when the customer paid, which happened to be two months later and it says error under status. Why does it say error and how can I change it to be outstanding or late payment? Please help.
Thanks for using our template.
Please share your file with the list of issues to contact@indzara.com
Best wishes
Thanks a lot for the sheet. Meanwhile I added a column between PAID AMOUNT and OUTSTANDING AMOUNT. I dont seem to have any problem so far. Do you think there is going to be a problem in future?
Thanks for using our template.
You may add a column. However, what matters most is how columns are linked to each other. In case you are using it to calculate, you need to make sure that the links are updated.
Best wishes
hello
i will use it for track Payments
but what if i have more than 1 DUE DATE For the same customer and same invoice ?
Thanks for using our template.
This template is designed to have one due date for each invoice. A customer can have more than one invoice pending with different due dates.
Best wishes
Hello
We have launched the “Invoice Manager Excel Template For Small Business” with many added features.
Features in a premium version
Automatically applies bulk payments across open invoices for the customer
Supports partial payments, multiple payments, bulk payments and overpayments
Dashboard with revenue trend and top 10 customers
Unpaid invoices report with current and past due invoices
Print-ready Customer report showing all invoices of 1 customer at a time.
Please visit https://indzara.com/product/invoice-manager-excel-template/ for details.
Best wishes
Dear Sir,
The invoice won’t work in google spreadsheets, please solve this problem
Hello
Our templates are designed and tested in MS Excel. Some features will not work in the Google environment.
Thanks
AND WHY DO THIS DONT WORK PROPERLY ON MICROSOFT EXCEL 2010?
Thanks for using our template.
Our templates are tested on MS Excel 2010 and later editions.
Best wishes.
ITS REALLY GREAT!
BUT THERE IS FEW THINGS TO BE FIXED,
1) Please mention how to create “status, past over due, and customer in upper green portion?
2) THERE IS ONE MORE THING WHEN WE APPLY PIVOT TABLE EVERY THING WORK PROPERLY BUT WHEN WE WANT TO CHECK THE PAID AMOUNT, IT SHOWS NUMBER OF PAID INVOICES INSTEAD OF PAID AMOUNT,
REGARDS,
ZAHID SULTAN
Thanks for your feedback.
1. The STATUS column has the formula that calculates the Status value. The formula can be modified to change the values.
Similarly PAST DUE AGE column has the formula that calculates PAST DUE AGING buckets.
Then, we just type the Past Due Aging values in cells E5 to E8. If you modify the values in the formula, then values in the top green area should also be modified.
2. if you have created a pivot table, then please make sure that the measure is SUM Of Paid Amount and not COUNT of Paid Amount.
Best wishes.
I think I broke it….. I was stupid and clicked “save” when the weird code thing was in the “Outstanding Amount” Column and now all of my Outstanding amounts say “FALSE”. I’m freakin’ out over here lol. SEND HELP!!!
Sorry to hear. You can always download the original template again and migrate your input data. If you need any help, please email file and questions to contact@indzara.com
We will be glad to help.
Best wishes.