How to Easily Track Overdue Payments in Excel
Ever stared at a spreadsheet not sure what payments are overdue and which ones are not, you aren’t the only one.
Regardless of whether you’re managing customer invoices, staff salaries or school fees, Excel can be a superpower if you know how to leverage it.
Here’s a simple guide to make tracking overdue payments easy!
Subscribe to my free eNewsletter for more regular tips and shortcuts… click here.
Step 1 – Start with a Simple Table Layout
Create columns for items such as:
- Client Name, Invoice Number, Due Date, Amount, Payment status (Paid or blank for unpaid)
- Make the headings bold so that they stand out as the column headings.
- Make sure there are no blank rows between the column headings and the data or any blank lines in the list of data as you add information.
NOTE: Of course, you can also import data in from accounting programs such as Xero, Reckon, Quickbooks or MYOB.
Want to see me do this in a demo?
Watch the video below
Step 2 – Highlight Overdue Payments visually using Excel’s Conditional Formatting Feature
- Select the Due Date column excluding the heading
- Go to Home Tab > Click on Conditional Formatting in the Styles Grouping
- Choose New Rule in the bottom section
- Choose New Rule in the bottom section
- Under Select a Rule Type, choose Use a formula to determine which cells to format
- Type in the following formula =AND(C2<TODAY(),E2=””)
Before we go any further, let’s explain the formula we have created
First, AND tells Excel we have two things to “check” in cell C2
First, check if the date in C2 is prior to today (TODAY() is Excel’s code for today’s date, whatever date that is)
If the date in C2 is prior to today, then the formula looks to see if E2, where the Status displays is empty. If it is, it means the invoice is unpaid.
Now back to the conditional formatting
- Click the Format button
- From the Fill tab, choose a colour that will make entries meeting the conditions stand out
- Click OK to close the Format Cells box
- Click OK again to close the New Formatting Rule box
Invoices with a due date of less than today without the word Paid in the status column will be highlighted.
Step 3 – Identifying how many days overdue invoices are
- Create a column beside Payment Status with the heading Days Overdue
- Click in the first cell below the heading – this should be the first invoice in the list
- Type in the following formula =IF(E2=””,TODAY()-C2,0)
- Press Enter
Let’s explain the formula we have created
The IF function contains three arguments or elements Excel will evaluate.
A logical test that can be proved to be either true or false
What Excel should do if the cell tested proves to be true
What Excel should do if the cell tested is false.
In this case the logical test is cell E2, the payment status empty, meaning the invoice is unpaid.
Then the IF function tells Excel what to do if it is true, and that is, it will take today’s date and subtract the cue date found in C2
Then the IF function tells Excel what to do if it is false, that is, the payment status is Paid, it will place a 0 in the cell indicating this is not overdue.
This formula shows how many days a payment is overdue which makes it easy for follow ups!
For more Microsoft 365 business tips, get my eNewsletter … click here.
Step 4 – Format and Filter the List
Although you don’t have to, you might want to make your list look a bit nicer.
To do this you can turn it into an Excel table.
- Simply click on the list and press Ctrl + T
- Excel will ask if your data has headers, click OK
The list will be transformed into a formatted Excel table with filter drop arrows
To display just the overdue or unpaid invoices
- Click the drop arrow beside Due Date to filter by colour – this will display all overdue invoices
Alternatively
- Click the drop arrow beside Payment Status and de-select Paid
- Click the drop arrow beside Days Overdue and select Largest to Smallest
The list will now be filtered to display just the overdue invoices and dates they are overdue by with the largest overdue date at the top of the list making it easier to work through.
From there you can prioritise making calls or sending up follow up messages to customers.
Watch the video below to see how to do it
For an extra idea…..
Step 5 – Create a Routine
Each week, set aside 10-15 minutes to:
- Update payment status
- Review overdue invoices
- Call customers or send reminder emails
Get more tips to make working with Microsoft 365 everyday easier, get my regular eNewsletter here.
#ExcelTips #OverduePayments #TrackInvoices #ExcelTutorial #SmallBusinessTools #InvoiceTracking #ExcelForBusiness #PaymentReminders #ConditionalFormatting #ExcelAutomation #FinanceManagement #ExcelProductivity #LatePayments #ExcelGuide #BusinessEfficiency #ExcelFormulas #InvoiceManagement #ExcelTraining #AccountsReceivable #ExcelWorkflow
