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

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.

### Step 1 – Basic Information of Fully Automatic Invoice

• 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.

### Step 4 – Join SUM and IFERROR Functions to Calculate Invoice Data

• Enter the following combined formula in H13:
=IFERROR(F13*G13,"")
• Press Enter.

• Use the AutoFill icon to drag the formula cell down.

• In the cell H18, enter the following formula:
=SUM(H13:H17)
• Press Enter.

• Calculate the Tax with the formula below:
=H18*7%
• Press Enter.

• To count the Shipping & Handling charges, enter the formula in H19:
=H18*5%
• Press Enter.

• To calculate the Grand Total, enter the given formula in H21:
=H18+H19+H20
• Press Enter.

### Step 5 – Input Data and Analyze the Invoice Model

• Click any cell under the Description column and look for a dropdown icon.
• Tapping the Dropdown icons will display the item list.
• Select any item and type the number of items in the Quantity column.
• The desired Grand Total will show in our dataset.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF