Sales Invoice Tracker Template In MS Excel

This Sales Invoice Tracker template for excel will calculate line item of invoice subtotals, sales tax, and allow you choose the different type of invoice and then display the corresponding customer invoiced data. And it is a little complicated and also very powerful template. And you can download it freely to use it in your business.

This Sales Invoice Tracker template is mainly used to maintain a history of customers, invoices and invoice details. You can analyze your previously invoiced data base on one invoice type.

Sales Invoice Tracker Template In MS Excel

Sales Invoice Tracker template Description

4 worksheets will be used in this template: Invoice, Customers, Invoices-Main, Invoice Details

Invoice#:

  • select an Invoice type from the drop down list of “Invoice#

Sales Invoice Tracker Template In MS Excel2

The range B4:B5 in the worksheet “Invoices-Main” is selected as the Data source of G4 cell in “Invoice” worksheet.

If you want to check the data source, just following the below steps:

1# click “G4” cell in worksheet “Invoice

Sales Invoice Tracker Template In MS Excel3

2# click “DATA” tab in excel Ribbon, then click “Data Validation” command in “Data Tools” group

Sales Invoice Tracker Template In MS Excel4

3#Data Validation” window will appear.

Sales Invoice Tracker Template In MS Excel5

4# click “Data source” button, it will locate to the referring range.

Sales Invoice Tracker Template In MS Excel6

or you can check the defined name (=Invoice_No) from “FORMULAS”->”Name Manager”.

Sales Invoice Tracker Template In MS Excel7

The excel formula of Customer information

All the fields of customer information apply to the similar Excel formula, as below:

The field “Bill To” ‘s formula:

=IFERROR(VLOOKUP(VLOOKUP(rngInvoice,InvoicesMain,2,FALSE),CustomerList,3,TRUE),””)

Let’s see how to understand this formula:

Assuming: the value “3-456-2” is selected in G4 cell.

rngInvoice: It’s a defined name and refers to “=Invoice!G$4”, so now rngInvoice=”3-456-2
InvoicesMain:  It’s a defined name of a range of cell in the “Invoices-Main” worksheet and refers to “=’Invoices-Main’!$B$4:$K$5
CustomerList:  It’s a defined name of a range of cell in the worksheet “Customers” and refers to “=Customers!$B$4:$L$5
VLOOKUP(rngInvoice,InvoicesMain,2,FALSE): It will search “3-456-2” string in the first column of excel range “InvoicesMain”, and returns the value from column2 that is in the same row, and this excel formula will return: “2 – Contoso, Ltd
VLOOKUP(VLOOKUP(rngInvoice,InvoicesMain,2,FALSE),CustomerList,3,TRUE) : it will search “2 – Contoso, Ltd” in the first column of excel range “CustomerList” and returns the value from column 3 that is in the same row, this excel formula will return: “Contoso, Ltd

The excel formula of invoiced data

It mainly use array formula with INDEX, IF,SMALL and Row functions, such like below formula:

Assuming: the value “3-456-2” is selected in G4 cell.

The Cell B9’s formula:

=IFERROR(INDEX(InvoiceDetails,SMALL(IF(InvoiceDetails[Invoice '#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(1:1)), MATCH($B$8, InvoiceDetails[#Headers], 0)),"")

rngInvoice: equal to “3-456-2”
InvoiceDetails: it’s a defined name of a range of cell in the worksheet “Invoice Details” and refers to “=’Invoice Details’!$B$4:$H$40”
InvoiceDetails[Invoice ‘#]: it’s range cell and  refers to: $C$4:$C$40
ROW(InvoiceDetails): it will return all row numbers for range InvoiceDetails(C4:C40), this function will return: “{4;5;6;…40}

 The excel formula of invoiced data2

ROW(InvoiceDetails[#Headers]): it will return the row number of “headers” row in the worksheet “Invoice Details”, this function will return value:”3”

The excel formula of invoiced data1

ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers]): the returned row number minus the row number of headers row. Such as: (row )29 –(row )3=(row )26
IF(InvoiceDetails[Invoice ‘#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])): If the value of  “Invoice #” in the range $C$4:$C$40 equal to rngInvoice (assuming: 3-456-2), then return its row number, so this IF function will return a array list , contains all row numbers that match the condition. Such like: {26;27;28…37}
ROW(1:1): return “1”
ROW(2:2): return “2”
SMALL(IF(InvoiceDetails[Invoice ‘#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(1:1)): it will return the smallest value in the array list, this formula will return “26”

The excel formula of invoiced data3

SMALL(IF(InvoiceDetails[Invoice ‘#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(2:2)):it will return the second smallest value in the array list, this formula will return “27”
InvoiceDetails[#Headers]: returns all values of headers row in the worksheet “Invoice Details”, such like: {“Description”,”Invoice#”, “Item#”…}
MATCH($B$8, InvoiceDetails[#Headers], 0)): it will return the position of value “Item#” in the range InvoiceDetails[#Headers]. This function will return: “3”

 So now C9’s formula can be reduce to: =IFERROR(INDEX(InvoiceDetails,26,3,””), it will return a value(Z4567) from table “InvoiceDetails” based on the index (26,3).

How To get this free Sales Invoice Tracker template in Excel?

Click “File” Tab ->”New” , then input “Sales Invoice Tracker template”  to search online template in the search box.

get this free Sales Invoice Tracker template in Excel

 

 

 

Related Posts

Invoice template with finance charge In MS Excel
Invoice template with finance charge2

This finance invoice template for excel will calculate line item total automatically, you need to enter customer and shipping details, descriptions and amount. And it is very easy to customize and use. And also is a very simple invoice finance ...

Billing invoice template In MS Excel
Billing invoice template2

This billing invoice template for excel will calculate line item total. And payment details can be find in another worksheet named as “Company Setup”. If you are looking for a professional billing invoice template for MS excel, this billing invoice ...

Invoice template that calculates total In MS Excel
Invoice template that calculates total In MS Excel2

This invoice template for excel will calculate line item Amount, sales tax, and Less Deposit Received to get invoice Total value. And it is very easy to customize and use. And also is a very simple invoice template for your ...

Service Invoice Template In MS Excel
Service Invoice Template for excel

This service invoice template for excel will calculate line item totals, sales tax, and allow you to input Salesperson name , job description, Payment Terms and Due Date for this invoice. And it is very easy to customize and use. ...

Simple Invoice Template In MS Excel
simple invoice template for excel 1

This invoice template for excel will calculate line item totals, sales tax, Deposit Received and deduct discount offered.  And it is very easy to customize and use. And you can download it freely to use it in your business. Simple ...

Free invoice Templates In MS Excel spreadsheet

If you are still looking for a reliable and easy to use invoice template in MS Excel Spreadsheet, and here is a right place, and this post will provide you with various types of excel invoice template for your free ...

Free Microsoft Excel Spreadsheet Templates

This excel tutorial will give you a simple introduction of MS excel spreadsheet template and a collection of Microsoft Excel Spreadsheet templates for free download. If you are looking for an easy to use and free Microsoft excel templates, this ...

How To Use Template In MS Excel spreadsheet
excel template 6

Excel template is also a workbook, the only different is that it can be as a template or module to create other similar workbooks. There are many free excel templates in the internet or you can create your own excel ...

Sidebar