How to Create an Order Form in Excel (with Useful Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to create an order form in Excel, then you are in the right place. For international or any domestic business we need to make an order form. Using Excel this task becomes so easy and user friendly to use. In this article, we’ll try to discuss how to create an order form in Excel.


Download Practice Workbook


Steps to Create an Order Form in Excel

Creating an order form is quite easy. We just need to follow some simple steps.


1. Making a Customers List Table

Firstly, we have to make a customer list with which we deal with our business and export or import goods. In the following dataset, we have made a dataset named List of Customers. It has column headers as Customer Name, Street, City, and Province. So, every customer has a detailed address which we will try to input in the order form.

how to create an order form in excel

Secondly, we need to make a table with the list.

  • So, select the cells > go to Home > choose Format as Table > pick any format from the options. In This case, it is Light Blue type.

Making a Customers List Table

  • Eventually, a Create Table window will appear.
  • Thirdly make sure that the table range is our selected cells and My table has headers is ticked.
  • Fourthly, click OK.

  • Eventually, we’ll see that the drop-down options are added to every column header.

how to create an order form in excel

Read More: How to Make a Fillable Form in Excel (5 Suitable Examples)


2. Creating a Products List Table

Now, we need to make a product list. In the following dataset, we have made a List of Products. It has column headers as Product and Price.

Creating a Products List Table

  • Similarly, to create table follow the procedures which we have discussed before and again make sure the data range is our selected cells.

how to create an order form in excel

  • Consequently, another table will be like this.

Read More: How to Create Data Entry Form with Drop Down List in Excel (2 Methods)


3. Naming the Products List and Customers List

Now we need to name the product list and customers list.

  • To do that, firstly, select the cells B5:B9 (without the column header) > click the box just left of the function box and give a name there. In this case, it is ProductList.
  • Secondly, press ENTER.

Naming the Products List and Customers List

Now, give a name to the whole dataset of List of Products.

  • So, again select the cells of B5:C9 > give a name like ProductLookup > press ENTER.

how to create an order form in excel

  • Now, if we want to check the name, click the drop-down button shown in the figure.

  • Eventually, we’ll see that the names are added.

how to create an order form in excel

  • Similarly, we can add CustomerList like this.

And the CustomerLookup will be like this.

how to create an order form in excel

Read More: How to Create Data Entry Form in Excel (Step by Step)


4. Initiating Order Form

Now, let’s start with the main order form.

  • Firstly, write Order Form in the B4
  • Secondly, to add a date, we can use the TODAY function and write the formula in the E4
=TODAY()

Initiating Order Form

  • Thirdly, press ENTER and get today’s date.

  • Fourthly, insert column headers which we need for the order form.

how to create an order form in excel

Read More: How to Create a Fillable PDF from Excel (2 Suitable Ways)


Similar Readings


5. Creating Product Drop-Down

In this step, we need to add a product drop-down button.

  • To do this, firstly, select the cells B10:B14. Here, the number of selected cells can be according to our requirements.
  • Secondly, go to Data > select Data Tools > choose Data Validation.

Creating Product Drop-Down

A Data Validation window will appear.

  • Thirdly, go to Settings > select List in the Allow box > click in the Source box and press F3, from there select productList > click OK.

how to create an order form in excel

  • Consequently, we’ll see the drop-down bars in every selected cell of the Product
  • Now, if we choose an option, suppose Television, it will add to that cell.

how to create an order form in excel

  • Here, Television is added to the selected cell.

Read More: How to Create an Excel Data Entry Form without a UserForm


6. Adding Price Lookup

Now, we’ll add a price lookup in the order form. We need to use the combination of IF and VLOOKUP functions to do this.

Firstly, write the formula in the C10 cell.

=IF(B11="","",VLOOKUP(B11,ProductLookup,2,FALSE))

Adding Price Lookup

  • Secondly, press ENTER and get the output. The Price of Television will automatically be included in the C10 If we give any other item, the subsequent Price will be shown in the C10 cell automatically.

how to create an order form in excel

Read More: How to Automate Data Entry in Excel (2 Effective Ways)


7. Total the Order and Format the Form

In this step, we need to total the order.

  • To do this, firstly, enter the Quantity in the D10 cell manually.
  • Secondly, write the formula in the E10
=IF(C11="","",C11*D11)

Total the Order and Format the Form

  • Similarly, press ENTER and get the output as 2700.

We’ll then fill all the cells with the specific products and calculate their total price.
Now, we want to find out the Total price of all the products in the E15 cell.

  • Eventually, write the formula with the SUM function in the E15 cell.
=SUM(E10:E14)

how to create an order form in excel

  • Then, press ENTER to get the output.

Read More: How to Design Form in Excel (2 Suitable Examples)


8. Creating Customer Drop-Down and Lookup

Now, we’ll add the customer drop-down bar in our order form. Because by doing this we can select any customer from the list and their detailed address will automatically be shown.

  • Firstly, write Ship to in the B5
  • Secondly, select the B6 cell > go to Data > choose Data Tools > click Data Validation.

Creating Customer Drop-Down and Lookup

  • Eventually, a Data Validation window will appear.
  • Thirdly, go to Settings > select List in the Allow box > click the Source box and press F3, it will show the options of pre-named sheets > select CustomerList from the options > press OK.

how to create an order form in excel

  • Eventually, the CustomerList drop-down bar will appear like this. If we select one, say, Volvo, the B6 cell will fill with Volvo.

how to create an order form in excel

  • The filling up of the B6 cell is like this.

  • Now, to add the street name of the customer, write the formula in the G7 cell like this.
=IF(B6="","",VLOOKUP(B6,CustomerLookup,2,FALSE))

how to create an order form in excel

  • After pressing ENTER, eventually, the street name will add like this automatically.

Now, we will add the state name and country name.

  • So, additionally, write the formula in the B7
=IF(B6="","",VLOOKUP(B6,CustomerLookup,3,FALSE) & ", " &

how to create an order form in excel

  • Similarly, if we press ENTER, we’ll see the state name and country name of the selected Customer.

  • Now, if we change the Customer, say, LG, the address will be changed automatically.
  • The address for LG will appear like this.

how to create an order form in excel

So, now our order form is ready to use.

Read More: How to Design Employee Details Form in Excel (Free Template)


Conclusion

That’s all about today’s session. And these are the ways to create order forms. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website ExcelDemy, a one-stop Excel solution provider.


Related Articles

Shajratul Alam Towhid

Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo