How to Create Fully Automatic Invoice in Excel (with Easy Steps)

Download the Template

Fully Automated Invoice.xlsx

Excel functions such as the VLOOKUP, IFERROR, and SUM functions can be used to create fully automatic invoices. Use the Data Validation feature to implement the whole procedure. The dataset below represents auto parts item lists and prices of a company.

Fully Automatic Invoice in Excel

Step 1 – Basic Information of Fully Automatic Invoice

In the merged cells, add the company name, company address, and email address.
You can include Name, Mobile, Date & Invoice No. headers.
Enter the correct values under each header.

In the headers, add Item, Base Price, and Item ID (in B4, C4, and D4 respectively).
Input correct information in each column.

Step 2 – Utilize the Data Validation Feature to Import Data from Table Array

Select cell C13.
Go to the Data tab.
Select Data Validation then go to Data Validation.

The Data Validation will dialog box pop up.
Select Settings.
Choose List in the Allow box.
In the Source box, select the table of Step 1.
Click OK.

Step 3 – Combine VLOOKUP and IFERROR Functions to Display Data

Enter the following formula in G13:

=IFERROR(VLOOKUP(C13,'Item List'!$B$4:$C$9,2,0),"")

Press the Enter or Tab button.

Use the AutoFill tool to fill the formula to the rest of the cells in column G.

Enter the following formula in B13:

=IFERROR(VLOOKUP(C13,'Item List'!$B$4:$D$9,3,0),"")

Press Enter.

Drag the formula cell down to AutoFill the whole column.

Formula Breakdown:

VLOOKUP(C13,’Item List’!$B$4:$C$9,2,0) looks for the corresponding 2nd row of the data in C13 from the range B4:C9 of the Item List table.
IFERROR(VLOOKUP(C13,’Item List’!$B$4:$C$9,2,0),””) returns a blank cell if it finds any missing or error data.