Data Entry Form w/ Drop-Down List in Excel & Google Sheets

This tutorial demonstrates how to create a data entry form with a drop-down list in Excel and Google Sheets.

 

 

The automatic form feature of Excel is often overlooked as you cannot find the command to create a form on any of the Ribbon tabs available by default. If data entry is part of your day-to-day routine in Excel, then creating forms to help you enter the data can be very convenient. Data needs to be in a table format to use the form feature of Excel.

Add Form Button to Quick Access Toolbar

1. To create a form automatically, first add the form button to the quick access toolbar (or to one of the tabs on the Ribbon).
2. Click the button on the right side of the quick access toolbar in the Excel title bar, and then click More Commands…

 

 

3. Select (1) All Commands from the Choose commands from drop down. Scroll down to find (2) Form. Click the (3) Add button to add the command to the quick access toolbar.

 

 

4. Click OK to add the form button to the toolbar.

 

 

Create a Form

1. Select any cell within the table data, and then click the form button in the quick access toolbar.

 

 

2. You can use the Find Prev and Find Next buttons to move through your entries, as well as using the New button to create a new entry in your table, and the Delete button to remove an entry.

Restrict Form Data Entry

If your table contains a drop-down list, the drop-down list doesn’t show up in the form. However, you can only enter data into that field in the form from that drop-down list.

Have a look at the drop-down list in the table below.

 

 

If you are entering data into the table, you are restricted by the data validation drop-down list in inputting a salesperson. The typed-in name has to be on the provided list.

 

 

If you try to type in a name that is not on the list , you get a data validation error once you click Close.

So, while you cannot see the drop-down list in the data form, any data validation restrictions you have applied in the table also apply in form entries.

Google Sheets With Forms

Google Sheets doesn’t have the same feature as Excel’s forms shown above. However, you can use a Google form tied to a Google sheet and achieve a similar result.

Create Form That Includes Drop Down

1. In your Google sheet, from the Menu, go to Tools > Create a new form.

 

 

2. Then, type in a (1) title for your form, and click the (2) Add button to add a question.

 

 

3. Select Dropdown to allow the user to choose from a prepopulated list, and then type in the entries for the drop-down list.

 

 

4. Once you have completed adding your questions and laying out your form, you can either Preview the form, or click Send.

 

 

Previewing lets you see what the form will look like to the user.

 

 

5. Select the drop down to see the options you added to the list.

 

 

6. When you click Send, you can choose to send the form via email, to send a link to the form or to embed the form in a web page.

 

 

Once people have sent their responses, they are stored in the original Google sheet where you created the form.

 

 

7. You can edit or manage your form by clicking on Tools > Manage Form in the Menu.

 

 

Google Forms – Other Question Types and Options

There are various types of questions you can add to your form.

Short Answer

A Short answer question allows users to enter a small amount of text.

 

 

Date

To add a question that requires the user to enter a date, click Date in the list of available question types.

 

 

Multiple Choice

1. Choose Multiple Choice if you want the user to choose one of the answers provided
2. Then type in the answers to be displayed next to radio buttons.

 

 

Required Questions

Clicking the Required toggle button makes a question compulsory.