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 customers 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.
How to Keep Track of Invoices and Payments in Excel: 3 Ideal Examples
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.
=IFERROR([@[Invoice Bill]]-[@Paid],"")We use this formula to calculate dues or outstanding amounts.
- Then type this formula in cell H12 and press ENTER.
=IFERROR(IF(OR([@[Invoice Bill]]="",[@Date]="",[@[Due Date]]="", [@[Due Date]]<[@Date]),"Error",IF([@Due]=0,"Fully Paid", IF([@Due]>0,IF(PD<[Due Date],"Recent",IF(PD=[@[Due Date]], "Due Today",IF(PD>[@[Due Date]],"Past Due"))),IF([@Due]<0, "Get Return")))),"")
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.
=IFERROR(IF([@Status]="Past Due",IF(PD-[@[Due Date]]<30, "1st Month",IF(PD-[@[Due Date]]<60,"2nd Month", IF(PD-[@[Due Date]]<90,"3rd Month", "90+ Days"))),""),"")This formula notices you about the duration of the due.
.
- Next, type the following formula in cell J12 and press ENTER.
=IFERROR(IF(AGGREGATE(3,5, [@Due])=1,1,0),"")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.
=IFERROR(COUNTIFS(Invoice_Info[Due Period],E6,Invoice_Info[Selected],1),"")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.
=IFERROR(SUMIFS(Invoice_Info[Due],Invoice_Info[Due Period],E6,Invoice_Info[Selected],1),"")
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.
=IFERROR("INVOICES: "&COUNTIFS(Invoice_Info[Status],"Recent",Invoice_Info[Selected],1)+COUNTIFS(Invoice_Info[Status],"Due Today",Invoice_Info[Selected],1),"")It will store recent invoices in cell B5.
- Another formula in cell B6 will be used.
=IFERROR(SUMIFS(Invoice_Info[Due],Invoice_Info[Status],"Recent", Invoice_Info[Selected],1)+SUMIFS(Invoice_Info[Due], Invoice_Info[Status],"Due Today",Invoice_Info[Selected],1),"")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.
=IFERROR("INVOICES: "&COUNTIFS(Invoice_Info[Status], "Past Due",Invoice_Info[Selected],1),"")
This will calculate the number of past dues in D5.
- Type the following formula in cell D6 and press ENTER.
=IFERROR(SUMIFS(Invoice_Info[Due],Invoice_Info[Status], "Past Due",Invoice_Info[Selected],1),"")
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: How to Keep Track of Customer Orders in Excel
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:
- 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.
=SUM(F4:F8)
This formula will store the total invoice of the table with the help of the SUM function.
- Then type the following formula in cell G9.
=SUM(G4:G8)
This formula will store the total paid amount.
- After that, write down the formula given below.
=SUM(I4:I8)
This formula will store the total outstanding of the table.
- Now type the formula in cell I4.
=F4-G4
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
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
=TODAY()
- 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.
=IFERROR(VLOOKUP(C3,CustomerList,3,FALSE),"") & ", " & CONCATENATE(VLOOKUP(C3,CustomerList,4,FALSE))
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.
=IF(VLOOKUP(C3,CustomerList,4,FALSE)="","",IF(VLOOKUP(C3,CustomerList,5,FALSE)<>"",CONCATENATE(VLOOKUP(C3,CustomerList,5,FALSE),", ",VLOOKUP(C3,CustomerList,6,FALSE)),CONCATENATE(VLOOKUP(C3,CustomerList,6,FALSE))))
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.
=IFERROR(VLOOKUP(C3,CustomerList,2,FALSE),"")
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.
=IFERROR(VLOOKUP(C3,CustomerList,7,FALSE),"")
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.
=IFERROR(VLOOKUP(C3,CustomerList,8,FALSE),"")
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.
=IF(AND([@Qty]<>"",[@[Unit Price]]<>""),([@Qty]*[@[Unit Price]])-[@Discount],"")
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.
=SUM(InvoiceTable[Total])
This will store the total amount of invoice.
- To determine the tax amount, type the following formula in F14 and press ENTER.
=D14*E14
- After that, again type a formula in cell H14 and press ENTER.
=D14+F14+G14
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.
Practice Section
Here I’m giving you a template so that you can make your own template.
Download Invoice & Payment Template (Free)
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.
Hello,
I hope all is well!
I am trying to mirror your steps, and I’m having an issue when I get to the G6 Step.
I keep writing the following formula =IFERROR(COUNTIFS(Invoice_Info[Due Period],E6,Invoice_Info[Selected],1),””)but every time i press enter it gives me an error. However, i’m not sure what the Invoice_Info is. This is what i end up doing – =IFERROR(SUMIFS(Table1[Due],Table1[Due Period],E6,Table1[Selected],1),””) but i’m not if that correct either.
Hi! Thanks for asking. As the article was to provide some templates, I didn’t go in detail with the formula. Here, Invoice_Info is a named range. It refers to the range B12:J17 of the ‘template 1’ sheet. To create a named range, you need to select your desired range of cells, then go to Data Tab >> Define Name and after that, give a name of that range. The advantage is that, you can use that range by inserting it’s name anywhere in the worksheet or workbook. You don’t have to select the range every time you use it in a formula. Hope this solves your problem.