Invoice Tracker Template 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
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
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
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
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
Invoice Tracker Spreadsheet – Current Invoices and Invoice Amount

Past Due Invoices:

Past Due Invoices and A/R Aging Report
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
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
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 (Available for Excel and Google Sheets)

  • 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.
  • Invoice Manager
    Invoice Manager Excel Template For Small Business
    $35
  • Invoice Manager – Google Sheet Template
    $35

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
  • Small Business Finance Manager Excel Template
    Product on sale
    $40
  • Small Business Finance Manager – Google Sheet Template
    Product on sale
    $40

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.

160 Comments

  • Hi
    Hope you’re well.
    This is a great video.
    Could you please assist me on how to create those 3 slicer in this template by clicking on it data gets updated accordingly ?
    Regards

    Reply
  • Hi
    I have uploaded this to my Google Drive but it has “View Only” kindly grant me Edit Access.
    My mail id is – Raghu.thbs3223@gmail.com.
    And also I am interested in buying “Invoice Manager Excel Template For Small Business” if you give some discount.

    Regards
    Raghu S
    Ph – +91 8050731525

    Reply
    • Hello Raghu S,

      Please click on File > Make a copy. This will help you make a copy of the sheet and save it in your own google drive for your use.

      If you have any more questions please email us at support@indzara.com

      Reply
  • Good morning,

    I have entered all of the data for 17 invoices but the information returned only applies to 12 of the invoices,the first 5 are missing.
    I have checked all of the formulas and they are all O.K.so I am at a loss as to why it has happened.

    Reply
    • Thanks for using the template.
      Please check if the date format entered is correct. Please email file with question to support@indzara.com. We will review and respond with the solution.
      Best wishes.

      Reply
  • Hi,
    Thanks for your amazing Templet, I am very happy with your video and templet thanks a lot.
    Can I insert one more column for the payment date?

    Regards

    Reply
    • Thanks for using our template.

      The data in the template is in a table format. Hence, you can insert a column to the end of the table. That will not affect any calculations though.

      Best wishes

      Reply
      • Hello sir,

        Thanks for your reply. Sir actually am using your templet for vendor payments tacker so for some vendors, we have to deduct the TDS amount. when I am entering the only paid amount after deducting the TDS amount it showing the status that past due to the TDS amount. so kindly help throughout this.

        Thanks & Regards
        Kamala

        Reply
        • Thanks for your message.

          This template will accept the complete invoice amount. You can track the TDS in a different sheet and make sure the amount payable reflects as outstanding.

          Best wishes

          Reply
  • Hi, Thanks a lot; but what to do in case of multiple currencies for different clients or even the same client?
    how to show the total invoices amount in each currency, and same for each client when filtering?

    Reply
    • Hello

      As of now, this template can handle only a single currency. We will try to incorporate your recommendation in the future releases.

      Thanks

      Reply
  • Hi,

    Thank you for this wonderful template. I think there is an error on the Status, and the Past Due Age formula on the downloadable excel. No matter what combination of values in date I try to provide, the status is always current, and there is no information displayed on past due age.

    I’m using the 2019 Mac version of Excel

    Reply
    • You are welcome.
      It could be the date formats entered. I see that you have emailed the file. We will review and get back to you with a solution.
      Thanks & Best wishes.

      Reply
  • Everything is very open with a precise description of the issues.

    It was definitely informative. Your website is useful.
    Thank you for sharing!

    Reply
  • Hi,
    I found your webpage after a google search for invoice tracking manager in excel.
    You are doing an excellent job.
    Since I am interested for the Invoice Manager Excel Template For Small Bussines I downloaded the free version in order to play with it and see if it fits into me needs.
    What I noticed first, is that the Slices do not work. Actually they are not exist. They have been replaced by a shape with informative text.
    I have installed the Microsoft Excel 2015 and I am wondering if this feature has been restricted in the free version of this template
    Regards

    Reply
    • Thanks for your interest in our template.

      The slicers will work on this template. The minimum requirement is that you have an MS Excel 2013 or later edition. Please download the template again and populate some data.

      Best wishes

      Reply
  • Hi,
    I found your webpage after a google search for invoice tracking manager in excel.
    You are doing an excellent job.
    Since I am intersted for the Invoice Manager Excel Template For Small Bussiness I downloaded the free version in order to play with it and see if it fits into me needs.
    What I noticed first, is that the Slices do not work. Actually they are not exist. They have been replaced by a shape with informative text.
    I have installed the Microsoft Excel 2010 and I am wondering if this feature has been restricted in the free version of this template
    Regards

    Reply
    • Thanks for your interest in our template.

      This template is designed for Microsoft Excel 2013 (or later) for Windows.

      Best wishes

      Reply

Leave a Reply

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