Invoice Tracker 2023 – Free Google Sheet Template
This Google Sheet is developed as a solution to keep track of the sales invoices and payments made by customers in one place. You can enter payments made by customers and let the template calculate outstanding balance amounts.
This template is designed for Google sheets, but if you are looking for an Excel template, please visit Invoice Tracker
Benefits of Invoice Tracker Template
- 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
Free Google Sheets
How to Use the Invoice Tracker Google Sheets Template
- Make a copy
- Enter Input Data
- View results in Dashboard
Step 1: Make a Copy of the sheet
Click on the link above and then make a copy of the sheet as shown in the screenshot below.
You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.
Step 2: Enter Input Data
DATA ENTRY
- Enter each invoice in the Invoice table beginning from row 15.
- 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 Background color 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.
Step 3: View results in 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 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.
14 Comments
Hello, if I enter an Invoice Number different from 1,2,3, etc., the Select column doesn’t work anymore, and therefore the Dashboard on top for Past Due Invoices stops working as well.
Is there a way to enter any Invoice Number format that is not necessarily numeric and still having the select column recognizing it?
Thanks
Thank you for showing interest in our template.
To have a alpha numeric invoice number you need to modify the number 2 to 3 in SUBTOTAL function on SELECTED column.
You can paste the below formula in cell J15 and duplicate to rest of the cell by selecting the rest of cells from J15 and pressing CTRL+D to duplicate the formula.
=IFERROR(SUBTOTAL(3, $A15), "")Best wishes.
Hi there! This is super helpful, my team loves it. Can you please help me understand the “SELECTED” column of the sheet and why it is there?
Thank you for showing interest in our template and sharing your valuable feedback.
The selected column is used for filters. When a gets row is filtered using the slicer, it will become 0 and only the selected row will have 1. For example, if you select paid in full status, the rows that have the status as paid in full will have 1, and other hidden rows will have 0. This impacts all the calculations.
We also have two premium templates similar to this template. Following are the links to the same for quick reference:
1. Invoice Manager:
https://indzara.com/product/small-business-excel-templates/invoice-manager-excel-template-for-small-business/
2. Small Business Finance Manager:
https://indzara.com/product/small-business-excel-templates/small-business-finance-manager-excel-template/
Best wishes.
i am interested
Thank you for showing interest in our template.
If you are looking for an edit access, please make a copy of the template to your Google Drive to gain edit access.
I also would like to inform you that, we have a premium version of the template with more functionality and advance reports. Following is the link to the same for quick reference:
Invoice Manager:
https://indzara.com/product/invoice-manager-google-sheet-template/
Small Business Finance Manager:
https://indzara.com/product/small-business-finance-manager-google-sheet-template/
Best wishes.
This is super useful! The only annoying bit is that even when I want to filter using the slicers I get a notification saying ‘You’re trying to edit part of this sheet that shouldn’t be changed accidentally. Edit anyway?’
Is there a way to remove that?
Thank you for showing interest in our template and sharing your valuable feedback.
The warning is shown because, we have protected the last 4 columns which has formulas from getting deleted accidently. To remove the protection, Goto Data ribbon -> Select Protect sheet and ranges -> Delete the 4 range that are protecting the last four columns (On h-over on the each protected range, you will be able see the range that is protected with Green Border in the sheet).
Best wishes.
Hello why is the invoice tracker can’t edit?
Thank you for showing interest in our template.
Requesting to click File -> Make a copy to create a copy of the template to your Google Drive to get edit access to the template.
Best wishes.
Hi what about for multiple installment payments? Is there a way to track due with multiple paying months?
Thank you for showing interest in our template.
Yes you can track due amount based on multiple payments. Requesting to check following video to implement multiple payment and bulk payment in invoice tracker template:
https://www.youtube.com/watch?v=LZUtpK34xFY
Best wishes.
Good day! How to change the currency ofnthe sheet?
Many thanks!
Thank you for using our template.
1. Select all the amount cells.
2. Goto Format in the menu bar.
3. Extend number.
4. Extend more format present at the last of the list.
5. Select more currency and choose the required currency format.
Best wishes.