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

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.

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.

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

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.

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

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

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.

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

• Similarly, we can add CustomerList like this.

And the CustomerLookup will be like this.

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

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

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

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.

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.

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

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

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

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.

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

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

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

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

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

• 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

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

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

• How to Do Bookkeeping for Small Business in Excel
• How to Create a Cash Book in Excel
• Excel Test for Accounting Interview
• Advanced Excel Test
• How to Create Invoice in Excel with Database

<< Go Back to Excel for Accounting | Learn Excel