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.
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.
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.
- 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.
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.
- 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.
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.
- Next, use the AutoFill tool to fill the formula to the rest of the cells in column G.
- Similarly, type the following formula in B13,
=IFERROR(VLOOKUP(C13,'Item List'!$B$4:$D$9,3,0),"")- Again, press the Enter key.
- Now, drag the formula cell down to AutoFill the whole column.
- Thus, we implement the 2 combined functions perfectly.
- 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
- Transport Bill Format in Excel (Create in 4 Simple Steps)
- How to Create a Cash Bill Format in Excel (A step-by-step Guideline)
- Create GST Invoice Format in Excel (Step-by-Step Guideline)
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.
- Later, use the AutoFill icon to drag the formula cell down.
- Meanwhile, in H18, write:
=SUM(H13:H17)- Similarly, press the Enter or Tab keys.
- Further, calculate the Tax with the formula below:
=H18*7%- Tab Enter key again.
- To count the Shipping & Handling charges, type:
=H18*5%- Pressing the Enter or Tab keys will show the output.
- Lastly, we will calculate the Grand Total using the given formula.
=H18+H19+H20- Thus, we are close to the desired result.
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.
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.
Related Articles
- How to Create Bill Payment Checklist in Excel (2 Smart Ways)
- Use Electricity Bill Calculation Formula in Excel
- How to Create Bill Book in Excel (2 Easy Ways)
- Create Invoice in Word from Excel Data (with Easy Steps)
- Labour Contractor Bill Format in Excel (Download Free Template)
- Hotel Bill Format in Excel (Create with Easy Steps)
- Excel Invoice Tracker (Format and Usage)
