How to Create Proforma Invoice for Advance Payment in Excel

In this article, we will describe in detail how to create a proforma invoice for advance payment in Excel. A Proforma invoice is used to request payment from a buyer for goods and/or services delivered. Advance payment means payment must be received before the goods or services will be delivered.

Step 1 – Inserting Company Details

Before creating a proforma invoice it it useful to create a sheet with various company details, so that the same proforma invoice can be easily re-configured for different requirements later.

For example, we created a sheet like this:

To make the details more accessible and controlled, we’ll use the Data Validationtool.

Here, we will insert multiple company names.

Select cell C5.
Open the Data tab >> go to Data Tools.
Click Data Validation then Data Validation again.

The Data Validation dialog box will appear.

Select List from Allow.
Enter the company names separated by commas. Here, we inserted ABC, XYZ, and CDK.
Click on OK.

A drop-down list of Company Names is available in cell D5.

Follow the same procedureto create a listfor Company Tagline.

Follow the same procedure to createa listof Currency inputs.

Do the samefor VAT/Sales Tax.

Repeatfor Color.

Read More:Proforma Invoice Format in Excel with GST

Step 2 – Information for the Proforma Invoice

Now let’s create another sheet to insert all the information required for the proforma invoice.

Enter all the vendor’s address details under Bill To.
Enter all the delivery address details under Ship To.
Enter the Invoice Number, Customer ID, and Date.

As it’s a proforma invoice, many jurisdictions require recording Export & Import information. For this purpose, we created two fields: Shipment Information and Additional Information for Customs.

The section below will hold the line items of the goods or services delivered, including the Description, Unit of Measurement, Unit Price, Quantity, VAT/Sales Tax, and Total Amount.

The next section contains the calculations of all applicable charges and payments including Advance Payment.

Step 3 – Formatting Proforma Invoice for Advance Payment

Let’s format the Invoice to make it more presentable and customer friendly.

Use any Fill Color of your choice. Here, we used Blue color.

We used Thick Outside Borders in all the Headings.

We used some Fill colors in the Product Description rows.

Here is the final output after formatting.

Step 4 – Using Conditional Formatting to Automate Colors

Now, we will automate the Fill Color by using Conditional Formattingand linking the details sheet with the invoice sheet.

Select any cell outside of the invoice.
Enter the following formula and press ENTER:

=Details!$C$25

Blue will be returned, because Blue is selected in the Colorfield in the Details sheet (cell C25).

Now we’ll use Conditional Formatting to change the color of the Headings based on another sheet.

Open the Home tab.
From Conditional Formatting, select New Rule.

The Conditional Formatting dialog boxwill pop up.

Select Use a formula to determine which cells to format.
Enter the following formula:

=IF($P$9="Blue",TRUE,FALSE)

Here, the IF function will check whether the P9 cell contains Blue or not. If the cell contains Blue then it will format the Fill Colors with Blue.

Click on Format.

The Format Cells dialog box will appear.

Select the color of your choice. Here, Blue based on the formula.
Click OK.

Open the Conditional Formatting Rules Manager.
Select Applies to.

Select all the cell ranges to apply the Conditional Formatting.
Insert the following range:

=$B$7:$C$7,$E$7:$F$7,$B$14:$O$14,$B$20:$O$20,$B$28:$O$28,$B$35:$I$35

Click on OK.

Apply the formula for Conditional Formatting of Fill Color in cell K9 too.

Only the Font color of the Invoice of the K9 cell will be formatted.

Here is the Rule for all the colors used in the Details sheet:

Go back to the Details sheet.
Select any color from the list, for example Red.

The Fill Color of the Invoice sheet changes automatically.

Step 5 – Using Formulas to Automate Advance Payment

To automate all the payments and options, we’ll use different formulas and link the details sheet with the invoice sheet.

To insert the invoice Date, use the TODAY function in cell L8 as follows:

=TODAY()

This function will insert today’s date automatically.

Example of Using Proforma Invoice for Advance Payment

Here, we inserted values into the required fields to show the invoice in action. These fields are editable; insert field name cell values as per your choice.