How to Keep Track of Invoices and Payments in Excel (3 Ideal Examples)

The article shows how to keep track of invoices and payments in excel. It’s pretty important for daily activities in a shop or market. Keeping track of invoices and payments will help you to find out how much money you will get from your customer and when you will get that from them.

In the dataset, I’m showing you the first template for keeping track of invoices and payments.

Download Invoice & Payment Template (Free)

3 Examples to Keep Track of Invoices and Payments in Excel

1. Keeping Track of Invoices and Payments in Excel by Showing Recent and Past Invoice Amounts

In this section, I’ll show you a detailed invoice tracker for business purposes. The template will show you both recent and past invoices in the sheet. Let’s see what’s in the description below.

Steps:

• First, make a chart like the following image.

• Select the range B11:J12 and then go to Insert >> Table
• A dialog box will show up and check My table has headers.
• And then click OK.

• You will see a table After that, we are going to type some formulas in some cells.
• Type the following formula with the IFERROR Function in cell G12 and press ENTER.

We use this formula to calculate dues or outstanding amounts.

• Then type this formula in cell H12 and press ENTER.

The above formula will show you whether your customer paid the invoice and the status of the due. Here, we defined a named range for the present date and the name is PD. It also gives you the information if your customer gets some return from you. We used the IF Function in that formula.

• Now use this formula in cell I12 and press ENTER.

This formula notices you about the duration of the due.

.

• Next, type the following formula in cell J12 and press ENTER.

The above formula takes a note about invoice data. It uses the AGGREGATE Function.

• Write down the formula below in cell F6 and press ENTER.

We use this formula to put the number of invoices in certain time duration.

• After that, press ENTER and use the Fill Handle to AutoFill the cells up to F9.

• Then type the formula given below in cell G6, press ENTER and use the Fill Handle to AutoFill the cells up to G9.

This formula will store the invoices in a given period.

• After that, we will be using this formula to store recent invoices in cell B5.

It will store recent invoices in cell B5.

• Another formula in cell B6 will be used.

This will put the total invoices of the recent times in cell B6.

• We again write the following formula in cell D5 and press ENTER.

This will calculate the number of past dues in D5.

• Type the following formula in cell D6 and press ENTER.

This formula will store the amount of past dues in D5. In the above process, we used some different functions like SUMIFS and COUNTIFS.

• Now we are all set. I just put some random data to show you how your invoice tracker will work.

The advantage is, that if you put new entries, you will see the updated invoice history at a glance as we are using a table. In this way, you can keep track of invoices and payments in Excel

Read More: Excel Invoice Tracker (Format and Usage)

2. Using Table Feature to Keep Track of Invoices and Payments in Excel 

In this section, I’ll show you a simple Excel table format so that anyone can keep the invoices and payments history. Let’s go through the description below for a better understanding.

Steps:

• First, make a chart like the following picture.

• Select the range B3:I8 and go to Insert >> Table 
• A dialog box will show up. Check My table has headers and click OK.

• You will see a table show up. We will write some necessary formulas. Type the following formula in cell F9.

This formula will store the total invoice of the table with the help of SUM.

• Then type the following formula in cell G9.

This formula will store the total paid amount.

• After that, write down the formula given below.

This formula will store the total outstanding of the table.

• Now type the formula in cell I4.

We use this formula to calculate row-wise outstanding.

• After that, use the Fill Handle to AutoFill the cells up to I8.

Now we put some random data to show you how this template works.

Thus, you can keep track of invoices and payments in Excel

Read More: How to Keep Track of Customer Payments in Excel (With Easy Steps)

Similar Readings

• How to Create Leave Tracker in Excel (Download Free Template)
• How to Track Stocks in Excel (Download Free Template)
• Students Tracking Their Own Progress Template
• How to Create a Task Tracker in Excel (Download Free Template)
• Tracking Student Progress Excel Template (Free Download)

3. Storing Customer Information Automatically to Keep Track of Invoices and Payments in Excel

If you have some regular customers, you can keep their information and use them to create an invoice and payment slip. This will be helpful if they want to put an online order. In the following description, I’ll be showing you how you can make this invoice and payment tracker.

Steps:

• First, store your customers’ information on a new sheet.

• Then create an Excel chart like the following picture in another sheet. Suppose we want to make an invoice tracker for today and so we use a formula with the TODAY Function for the date. And if you want to know how to create a table, go to Section 2

• After that, create a named range for the biller company. In this case, I named it CustomerNamesLookup.

• We created a data validation list for the biller company. For that reason, select the cell C3 and go to Data >> Data Validation
• After that, select List from the Allow: section and set the Source as ‘=CustomerNamesLookup’.
• Click OK.

• After that, create another name for the range B3:I5 of the customer information In this case, it’s CustomerList.

• Now we are going to put in some necessary formulas. Let’s start with cell C4.

This formula will store the address of the biller company. We used VLOOKUP to look for the CustomerList and CONCATENATE to put the address and ZIP Code. In the following image, you will see the address and ZIP code for the Activision company.

• Press the ENTER button and you will see the address of your selected customer.

• After that, type the following formula in cell C5.

The formula will lookup for the CustomerList to provide the name of the city and state where they live in.

• You will see the name of the city and state after pressing the ENTER

• After that, type this formula in cell E3.

The formula will provide you with the name of the customer.

• Press the ENTER button and you will see the name of the person who will contact you on behalf of the company.

• After that, write down the formula in cell E4.

You can see the phone number of the contact guy after using this formula.

• After that, press the ENTER button and you will see the phone number of the person.

• Then again, type the following formula in cell E5.

This will provide you with the email ID of the customer.

• Hit ENTER and you will see the email ID in cell E5.

• Type the following formula in H8. Here we used logic functions IF and AND.

This will give you the total invoice for your product.

• Press ENTER and AutoFill the cells up to H12.

• Now make a name for the range B8:H12. I named it InvoiceTable.

• Write down the formula below and hit ENTER.

This will store the total amount of invoice.

• To determine the tax amount, type the following formula in F14 and press ENTER.

• After that, again type a formula in cell H14 and press ENTER.

This formula will give you the amount the customer has to pay.

You are all set now. Let’s just put some random data to show you how this template works.

Suppose EA Sports wants to order the following items. You just put the products and invoice information and select EA Sports from the drop down list to find their contact information.

• After choosing EA Sports from the data validation list, you can easily contact with them.

Thus, you can keep track of your invoices and payments in Excel.

Read More: How to Keep Track of Inventory in Excel (2 Easy Methods)

Practice Section

Here I’m giving you a template so that you can make your own template.

Conclusion

In the end, I believe the templates of this article will be helpful for you to keep track of invoices and payments with the help of Excel. If you don’t want to trouble yourself making  your own template for invoice and payment tracking, just download the file and choose a template of your wish. If you have any feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

Related Articles

• How to Keep Track of Clients in Excel (Download Free Template)
• How to Track Attendance in Excel (with Detailed Steps)
• Create Fully Functional To Do List in Excel (4 Handy Methods)
• How to Create a Recruitment Tracker in Excel (Download Free Template)
• Make a Sales Tracker in Excel (Download Free Template)
• How to Maintain Store Inventory in Excel (Step by Step Guide)