Auto populate cells based on drop down box selection Google Sheets

Watch Video – Creating a Google Sheets Drop down List

In this tutorial, you’ll learn how to add drop down options in Google Sheets using data validation.

Drop Down lists can be used in data entry where you want the user to select from predefined options only. This also ensures that there are no errors (such as misspelled words).

You can also use drop-down lists in Google Spreadsheets to create interactive charts, to-do lists, and dashboards.

For example, you can create a drop-down with a to-do list where the user can select from options such as task completed, task-in-progress, yet to start.

Another (advanced) use of the drop-down list can be to extract data for the selected item. For example, suppose you have a list of students and their marks in five subjects.

You can use the drop-down menu to select the name of the student and use the formulas to automatically extract the score details of that student.

You will also learn how to copy drop-down lists from one cell and paste it in other cells. You can also easily remove all the drop downs at once from all the cells.

Google Sheets has some useful features that help you be more efficient when it comes to data entry.

Drop-down lists are one such feature, which allows you to quickly select from a pre-filled list. This way, you don’t have to manually enter the text – simply select it from a drop-down list (as shown below).

Whether you’re making a budget for your monthly expenses or calculating grades of every student in the class, a drop-down list can be a helpful and quick way to do that.

The upside to making a drop-down list is that it’s quite simple and gives immediate results.

It also makes sure that your data entry is error-free as you’re allowing a user to select from a drop-down list instead of entering the data manually (which may lead to misspelled words or incorrect format).

• How to Create a Google Sheets Drop Down List
  • How to Add a Drop Down Menu in Google Sheets Using a Range of Cells
• How to Add Drop Down List In Google Sheets by Manually Specifying the Items
  • How to Insert Dropdown in Google Sheets
• How to Make a Drop Down in Google Sheets by Copying Another Drop-Down List
• How to Change or Remove a Drop-Down Menu In Google Sheets
• FAQs for How to Create a Drop Down List in Google Sheets
  • What Happens When You Insert Data That Wasn’t in the Drop Down List?
  • How Do I Alphabetize a Drop-Down List in Google Sheets? / How Do I Sort a Drop Down List?
  • How Do You Color Code a Drop-Down List in Google Sheets? / How Do You Associate a Drop-Down List With a Color in Google Sheets?

How to Create a Google Sheets Drop Down List

In this tutorial, I will show you how to create and use drop-down lists in Google Sheets.

How to Add a Drop Down Menu in Google Sheets Using a Range of Cells

You can create a drop-down list in Google Sheets by using a dataset that is already there in a range of cells.

Suppose you have a dataset as shown below and you want to create a drop-down list in cell C2 (you can also create these drop-downs in multiple cells at once).

Below are the steps to create a drop-down list in Google Sheets:

1. Select the cell (or range of cells) in which you want the drop-down list
2. Click the ‘Data’ option in the menu
   
3. Click on the ‘Data Validation’ option
   
4. In the Data Validation dialog box, make sure the right cell/range is selected. If you want, you can change the cell/range in the Data Validation dialog box.
   
5. In the Criteria option, select the option – “List from a range”
   
6. In the field next to it, enter the cell/range address in which you want the drop-down menu. The best way is to click on the ‘Select data range’ icon and then select the cell/range manually from the sheet
   
7. Make sure the “Show dropdown list in cell” option is selected. If this is unchecked, you will not see a drop-down icon in the cell/range where these have been added.
   
8. Click on Save.

Now you have the drop-down lists in the selected cells. You can click on the drop-down icon (small downward pointing arrow) and it will show you all the options from which you can select. And as soon as you make a selection, that value is added to the cell.

How to Add Drop Down List In Google Sheets by Manually Specifying the Items

In case you don’t want to have the list of items in a range in the worksheet and instead specify these manually, you can also do this.

This could be the case when you want to have options such as Yes/No or Good/Ok/Bad as the items in the drop-down. Instead of having these somewhere in the sheet, you can easily specify these while creating the drop-down list.

How to Insert Dropdown in Google Sheets

Below are the steps to create a drop-down list by manually specifying the options:

1. Select the cell (or range of cells) in which you want the drop-down list
2. Click the Data option in the menu
3. Click on the Data Validation option
4. In the Data Validation dialog box, make sure the right cell/range is selected. If you want, you can change the cell/range in the Data Validation dialog box.
5. In the Criteria option, select the option – “List of items”
   
