How to Create a Form with Drop Down List in Excel

Method 1 – Create Form Area

Select an area on your Worksheet where you will create your custom form.

Select your background color. We chose Black for our form.

Method 2 – Adding Form Elements in Excel

Steps

Merge Cell C5 and D5 to make it a single cell. This is only for aesthetic purposes.

Merge Cell E5 and It will be our input field.

Create the following fields:

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:

Method 3 – Create Drop Down List in the Form

Steps

Click on Cell E7.

Go to the Data From the Data Tools group, click on Data Validation.

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

You will see a drop-down list in the Annual Interest Rate field.

Add the drop-down list to the Number of Compounding Years field. 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.

Click on OK. And you will see the drop-down list in the field.

Method 4 – Adding Formula to Calculate in the Excel Form

The Generic Formula to Calculate Compound Interest:

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

The Gained interest is the extra amount you will earn for the interest rate.

The Generic Formula to Calculate Gained Interest:

Gained Interest = Estimated Balance – Initial Balance

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

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

In Cell E15, type the following formula to Calculate Compound Interest:

=E14-E5

Method 5 – Provide Value in the Excel Form with a Drop-Down List

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. You want to know which compound interest will be the best for the interest rate of 7%.

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 the Estimated Balance for Monthly compound interest, select 12 from the drop-down list.

Your estimated balance will be $20,096.61 after 10 years.

To calculate the 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 Daily compound interest will be the best option for this amount of money. Our form is working correctly.

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

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