Freelancers or a small business owner like me often run into problems where keeping track of money is a pain in the neck.
That’s why I created a payment tracker for myself so that my team can now provide services or products to multiple clients globally. No sweat.
In a nutshell, I am now full of the joys of spring today.
Like me, you also want to keep track of the money coming in and going out of your business. But don’t know how?
Keep Reading, As I’m about to show you how to create an Excel spreadsheet to track your customer’s invoices and payments.
So, let’s get started.
How To Keep Track Of Invoices and Payments in Excel
Look at the invoice history of the table below.
The dataset contains Invoice ID, Invoice Date, Due Date, Client name, invoice amount, payment date, payment amount, and also status of the payment, whether it is paid or pending. You can add one/two columns depending on your criteria.
| Invoice ID | Invoice Date | Due Date | Client Name | Invoice Amount (USD) | Payment Date | Payment Amount | Status |
| OD1123 | 05-01-23 | 15-01-23 | Dumala John | 20,000 | Pending | ||
| OD2123 | 20-01-23 | 27-01-23 | Ttakshang | 12,000 | 25-01-23 | 12,000 | Paid |
| OD3123 | 24-01-23 | 31-01-23 | Pintu Agarwal | 20,000 | 28-01-23 | 20,000 | Paid |
| OD1223 | 7-02-23 | 15-02-23 | Steven Atras | 15,000 | Pending | ||
| OD2423 | 12-04-23 | 28-04-23 | Noman Ali | 35,000 | 25-04-23 | 35,000 | Paid |
Here are the steps to generate an invoice tracker:
1. Create a Spreadsheet
Open a new Excel spreadsheet and create columns for each of the following:
- Invoice Number
- Invoice Date
- Customer Name
- Invoice Amount
- Payment Date
- Payment Amount
- Balance Due
2. Enter the Invoices
Enter each invoice you send to your customers into the spreadsheet, including the invoice number, date, customer name, and invoice amount.
Ensure entering each invoice in a separate row.
3. Enter the Payments
As you receive payments from customers, enter them into the spreadsheet. Enter the payment date, payment amount, and adjust the balance due accordingly. You can use Excel formulas to automatically calculate the balance due.
The best formula would be to subtract the invoice amount with the payment amount. Don’t forget to type the equals sign before entering any formula.
4. Sort and Filter the Data
Sort the data by customer name or invoice date, so it’s easy to find specific invoices and payments. You can also filter the data to show only unpaid invoices or overdue payments.
Every time when I get a huge dataset from my office, I always run in Excel and sort the overall data first.
Like me, you might also know sorting and filtering data in Excel allows you to easily organize and find specific information within your spreadsheet.
But, don’t know how?
- Select the range of data you want to sort or filter.
- Click on the Data tab and select the Sort/Filter button, depending on your preference.
- Choose the column you want to sort and set the sort order (ascending or descending).
- Select OK.
Note: You can use the Advanced Filter functions to filter data based on multiple criteria.
5. Use Conditional Formatting
Use conditional formatting to highlight overdue payments or unpaid invoices. This can help you identify issues before they become bigger problems.
It’s really simple to apply formatting to a worksheet. Go to the Home Tab and select the option that says Conditional Formatting. You can select Highlight Cell rules and click on the option that says Text that contains unpaid/overdue. Apply different color codes and you are all set.
6. Create Summary Reports
Create summary reports that show the total amount invoiced, the total amount received, and the outstanding balance for each customer.
This will help you see how much money is coming in and identify customers who need follow-up.
So, now you can keep track of your financial records in Excel as easily as that.
Additional Tips To Sort and Filter Customer Payments in Excel
1. Generate a Dynamic List For Ease
Dynamic dropdown lists in Excel are super helpful when you have a huge dataset. Imagine pouring water in a mug. That’s how easy it gets.
To add drop-down lists to the Customer Name and Invoice Number columns in Excel, follow these steps:
- Select the cells in the Customer Name column where you want to add the drop-down list.
- Go to the Data tab on the ribbon and click on Data Validation in the Data Tools group.
- Select List in the Allow dropdown menu from the Data Validation dialog box.
- Type or paste the list of customer names in the source box that you want to appear in the drop-down list, separated by commas.
- Click OK to create the drop-down list.
- Repeat these steps for the Invoice Number column, using a list of invoice numbers instead of customer names.
2. Save the File on a Regular Basis
Do save the files always. It’s extremely difficult to recover damaged or unsaved files. However, you can enable the autosave feature in Excel to ease your task. To enable the autosave feature, go to Files > Options > Save. Ensure that the AutoSave box is enabled.
For mac users, open up your Excel workbook. Go to Excel > Preferences > Sharing and Privacy > Save > Turn on Autosave by default.
FAQ
Q: What are invoices and payments?
A: Customer invoices are bills or statements that a business sends to its customers requesting payment for products or services rendered.
Invoices more or less include details such as the date, invoice number, a description of the goods or services provided, quantity, price per unit, and the total amount due. Sometimes it may also include the method of payment.
Needless to say, payment is the money a customer sends to the business in exchange for the goods or services provided. Around 60% of people pay through cash. Other’s use checks, credit cards, or electronic transfer.
Q: Is Excel good for tracking finances?
A: Yes, Excel is a great tool if you want to track your day-to-day finances. There are some templates that you can use to save time rather than creating one. However, don’t forget to input data regularly as there aren’t any options for adding reminders.
Conclusion
In short,
It’s important to keep track of payments if you are a businessman. There are many budget trackers or invoice trackers that are unofficial. But, Microsoft’s Excel aces when coming to any important tasks like tracking payments.
You can also find some decent, attractive templates that are completely free. So, give it a go.
