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.

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:

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.

How to Create a Form with Drop Down List in Excel: Easy Steps

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.

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:

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:

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.

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.

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.

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 Balance – Initial 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

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

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.

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.

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.

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.

💬 Things to Remember

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

Download Practice Workbook

Download the practice workbook.

Create a Form with Drop Down List.xlsx

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.

Keep learning new methods and keep growing!

<< Go Back to Create Drop-Down List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!