In this tutorial, you will learn how to create drop down lists in Google Sheets.
Google Sheets drop down lists are a great way to add dynamic to your data entry process. By adding drop downs, you make it easier to add frequently-used options into maintained spreadsheets. Although it is not required that you use two spreadsheets to create a drop down list, I have personally found it more effective in the long-term for managing drop downs in larger scale projects. For the sake of this tutorial, our drop down list will be using two different spreadsheets.
Creating Your Drop Down
- Create a new Google spreadsheet.
- At the very bottom left of the spreadsheet you will see a plus sign, a menu icon, and the sheet tab name, which defaults to “Sheet 1”. Click on the plus sign.
- Clicking on the plus sign creates “Sheet 2.”
- Label Sheet 1 the title of your spreadsheet and label Sheet 2 “List Data.”
Drop Down List Data
- In Sheet 1, set up two-columns. Freeze the top row to create headers. Do this by going to View > Freeze > 1 row.
- Column A should list an item/person.
- Column B will be reserved for the drop down that helps describe the item.
- In Column A of your List Data sheet (Sheet 2), create a list of the data you want to include in your drop down.
For instance, if you need a drop down of the colors of the rainbow the data would be: Red, Orange, Yellow, Green, Blue, Purple, Indigo.
Drop Down “List From Range”
Creating a drop down list from range makes it easy to apply the list to multiple cells and create new drop down cells with consistent list options.
- Return to Sheet 1. In column B, select all of the cells in which you want a drop down list.
- Go to Data > Data Validation. A pop-up window will appear where you may enter specifications.
- Next to Criteria, select “List from Range.”
- There will be a blank text box next to this option where you can input the range where the drop down list will be sourced.
- With the pop-up window still open, go to the List Data sheet (Sheet 2).
- Click in the range input box and then highlight the cells containing the list data.
- Click “Okay.” This will input the range into the text box.
- Select “Show dropdown list in cell.”
- Underneath this checkbox is “On invalid data.” This will tell Google Sheets how to handle data input into the drop down column on the primary sheet that is not part of the drop down list.
- “Show warning” will tell you that it is not part of a list. “Reject input” will not allow you to input the data.
- Click “Save.”
Drop Down “List of Items”
You can also create a drop down menu by directly inputting a list of items. This works better for smaller scale projects.
- Select the cell in which you would like to create a drop down menu.
- Go to Data > Data Validation.
- Next to Criteria, select “List of Items” from the list.
- Input a list of items in the text box next to your selection.
- Choose your invalid data option and click “Save”.
Once you’ve created a drop down menu, it’s important to test it and make sure it’s working properly. To do this, return to the primary sheet. You should sheet a small arrow in the right hand corner of the cells. This indicates that you have successfully created a drop down menu.
Learning how to create drop down lists adds an instant cool factor to your Google Sheet skill set. It’s program knowledge that puts you ahead of the game. And f you are tasked with maintaining master lists, this makes your data entry life that much easier.