6. In the field next to it, manually enter the items (separated by a comma) that you want the drop-down to show. In this example, since I want to show Yes/No, I would enter – Yes/No
   
7. Make sure the “Show dropdown list in cell” option is selected. If this is unchecked, you will not see a drop-down icon in the cell/range where these have been added.
   
8. Click on Save.

Now when you click on the drop-down icon in the cell, it will show the options Yes and No in the drop-down.

How to Make a Drop Down in Google Sheets by Copying Another Drop-Down List

After you add a drop down to a Google Sheet, You can easily copy and paste the drop-down list from one cell to another.

All you have to do is select the cell where you have the drop-down menu already, copy the cell (Control + C) and then paste it on the cells/range where you want it.

As soon as you do this, the in-cell drop-downs will be available in the copied cells as well.

In case you only want to copy the drop-down list from a cell and not the formatting (such as color or border or number format), follow the below steps:

1. Select the cell from which you want to copy the drop-down list
2. Copy the cell (control + C)
3. Right-click on the cell where you want to copy the drop-down
4. Hover the cursor over the Paste Special option.
5. Click on the ‘Paste data validation only’ option.
   

How to Change or Remove a Drop-Down Menu In Google Sheets

In case you have a drop-down list (or data validation rule) in a cell and you want to remove it or edit it, you can do that easily.

Below are the steps to remove the drop-down list from a cell or range of cells:

1. Select the cells from which you want to remove the drop-down list
2. Click the Data option from the menu
3. Click on Data Validation. This will open the Data Validation dialog box
4. In the Data Validation dialog box, make sure the correct cell is selected. If you want, you can change the cells in the dialog box as well.
5. Click on the ‘Remove validation’ option.
   
6. Click on Save.

In case you want to edit the drop-down menu and change it (such as add more items to it or make it refer to some other range as the source), you can do that using the same steps. Just make the changes and click on the Save button.

FAQs for How to Create a Drop Down List in Google Sheets

What Happens When You Insert Data That Wasn’t in the Drop Down List?

There are two potential things that can happen depending on the options you chose when you created the drop down list. If you selected Reject input the data won’t stay in the cell. If you chose Show warning,  a small red triangle will appear in the cell to indicate that there is invalid data.

How Do I Alphabetize a Drop-Down List in Google Sheets? / How Do I Sort a Drop Down List?

All you have to do to alphabetize or sort a drop down list in Google Sheets is sort the column or range that has the validation data in it. To do this:

1. Select the range you want to sort (the source data for the drop down)
2. Navigate to Data > Sort
3. In the pop up menu, decide how you want to sort your drop down list

How Do You Color Code a Drop-Down List in Google Sheets? / How Do You Associate a Drop-Down List With a Color in Google Sheets?

Color coding your dropdown list is a great idea, especially if you want to make user selections easier to identify. You can use conditional formatting to color code your dropdown list. For example, if your dropdown list has a Yes or No options, you could use conditional formatting to display all “Yes” selections in Green and “No” selections in red.

Here are the steps you can follow to achieve this:

1. Select your dropdown list (s)
2. From the Format menu, select Conditional Formatting
3. From the Conditional Formatting sidebar that appears, set your formatting rules.

In our case, you can have two formatting rules. For the first rule, select “Text is exactly” from the dropdown menu under “Format cells if…” and type “Yes”. Select a fill color, say red, from the Formatting style options. Click “Add another rule” and repeat to specify a green color formatting when text is exactly “No”.

Note: If you have numeric value options in your Google Sheets drop down list, you can color code it using the Color scale tab of the Conditional formatting sidebar.

You may also like the following Google Sheets tutorials:

1. How to Make Multiple Selection in Drop-down Lists in Google Sheets
2. How to Autofill Numbers in Google Sheets
3. How to Insert CheckBox (Tick Box) in Google Sheets
4. How to Insert BULLET POINTS in Google Sheets
5. How to Insert a Degree Symbol (°) in Google Sheets
6. How to Insert an Image in a Cell in Google Sheets

How do I make cells auto populate in Google Sheets based on Dropdownlist selection?

How do I auto populate Data in Google Sheets based on another cell?

How do I change cell values based on drop down box selection?

How do I create a dependent dynamic drop