How to Create a Form with Drop Down List in Excel

In Microsoft Excel, you can create various forms like data entry, calculator, etc. These types of forms help you to enter your data with ease. It also saves you a lot of time. Another useful feature of Excel is the drop down list. Typing limited values, again and again, can make the process hectic. But with the drop down list, you can easily select values. In this tutorial, you will learn to create a form with drop down list in Excel.

This tutorial will be on point with a step-by-step guide and proper illustrations. So, read the whole article to enrich your knowledge.


Download Practice Workbook

Download the practice workbook.


Brief Information of Our Form

Here, we are going to create a form of Compound Interest. It will take some value from you and give you the final result.

Our Form will look like the following:

Create a Form with Drop Down List in Excel

 

Before we start, you need to have some basic idea about compound interest. Read the following section to know about it more.


What Is Compound Interest in Excel?

Compound interest means earning or paying interest on interest. Basically, it is one of those popular financial terms. When we think about compound interest, we consider it as gaining money. It increases our savings after a limited period.

In Simple Interest, interest is only estimated from the principal. And also interest is not added to the principal. But, with compound interest, after a separately compound term, the interest accumulated over that span is added to the principal so that the following estimation of interest incorporates the actual principal plus the previously acquired interest.

Suppose, you deposited $1000 to a bank for 2 years. And the bank provides a compound interest of 3% every year.

After one year, your balance will be $1030. Because 3% of $1000 is $30. That’s pretty simple.

But, in the second year, the interest won’t be counted on that $1000. Rather than, it will be counted on your current balance of $1030. That will give you a balance of $1060.9.


Step by Step to Create a Form with Drop Down List in Excel

In the following sections, we will create a form in the Excel worksheet with drop down list. Practice these steps with us. It will surely enrich your Excel knowledge.


1. Create Form Area

First, of all select an area on your Worksheet where you will create your custom form.

After that, select your background color. Here, we choose Black for our form.

Related Content: Create Excel Filter Using Drop-Down List Based on Cell Value


2. Adding Form Elements in Excel

Now, it’s time to create the form elements. Follow these steps:

📌 Steps

  • First, merge Cell C5 and D5 to make it a single cell. This is only for aesthetic purposes.

  • Now, merge Cell E5 and It will be our input field.

  • In a similar way, create the following fields:

Create a Form with Drop Down List in Excel

  • Here, we have to provide these fields with their names. As we are creating a form for compound interest, we need this information from a user:

Create a Form with Drop Down List in Excel

Related Content: How to Create List from Range in Excel (3 Methods)


3. Create Drop Down List in the Form

Now, it’s time to create a drop down list in the form. Follow these steps.

📌 Steps

  • First, click on Cell E7.

  • After that, go to the Data From the Data Tools group, click on Data Validation.

Create a Form with Drop Down List in Excel

  • From the Data validation dialog box, select List in Allow. And in the Source field, type the interest rates. We gave here four interest rates.

  • After that, you will see a drop down list in the Annual Interest Rate field.

Create a Form with Drop Down List in Excel

  • Now, we will add the drop down list in the No. of Compounding Years field. To do that, click on Cell E9.

  • After that, go to the Data From the Data Tools group, click on Data Validation. From the Data validation dialog box, select List in Allow field. And in the Source field, provide three types of compound interest.

1: It will calculate yearly compound interest.

12: It will calculate monthly compound interest.

365: It will calculate daily compound interest.

  • Now, click on OK. And you will see the drop down list in the field.

Create a Form with Drop Down List in Excel

Read More: How to Create Dependent Drop Down List in Excel


Similar Readings:


4. Adding Formula to Calculate in the Excel Form

We almost finished our form. Now, we said earlier, our form will calculate the compound interest. For that reason, we have to insert the formula for calculating the compound interest.

The Generic Formula to Calculate Compound Interest:

Estimated Balance = Initial Balance* (1 + Annual interest rate / Compounding periods per year) ^ (Years * Compounding periods per year)

Now, the Gained interest is the extra amount that you will earn for the interest rate.

The Generic Formula to Calculate Gained Interest:

Gained Interest = Estimated BalanceInitial Balance

Here, in Cell E14, type the following formula to Calculate Compound Interest:

=E5*(1+E7/E9)^(E9*E11)

After that, in Cell E15, type the following formula to Calculate Compound Interest:

=E14-E5

Read More: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)


5. Provide Value in the Excel Form with Drop Down List

Now, here is the scenario. You want to invest $10000 for 10 years in a bank. This bank provides yearly, monthly, and daily compound interests. They also provide 5%,7%,8%, and 10% interest in various occasion. Now, you want to know which compound interest will be the best for the interest rate of 7%.

Create a Form with Drop Down List in Excel

Let’s check it one by one. First, select 7% from the Annual Interest Rate field. To calculate Estimated Balance for Yearly compound interest, select 1 from the drop down list.

Create a Form with Drop Down List in Excel

As you can see, your estimated balance will be $19,671.51 after 10 years.

Now, to calculate Estimated Balance for Monthly compound interest, select 12 from the drop down list.

Create a Form with Drop Down List in Excel

As you can see, your estimated balance will be $20,096.61 after 10 years.

Finally, to calculate Estimated Balance for Daily compound interest, select 365 from the drop down list.

Create a Form with Drop Down List in Excel

As you can see, your estimated balance will be $20,136.18 after 10 years.

So, from these results, we can easily determine that for this amount of money, Daily compound interest will be the best option. Our form is working correctly. So, we successfully created a form with drop down list in the Excel worksheet.

Read More: How to Create a Drop Down List with Unique Values in Excel (4 Methods)


💬 Things to Remember

✎ You can also use this form as a Compound Interest Calculator in Excel. It will work in both ways.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to create a form with drop down list in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo