Indzara

Invoice Tracker Template 2024 in Excel – Free

For small businesses, keeping track of the sales invoices and payments made by customers is a critical task. Use this Indzara 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 using the link below.

Benefits

  • 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 track invoices in Excel?

DATA ENTRY

Invoice Data Entered in Invoice Tracker Excel Template
  • 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.
Calculated Columns in Invoice Tracker

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.

Error Validations – Invoice Tracker – Missing Due Date

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.

Invoice Tracker Template – Summary Metrics – Dashboard View

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.

Invoice Tracker Spreadsheet – Current Invoices and Invoice Amount

Past Due Invoices:

Past Due Invoices and A/R Aging Report

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:

Expecting Payments in Next 7 and Next 30 days

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.

Slicers allow filtering of Table and calculated metrics

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.


Recommended Premium Templates

Invoice Manager Pro (Available for Excel)

  • Print customizable invoices for your small business
  • Manage invoices and payments in one file using a simple data entry framework.
  • Partial Payments, Payments across multiple invoices and extra payments are handled.
  • Dashboard showing trend of invoice revenue and top customers with revenue and balance.
  • Unpaid Invoices Report shows all current and past due invoices with invoice aging.
  • Print a customer report showing all or past due invoices.
Thumbnail
Slicers allow filtering of Table and calculated metrics

Small Business Finance Manager (Available for Excel and Google Sheets)

  • View the total balances with all your accounts in one place in Dashboard
  • Generate automated Profit & Loss Statement (or income statement) for your business
  • Easily view the money flow in and out monthly into your accounts in Cash Flow Report
  • Make smarter business decisions by knowing valuable business insights about income and expense trends in Business Insights dashboard
  • See a list of all unpaid invoices and bills in Unpaid Report
  • Generate Customer and Supplier reports
  • Supports 14 types of financial transactions including bulk payments, refunds, credits and transfers

Additional Tips

How to set up a different currency?

Press Ctrl+G and choose CURRENCY_CELLS.

Go To Currency Cells
Go To Currency Cells

Press Ctrl+1 and select your preferred currency symbol.

Choose Currency Symbol as preferred
Choose Currency Symbol as preferred

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.

Sort Invoices by Due Dates
Sort Invoices by Due Dates

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.

171 Comments

  • How would i add a field that shows the total due to include current and past due amounts?

    Reply
    • Thank you for showing interest in our template.

      You need to write a sum formula to sum the outstanding amount column as shown below in any cell:
      =SUM(T_INV[OUTSTANDING AMOUNT])

      Best wishes.

      Reply
  • Hello

    When trying to set up formula for Due Date =( then I highlight Invoice Date) +60 enter
    i get an error message about Circular Reference and date 1/0/1900 shows int he row.

    I’ve tried googling and cannot seem to resolve it

    Thank you
    Milana

    Reply
    • Thank you for showing interest in our template.

      I tried adding the formulas as =[@[INVOICE DATE]]+60 in due date column and it is working fine without circular reference error.

      In excel all dates are numbers and starts from 01-01-1900 which is equivalent to number 1. Hence when you add 60 days when invoice date cell is empty, it will add 60 days from 01-01-1900. However, if it is showing 01-0-1900 it refers to 0. It could have caused due to circular reference error.

      Best wishes.

      Reply
  • Hello,
    Is there a way I can add a new slicer and for this slicer when selected to update the figures in the Dashboard at the top?

    Reply
    • Thank you for showing interest in our template.

      You just need to click anywhere inside the invoice table and goto “table design” ribbon at the top to insert a new slicer.

      Best wishes.

      Reply
  • Hello,

    I downloaded the template and started using it. It is amazing how easy it seems now to follow up on invoices. Thank you so much.

    I have a question about the due dates, it used to show me due date 1-30 days and 31-60 days (in the past due age column) But now it only shows 1-30 days even when I have invoices past due 60 days. I’m not sure what I did here. Could you pls advise about this?

    Thanks again 🙂

    Reply
    • Thank you for using our template and sharing your valuable feedback. You are welcome

      Please share your sheet to support@indzara.com to check further on your concern.

      Best wishes.

      Reply
  • Hello
    Thank you for this free version of excel invoice tracker, I just downloaded it and added some invoice. I really like it makes things easier.
    my question is I have so many invoice and I added so far 106 invoice but when I add more invoices it wont calculate or add to the total balance on the top. can you advice please ? thanks again

    Reply
    • Thank you for using our template and sharing your valuable feedback.

      It might be caused, when the entered invoice is outside the Invoice table. Requesting to share your sheet to support@indzara.com to check further.

      Best wishes.

      Reply
  • Hello & thank you for your tutorial. It was so helpful. I just have one question.. can you tell me how to enter my filters in the slicers… I literally cant figure that out. I got everything else down except that.

    Reply
    • Thank you for sharing your valuable feedback and you are welcome.

      Requesting to share some screenshot of your concern to support@indzara.com to check further.

      Best wishes.

      Reply
  • Can I check if my outstanding amount is $0, why do I sees the status as Past Due or Overpaid instead of Paid in Full ?

    Reply
    • Thank you for using our template.

      As mentioned in email, since you have multiplied the total with 1.07 without rounding off to two digit. The due amount is not exactly 0 and it has values in 3rd or 4th decimal.

      Best wishes.

      Reply
  • Hi,
    Very helpful info, thanks for sharing, do you have a tutorial on how to make this invoice tracking spreadsheet step by step? I notice that you have a free download and how to use it instruction. can you teach me how to make them?

    Reply
    • Thank you for showing interest in our template.

      Currently, we do not have a tutorial for Invoice Tracker template. Following are our other tutorial videos, which can help you understand the formulas used:
      https://indzara.com/excel-training/

      Best wishes.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *