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 easy and user-friendly to use. In this article, we’ll try to discuss how to create an order form in Excel.


How to Create an Order Form in Excel: with Useful Steps

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


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 a 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.


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


4. Initiating Order Form

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

  • Firstly, write the Order Form in the B4 cell.
  • 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


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 column.
  • 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.


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


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 cell.
=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.


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 be added 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.


Download Practice Workbook


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.


Related Articles


<< Go Back to Excel for Accounting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo