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

Sometimes we need to create a proforma invoice for our organization. Using Microsoft Excel, we can easily make it. In this article, we will show you step by step procedure to create a proforma invoice in Excel. If you are curious to know about the process, download our practice workbook and follow us.


Download Template

Download this free template for practice while you are reading this article.


What Is Proforma Invoice?

The proforma invoice represents an initial bill or invoice. This kind of bill uses for requesting the payment from the committed consumer for products or services before the items are supplied. The invoice contains an illustration of the products, the total payable amounts, and all other necessary information about the transaction.


Step-by-Step Procedure to Create Proforma Invoice in Excel

The complete procedure to Create the proforma invoice format is explained below.  After reviewing all the steps, we will be able to make the proforma invoice according to your desire. The outlook of our final invoice will be like the image shown below:

How to Create Proforma Invoice in Excel


Step 1: Create Dedicated Slot for Shipping Information

In this step, we will show you how to create the dedicated slot for showing the shipping details in the invoice.

  • First of all, choose a shape according to your desire to set a title for your proforma invoice. Inserting the shape is not mandatory, but it will give your invoice a good outlook.
  • For that, in the Insert tab, click on the drop-down arrow of the Illustrations > Shapes.
  • Now, choose any shapes according to your desire. For our invoice, we select the Scroll: Horizontal shape.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • You will see your mouse icon will be changed. Click and drag your mouse to insert the shape.
  • Set the title of the invoice inside the shape. We set our invoice title as PROFORMA INVOICE.

  • After that, select the range of cells B5:D5 and choose the Merge & Center option from the Alignment group, located in the Home tab.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Then, set the title of this merged cell as SHIPPER DETAILS and format the cell according to your desire.

  • Now, in the range of cells B6:B11, write down the following entities so that anyone can input all the necessary information about the seller.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Next, select the range of cells C6:D6 and click the Merge & Center option from the Alignment group, located in the Home tab.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Similarly, merge all the range of cells C7:D11.
  • The spot to input the seller information is ready.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Similarly, create another two slots for consumer details and shipping details by following the same procedure.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Now, insert a suitable border between each section to separate them. In our invoice, we use a double-line border between each slot and an outside border to give the invoice a better visual.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • Finally, insert your organization’s logo in the invoice. For that, select cell I1 and click on the drop-down arrow of the Illustrations > Pictures from the Insert tab.
  • Then, select the This Device option.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

  • As a result, the Insert Image dialog box will appear. Select your institution’s logo and click on Insert. In our case, we choose our website logo to demonstrate the process.

  • Our first job is finished.

Create Dedicated Slot for Shipping Information to Create Proforma Invoice

Thus, we can say that we have completed the first step to create the proforma invoice in Excel.

Read More: Excel Invoice Tracker (Format and Usage)


Step 2: Design Product Details

In the following step, we are going to design the product’s detail list. We will include all the necessary information regarding the products.

  • At first, in the range of cells B13:J13, write down the following title to create the product list.

Design Product Details Sheet to Create Proforma Invoice

  • Now, in cell J14, write down the following formula to round the price amount of that product. To round the price amount, we will use the ROUND function.

=ROUND(H14*I14,0)

  • Press Enter.

Design Product Details Sheet to Create Proforma Invoice

  • Then, drag the Fill Handle icon to copy the formula up to cell J18.

  • After that, select the range of cells B14:J14, and in the Home tab, click the drop-down arrow of the Cell Styles from the Styles group.
  • Choose the 20%- Accent 1 option from the Themed Cells Styles section.

Design Product Details Sheet to Create Proforma Invoice

  • Now, click on the Format Painter option from the Clipboard group and set the same formatting in rows 16 and 18.

Design Product Details Sheet to Create Proforma Invoice

  • It will provide your datasheet with better readability to its users.

  • Next, select the range of cells I14:J14 and choose the Accounting cell format from the Number group, located in the Home tab.

Design Product Details Sheet to Create Proforma Invoice

  • Finally, set an outside border at the edge of the product list and the right border for every column.

Design Product Details Sheet to Create Proforma Invoice

At last, we can say that we have finished the second step to create the proforma invoice in Excel.

Read More: Create GST Invoice Format in Excel (Step-by-Step Guideline)


Step 3: Estimate Final Bill

Now, we will calculate the final bill for this shipment. After completing this step, our proforma invoice will complete.

  • First, select the range of cells H20:I20, and click the Merge & Center option from the Alignment group, located in the Home tab.

Estimating Final Bill to Create Proforma Invoice

  • Then, titled merged cell as SUBTOTAL AMOUNT. Format the cell according to your desire.

  • Now, in cell J20, use the SUM function to sum the range of cell J14:J18. For that, write down the following formula into the cell.

=SUM(J14:J18)

  • Press Enter.
  • Set the cell format as Accounting from the Number group.

Estimating Final Bill to Create Proforma Invoice

  • After that, add the following entities in the range of cells I21:I27. Among these entities, we mentioned the freight charge, two types of taxes, Insurance for the products, inspection charge, etc. You can modify these items according to your desire.

  • Then, use the ROUND function to round the tax amount in cell J22. For that write down the following formula into the cell.

=ROUND(J20*10%,0)

  • Press the Enter.

Estimating Final Bill to Create Proforma Invoice

  • Similarly, use the same function’s formula in cell J23.

=ROUND(J20*10%,0)

  • Again, press the Enter.

Estimating Final Bill to Create Proforma Invoice

  • At last, merge the range of cells H28:I28, and entitle it as TOTAL AMOUNT.
  • Then, write down the following formula into cell J28 to calculate the final bill of this shipment.

=SUM(J20:J23)

  • Press Enter.

Estimating Final Bill to Create Proforma Invoice

  • After that, add two signature sections for both the shipper and the consumer.

  • In the end, include a disclaimer for the shipping agent and other authorities to ensure the smooth operation of the whole transaction.

  • Our proforma invoice is ready to use.

How to Create Proforma Invoice in Excel

So, we can say that we have finished the final step to create the proforma invoice in Excel.

Read More: How to Create a Cash Bill Format in Excel (A step-by-step Guideline)


Step 4: Verify Proforma Invoice with Data

Here, we will verify our proforma invoice with some sample data. In addition, we will check the accuracy of our using formulas.

  • Input some sample data as shown in the image.

Verify Proforma Invoice with Data

  • In addition, we also add $200 as the Freight Charges and $200 as the Insurance for the products.
  • We will see that the complete bill will be ready within a second.

Verify Proforma Invoice with Data

Thus, we can say that our proforma invoice worked accurately, and we are able to create the proforma invoice in Excel.

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


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to create a proforma invoice format in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo