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

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 30, 2023

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

 

createform autoform

 

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

 

createform quickaccess

 

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

 

createform customize quickaccess

 

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

 

createform quickaccess toolbar

 

Create a Form

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

 

createform form

 

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

 

createform dropdown

 

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.

 

createform data validation

 

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.

 

dataentryform gs form menu

 

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

 

dataentryform gs form name

 

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

 

dataentryform gs form drop down

 

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

 

dataentryform gs form preview button

 

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

 

dataentryform gs form preview

 

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

 

dataentryform gs form dropdown preview

 

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

 

dataentryform gs form send

 

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

 

dataentryform gs form responses

 

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

 

dataentryform gs form manage

 

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.

 

dataentryform gs form q1

 

Date

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

 

dataentryform gs form q3

 

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.

 

dataentryform gs form q4

 

Required Questions

Clicking the Required toggle button makes a question compulsory.

 

dataentryform gs required

AI Formula Generator

Try for Free

See all How-To Articles