Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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,
=IFERROR(F13*G13,"")
  • 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:
=SUM(H13:H17)
  • 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:
=H18*7%
  • Tab Enter key again.

Multiply H18 by 7%

  • To count the Shipping & Handling charges, type:
=H18*5%
  • 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.
=H18+H19+H20
  • Thus, we are close to the desired result.

Add H18, H19, and H20 to calculate Grand Total


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


Conclusion

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.

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo