Many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. One interesting thing that we can do with Excel is to make invoices. In this article, we will discuss how to make an Excel Invoice by formula.

Here, we take a data set of different products of a shop. We will use this data to make **invoices** and will provide it to our customers.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**How to Make Invoice Using Formula in Excel**

In this article, we will discuss how to make an invoice using the Excel formula. First, make a draft of the invoice to ensure which information we want to see in the invoice and how the invoice will look.

We want to see the **Invoice number, Date, ID, Product Name, Price, Units, Subtotal** in the **Invoice paper**. Now, we will gradually go forward and use Excel formulas to make an automatic system for this. In the Product section, we will manually select ID and give Units and want to get the rest of the calculation automatically.

**Step 1: Make Invoice ****Inserting Invoice ****Number and Date**

- Initially, we will give the
**Invoice Number and Date**, as we are going to make an invoice for a customer.

**Step 2: Adding Product Names and ID in the Invoice**

- Now, we will add products to the invoice. Which products the customer is going to buy are not known to us. We will enter the product name or IDs manually.

- Every time we want to add a product, we need to write the ID and Product name manually is tiresome work. It will decrease productivity and waste time. We will use an Excel formula consisting of
**VLOOKUP**and**ISBLANK**functions to make this easier. If we enter ID then the product name will appear instantly, otherwise show blank. So, the formula is:

`=IF(ISBLANK(B6),"",VLOOKUP(B6,Products!$B$5:$D$14,2,FALSE))`

- Press
**Enter**and the product name will be shown in the corresponding cell.

**Formula Breakdown**

**VLOOKUP(B6,Products!$B$5:$D$14,2,FALSE)**

It will search **B6** on Product Sheet range **B5:D14** and show output from the 2nd column of the range.

**IF(ISBLANK(B6),””,VLOOKUP(B6,Products!$B$5:$D$14,2,FALSE))**

If a match is found, then show the result otherwise the cell will be blank.

- Now drag the
**Fill Handle**till**Cell C10**.

Notice that while we are putting the **ID **in **Column B**, **Column C** is showing the product names accordingly.

**Step 3: Inserting Price Based on Product ID with Formula in Excel**

- Follow the same method to show the
**Price**in**column D**. And the formula will be for this:

`=IF(ISBLANK(B6),"",VLOOKUP(B6,Products!$B$5:$D$14,3,FALSE))`

**Step 4: Manual Inputs of the Unit Numbers of the Products in Excel**

- Put units on
**Column E**counting the products customer bring to the counter. No formula can be used for this, as it is customized and can change if the customer wants.

**Similar Readings**

**How to Create a Cash Bill Format in Excel (A step-by-step Guideline)****Tax Invoice Format in Excel (Download the Free Template)****Create GST Invoice Format in Excel (Step-by-Step Guideline)****Excel Invoice Tracker (Format and Usage)**

**Step 5: Calculating the Subtotal for Each Item with Formula**

- Now, we want to get the subtotal of each product to calculate the total order amount. We can simply use the multiplication of Price and Units for this. But we will use a conditional formula so that we can avoid errors. We set the condition in a way that if the
**ID column**is blank then no subtotal will show. So, the formula is:

`=IF(ISBLANK(B6),"",D6*E6)`

- Press the
**Enter**button.

**Formula Breakdown**

**IF(ISBLANK(B6),””,D6*E6)**

It will look if **B6** is blank or not. If blank then the cell be blank otherwise show the product of **D6** and **E6**.

- Drag the
**Fill handle**until**Cell C10**.

We can see that we are getting subtotal only in the rows where we have a product ID. The rest of the rows are blank.

If we put any value in the **Price **and **Units** mistakenly, we will not get any subtotal.

The subtotal on the 9th row is blank as there is no product ID.

**Step 6: Determining Total Sum of the Product Prices**

- Use the simple
**SUM**function on the**Cell F11**to get the order subtotal and press**Enter**. The formula is:

`=SUM(F6:F10)`

**Step 7: Including Tax Amount for the Subtotal with Formula**

- Now, we will apply
**7.5%**tax with the subtotal. The formula is:

`=F11*7.5%`

**Step 8: Showing Final Calculated Output of the Invoice**

- Finally, we will get the total after summing Order subtotal and tax. And the formula is:

`=SUM(F11:F12)`

This is an Excel formula sheet to generate Invoices easily.

**Formula Modification with Table & Data Validation in Excel**

Now, we will modify some sections to make this formula sheet more dynamic.

We applied the formula in **Step 3.** We used sheet reference by giving the sheet name and range. In this section, we will work to make this easier.

**Step 1: Declare Data Range as Table in Excel**

- Go to the
**Products**sheet. - Select the range we are using.
- Go to
**Insert**from the main tab. - Select
**Table**from the commands. - A dialog box will show, which will indicate the range.
- The Tick on
**My table has headers**.

- Now, press
**OK**and see the table name on the marked section.

- Now, set the table name as we want and press
**Enter**.

**Step 2: Modify Corresponding Excel Formula**

- Now, we can modify the formula of
**Step 3**and it will be:

`=IF(ISBLANK(B6),"",VLOOKUP(B6,Product,2,FALSE))`

- Also, the price formula is modified in the same way. And that will be:

`=IF(ISBLANK(B6),"",VLOOKUP(B6,Product,3,FALSE))`

**Formula Breakdown**

**VLOOKUP(B6,Product,2,FALSE)**

It will search **B6** on the Product table and show output from the 2nd column of the table.

**IF(ISBLANK(B6),””,VLOOKUP(B6,Product,2,FALSE))**

If a match is found, then show the result otherwise the cell will be blank.

**Step 3: Apply Data Validation for Invoice in Excel**

Now, to get an easier way to make an Invoice we will apply **Data validation** on the ID section.

- Select the cells on the ID columns.
- Go to the
**Data**tab. - Now, select Data validation from the commands.

- Select List from the
**Allow box**. - Select the Id range of the Product sheet in the
**Source box**. - Then press
**OK**.

- Now, go to the
**ID**column. Select any ID from the drop-down.

- Here will select
**A-0002**id and press enter and now show the information against this ID.

This is the final format for a formula-based Excel invoice. Now, we need only to select the ID from the ID column and input the Units and the Invoice will be ready in a few seconds.

**Conclusion**

Here, we discussed how to make an Invoice based on the Excel formula automatically. We thoroughly discussed the steps so that you can understand them easily. Please give your suggestion in the comment box.