How to Keep Track of Invoices and Payments in Excel – 3 Examples

It returns the duration of the due.

track of invoices and payments in excel" width="820" height="488" />

.

• Enter the following formula in J12 and press ENTER.

The formula returns information on invoice data. It uses the AGGREGATE Function.

It returns the number of invoices in a period of time.

track of invoices and payments in excel" width="824" height="429" />

track of invoices and payments in excel" width="824" height="429" />

It stores invoices within a given period.

=IFERROR("INVOICES: "&COUNTIFS(Invoice_Info[Status],"Recent",Invoice_Info[Selected],1)+COUNTIFS(Invoice_Info[Status],"Due Today",Invoice_Info[Selected],1),"")

=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),"")

It returns total invoices in recent times in B6.

track of invoices and payments in excel" width="809" height="478" />

• Enter the following formula in D5 and press ENTER.

It calculates the number of past dues in D5.

• Enter the following formula in D6 and press ENTER.

track of invoices and payments in excel" width="809" height="428" />

It stores past dues in D5.

• The following chart is an example of an invoice tracker with random data:

track of invoices and payments in excel" width="793" height="488" />

New entries will update the invoice history.

Example 2 – Using the Table Feature to Keep Track of Invoices and Payments in Excel

Steps:

• Create a table like the following:

track of invoices and payments in excel" width="793" height="245" />

• Enter the following formula in F9.

track of invoices and payments in excel" width="821" height="296" />

It stores the total invoice using the SUM function.

It stores the total paid amount.

• Add the formula below.

track of invoices and payments in excel" width="823" height="292" />

It stores the total outstanding.

It calculates row-wise outstanding.

track of invoices and payments in excel" width="822" height="296" />

The following chart showcases a template with random data to keep track of invoices and payments in Excel:

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

Steps:

• Store your customers’ information in a new sheet.

track of invoices and payments in excel" width="760" height="313" />

• Create a table like the one in the following picture in another sheet.
• To make an invoice tracker for today, use the TODAY Function for the date.

• Create a named range for the billing company. Here, CustomerNamesLookup.

track of invoices and payments in excel" width="759" height="343" />

track of invoices and payments in excel" width="762" height="364" />

• Enter the following formula in C4.

It stores the address of the billing company. The VLOOKUP function looks for the CustomerList and the CONCATENATE Function for address and ZIP Code.

• Press ENTER and you will see your customer’s address.

track of invoices and payments in excel" width="800" height="471" />

• Enter the following formula in 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))))

It returns the name of the city and state.

track of invoices and payments in excel" width="798" height="478" />

It returns the contact person’s name.

track of invoices and payments in excel" width="795" height="410" />

It returns the phone number.

track of invoices and payments in excel" width="800" height="408" />

• Enter the following formula in E5:

It returns the customer’s email ID.

track of invoices and payments in excel" width="799" height="410" />

It returns the total invoice for your product.

track of invoices and payments in excel" width="813" height="467" />

• Enter the formula below and press ENTER.

It stores the total amount of the invoice.

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

track of invoices and payments in excel" width="815" height="410" />

It returns the amount to be paid.

The following chart showcases a template with random data:

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

track of invoices and payments in excel" width="818" height="468" />

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

Practice Section

Use the following template to practice:

track of invoices and payments in excel" width="795" height="424" />

Download Invoice & Payment Template (Free)

Related Articles

• How to Keep Track of Clients in Excel
• How to Make a Sales Tracker in Excel
• How to Create a Task Tracker in Excel
• How to Create a Daily Task Sheet in Excel
• How to Create a Progress Tracker in Excel
• How to Create Real Time Tracker in Excel

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency. Read Full Bio

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.

Reply

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.