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

Microsoft Excel is a powerful tool. We use Excel Functions in our education, business, and financial analysis. Furthermore, as bookkeepers, we have to build automatic invoice models for our vendors and service providers to maintain transparency of transactions. With this in mind, in this article, we will learn how to create a fully automatic invoice in Excel effectively with appropriate illustrations.

Overview of a fully automatic Invoice in Excel

Download Practice Workbook

Download this workbook and practice while going through the article.

Step-by-Step Procedures to Create Fully Automatic Invoice in Excel

Designing an invoice model for our company can be cumbersome. However, we can utilize Excel functions such as the VLOOKUP, IFERROR, and SUM functions. Further, we will use the Data Validation feature to implement the whole procedure. To demonstrate, we take a dataset that represents auto parts item lists and prices of GoFar Auto Parts Trading & Co.

Fully Automatic Invoice in Excel

Step 1: Basic Information of Fully Automatic Invoice

Before diving into the calculation part, let’s add some basic information and design an invoice template first. For the template, add the following headers.

  • Initially, add the company name in the merged cells, company address, and email address.
  • Further, include Name, Mobile, Date & Invoice No. headers as well.
  • Next, put the correct values under each header.

Basic Information of a fully automatic Invoice

  • Likewise, add headers Item, Base Price, and Item ID (in B4, C4, and D4 respectively, in our case).
  • Afterward, populate each column with correct information.

Dataset of a Fully Automatic Invoice in Excel

Step 2: Utilize Data Validation Feature to Import Data from Table Array

The second step aims to access the Data Validation feature to display the data list in the worksheet. Go through the steps to do so.

  • Firstly, select cell C13 and go to the Data tab Data Validation dropdown icon → Data Validation consecutively.

Utilize Data Validation Feature to Fully Automatic Invoice in Excel

  • Subsequently, the Data Validation dialog box pops up.
  • There, select Settings → Choose List in the Allow box → and in the Source box, select the table of Step 1.
  • Finally, hit OK to close the dialog box.

Utilize Data Validation Feature to Fully Automatic Invoice in Excel

Step 3: Combine VLOOKUP and IFERROR Functions to Display Data

The objective of the third step is to combine the VLOOKUP and IFERROR functions to show the corresponding columns along the specified column. The VLOOKUP function searches for a given data in the leftmost column of a table and returns the data in the same row from a specified column. Alternatively, the IFERROR function returns the specific data that we input for an error condition. Let’s follow the instructions below to create a combination excel formula.

  • To begin with, write the following formula in G13,
=IFERROR(VLOOKUP(C13,'Item List'!$B$4:$C$9,2,0),"")
  • After that, press the Enter or Tab buttons.

Combination of VLOOKUP & IFERROR Functions in G13

  • Next, use the AutoFill tool to fill the formula to the rest of the cells in column G.

Combination of VLOOKUP & IFERROR Functions

  • Similarly, type the following formula in B13,
=IFERROR(VLOOKUP(C13,'Item List'!$B$4:$D$9,3,0),"")
  • Again, press the Enter key.

Combination of VLOOKUP & IFERROR Functions in B13

  • Now, drag the formula cell down to AutoFill the whole column.
  • Thus, we implement the 2 combined functions perfectly.

Combination of VLOOKUP & IFERROR Functions in B13

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),””) however, returns a blank cell if finds any missing or error data.

Read More: How to Create Proforma Invoice in Excel (Download Free Template)

Similar Readings

Step 4: Join SUM and IFERROR Functions to Calculate Invoice Data

In this step, we will thoroughly calculate the input values joining the SUM and IFERROR functions. The SUM function determines the summation of a range and returns an integer. Follow the easy steps for the calculations.

  • First, write the following combined formula in H13,
  • Afterward, press the Enter button.

Excel Multiplication Function and IFERROR Function in H13

  • Later, use the AutoFill icon to drag the formula cell down.

Excel Multiplication Function and IFERROR Function in H13:H17

  • Meanwhile, in H18, write:
  • Similarly, press the Enter or Tab keys.

Using SUM Function in H18 to Create a fully automatic Invoice in Excel

  • Further, calculate the Tax with the formula below:
  • Tab Enter key again.

Multiply H18 by 7%

  • To count the Shipping & Handling charges, type:
  • Pressing the Enter or Tab keys will show the output.

Multiply Total Amount by 5%

  • Lastly, we will calculate the Grand Total using the given formula.
  • Thus, we are close to the desired result.

Add H18, H19, and H20 to calculate Grand Total

Read More: Tax Invoice Format in Excel (Download the Free Template)

Step 5: Input Data and Analyze Invoice Model

In our last step, we will test and verify our Invoice model after inputting values. Watch the GIF carefully for a better understanding.

  • Firstly, 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.
  • Hence, the desired Grand Total will pop up in our dataset.

GIF output of a fully automatic Invoice in Excel


In conclusion, we have discussed some easy steps to create a fully automatic invoice in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan
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

Leave a reply

Advanced Excel Exercises with Solutions PDF