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.
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 is ticked.
- Fourthly, click OK.
- Eventually, we’ll see that the drop-down options are added to every column header.
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.
- Similarly, to create 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.
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.
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.
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()
- Thirdly, press ENTER and get today’s date.
- Fourthly, insert column headers which we need for the order form.
Read More: How to Create a Fillable PDF from Excel (2 Suitable Ways)
Similar Readings
- How to Create a Data Log in Excel (2 Suitable Ways)
- How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)
- Create Data Entry Form in Excel VBA (with Easy Steps)
- How to Restrict Data Entry in Excel Cell (2 Simple Methods)
- Create an Autofill Form in Excel (Step by Step Guide)
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
- Now, if we choose an option, suppose Television, it will add to that cell.
- 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))
- 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.
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)
- 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)
- 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.
- 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.
=IF(B6="","",VLOOKUP(B6,CustomerLookup,2,FALSE))
- 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) & ", " &
- 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.
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
- How to Create a Printable Form in Excel (2 Easy Ways)
- Types of Data Entry in Excel (A Quick Overview)
- How to Record Time of Data Entry in Excel (2 Effective Ways)
- Create Excel Sheet from Google Form (2 Useful Methods)
- How to Populate an Excel Spreadsheet from a Web Form
- Create an Excel Data Entry Form That Includes Checkboxes
- How to Check for Data Entry Errors in Excel (2 Easy Ways)