Invoice Excel Formula

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.

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

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:

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

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:

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

Step 7: Including Tax Amount for the Subtotal with Formula

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

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:

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:

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

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.

Further Readings

• Transport Bill Format in Excel (Create in 4 Simple Steps)
• Labour Contractor Bill Format in Excel (Download Free Template)
• Hotel Bill Format in Excel (Create with Easy Steps)
• Tally Bill Format in Excel (Create with 7 Easy Steps)