If you want to track customer invoices and payments in Excel, the first step is to set up an invoice tracker. Just start a spreadsheet with columns like invoice number, customer name, due date, and amount owed. From there, you can use Excel tools like formulas, data validation, and conditional formatting to stay organized and on top of your records.
In this tutorial, I’ll walk you through how to build your own invoice tracker. I’ve also included a free downloadable Excel template to help you get started even faster.
Thank you for downloading!
Featured Partner
Step 1: Add the column headers.
Open a new Excel spreadsheet file and then define the following column headers:
- Invoice #
- Date issued
- Client name
- Status
- Due date
- Invoice amount
- Payment amount
- Date paid
- Days overdue
- Amount outstanding
Your headers will look like this:
Sample headers for an invoice tracker in Excel
Step 2: Set up the formulas.
Next, you’ll just need to set up a few formulas to calculate things like how many days an invoice is overdue and how much you still expect to receive.
Days overdue
To calculate the number of overdue days, use a formula that subtracts the due date from the current date (today’s date).
The formula for the days overdue in our downloadable spreadsheet is as follows:
=IF(ISBLANK(E10),”-“,IF(D10=”Paid”,”-“,IF((TODAY()-E10)>0,TODAY()-E10,”-“)))
Excel’s IF formula can also help you determine if the invoice is not due yet or is already paid. The Amount Outstanding cell will show a value of “-” if the invoice is not yet due. Otherwise, it will show the invoice amount if it’s already due.
Formula explanation:
- The first condition checks whether cell E10 (Due Date) is blank, as represented by the ISBLANK function. If the condition is true, Excel will show “-” as a result. Otherwise, we go to the second condition.
- The second condition checks if cell D10 (Status) is equal to “Paid.” If the condition is true, Excel will show “-” as a result. Otherwise, we go to the third condition.
- The third condition checks if the difference between today’s date is greater than zero, as shown in TODAY()-E10. If the difference is a positive value, Excel will show the difference as Days Overdue. Otherwise, it will only show “-” as the result.
Outstanding balance
The Amount Outstanding (Column J) is simply the difference between the Invoice Amount (Column F) and Payment Amount (Column G). For example, the formula should be =F12-G12.
Subtotals
The SUBTOTAL function helps you compute the sum of filtered cells, which we’ll discuss in Step 3. In the bottom part of our Excel template, you’ll see the subtotals for Invoice Amount, Payment Amount, and Amount Outstanding. Even if you filter the cells, such as filtering all invoices from one client, the SUBTOTAL function will only compute the sum based on the filter you selected.
The formula is:
Formula explanation:
- The first argument in the SUBTOTAL function is the function number or function_num. There are 11 different functions available. But since we only want to get the sum, we place 9 in the function_num argument because it represents the sum.
-
- AVERAGE: Calculates the average of the numbers in the cells identified (range)
- COUNT: Counts the number of cells that contain numbers within the range
- COUNTA: Counts the number of cells that are not empty within the range (could include letters and characters)
- MAX: Returns the largest number in the range
- MIN: Returns the smallest number in the range
- PRODUCT: Multiplies the numbers in the range
- STDEV: Calculates the standard deviation of the numbers in the range
- STDEVP: Calculates the population standard deviation of the numbers in the range
- SUM: Adds the numbers in the range
- VAR: Calculates the variance of the numbers
- VARP: Calculates the population variance of the numbers in the range
Note: When rows are hidden and you only want to sum the visible cells, you can put “10” in front of the function code (9). However, since we only want to sum the visible cells in column F, our formula is =SUBTOTAL(109,F9:F18).
- The second part of the formula (F9:F18) is the reference or the range in which Excel will process based on function_num. Since we used function 9 to get a sum, Excel will compute the sum of the range referenced for us.
Step 3: Create filters.
To keep tabs on your invoices and payments more easily, I recommend adding filters to each column in Excel. This allows you to find specific records easily, identify outstanding invoices, and track payments. To do this, click on the filter icon.
Applying filters to headers in an Excel invoice tracker
The filter will apply to all your headers automatically. You can then use these filters when looking for specific invoices and transactions later on, as shown in the image below:
Filtering Items in the Spreadsheet Using the Filter Feature
Step 4: Enter the data.
Once you finalize your invoice tracker, you can start entering your data. With the template provided, you will need to fill in all the fields, except “Days Overdue” and “Outstanding,” which will populate based on the formulas we entered.
Here’s a sample filled invoice tracker:
Sample Excel invoice tracker with data
Step 5: Track invoices and payments.
Now, you can start tracking customer invoices and payments. Using the filters we’ve added, you can easily find specific invoices or transactions based on invoice number, customer name, or due date. If you click the dropdown button in the headers, you can filter certain items and show only the ones you want to see.
Tracking invoices and payments using the customer filter tool
Common issues with using Excel as an invoice tracker
Formula errors are one of the most common issues you may face when trying to figure out how to track invoices in Excel. Even a small mistake like a missing parenthesis or wrong cell reference can throw off calculations for days overdue, totals, or payment status. If the output produced is not as expected, be sure to review your formulas for errors.
Another frequent issue is manual data entry mistakes. Since Excel doesn’t have built-in validation for invoice tracking, it’s easy to enter duplicate invoice numbers, wrong dates, or incorrect payment amounts. These small errors can add up, especially if you’re managing multiple clients or projects.
To help avoid problems, consider doing these things:
Importance of tracking invoices and payments
Tracking invoices and payments is crucial for maintaining healthy cash flow and avoiding missed or delayed payments. Using Excel makes this process simple and efficient, helping you stay organized and in control. It ensures you always know who owes you, how much they owe, and when payments are due.
Key benefits of tracking invoices in Excel include the following:
- Identifying outstanding invoices quickly
- Monitoring payment history
- Reducing the risk of lost income or late fees
Integrating invoice tracking with accounting software
Integrating your Excel invoice tracker can be done with popular accounting software tools like QuickBooks, Xero, and FreshBooks. These platforms allow for CSV imports, letting you sync invoice data directly from your spreadsheet. Some of these platforms may also support live Excel integrations via plugins or APIs for real-time data syncing.
To get the most out of integration, here are some tips to improve oversight and accuracy:
Automation in invoice tracking
While Excel is a reliable tool for manual invoice tracking, there’s a growing shift toward automation in invoice management. Automation tools streamline the process by eliminating repetitive tasks, minimizing human error, maintaining accuracy, and speeding up payment cycles.
Transitioning from Excel to an automated solution, such as the best invoicing software for small businesses, makes sense when invoice volume increases or when you need better visibility and control. Automation is especially useful if your business manages recurring payments, deals with multiple clients, or requires detailed reporting. It also supports scalability without overwhelming your administrative staff.
Consider making the switch from Excel to an automated tool if you are
- Handling a high volume of monthly invoices
- Spending too much time on data entry and follow-ups
- Missing due dates or struggling with tracking payments
- Needing consolidated dashboards or audit trails for compliance
Frequently asked questions (FAQs)
Excel might be a good resource if you want flexibility in terms of data entry and a simple and affordable solution for tracking invoices and payments.
No, you can’t. However, you can use it to track overdue invoices and use the information to send automated email reminders or alerts.
Yes, you can use its built-in features like formulas, tables, and charts to generate invoice reports from your invoice tracker.
If you need more than just invoice tracking or have many invoices to maintain, then consider accounting software like QuickBooks Online.
