Create Drop Down List in Google Sheets from Another Sheet

Google Sheets drop-down list is very useful when you want to enter data from some predefined options. The drop-down list is one of the efficient ways of data entry and can make the process consistent and error-free. In this article, we are going to know how to create Google Sheets drop-down list from another sheet.

A Sample of Practice Spreadsheet

You can download the spreadsheet to practice.

Step-by-Step Procedure to Create Drop-Down List in Google Sheets from Another Sheet

For creating a drop-down list in google sheets by using data from another sheet, first of all, we select a source dataset from where the drop-down list will be developed, then import the data in a new sheet, and finally through data validation create a drop-down list for our target dataset using the source dataset.

The source dataset represents two columns namely Fruits Name and Quantity.

source data for dropdown list in google sheets

And the target dataset which displays on another spreadsheet presents two columns named Consumer Name and Fruit Preference.

Target dataset to create dropdown in google sheets from another sheets

Step 1: Create a Named Range in the Source Dataset

Before creating a drop-down list, we will be naming the data range that we will import from spreadsheet 1. The source dataset is a dataset from where we will use data for the drop-down list.

  • Here, the dataset contains two specific columns. The first column represents the Fruit Name and the second one presents its quantity.

Source dataset to create dropdown in google sheets fron another sheet

  • Create a named range for the Fruits Name column range “Fruits_Name”.to do so, first select the whole column or cell range that you want to name. Then go to the Manage named ranges.

Name range for Source dataset

  • Then change the name. Here, we name the range “Fruits_Name”. Then press Done.

name range for dropdown list in google sheets from anothe rsheet

  • When you select the range, you can see the name range above there.

naming range for dropdown list in google sheets from another sheet

Read More: Create Drop Down List for Multiple Selection in Google Sheets

Step 2: Import Data from Another Sheet

To import data in another sheet some steps should be followed:

  • Open the target worksheet (spreadsheet 2) and create a new worksheet in it. Insert “=IMPORTRANGE (formula into a selected cell.

Insert data from another sheet to create dropdown in google sheets

  • The formula syntax is
  • Here, URL is the unique ID that Google Docs assigns for each spreadsheet.

Insert IMPORTRANGE formula foimporting data in google sheets

  • Press ENTER and you will find the Fruit_Name column import from the source dataset into the new Google Sheets.

import data from another sheet

Note: This step is not necessary if the data is only in a separate worksheet in the same spreadsheet since you can directly apply data validation (Step 3) if the data is in the same spreadsheet.

Read More: How to Remove Data Validation in Google Sheets

Step 3: Create Drop-Down List for the Target Dataset

We already have a target dataset containing two columns, namely Consumer Name and Fruit Preference. In the Fruit Preference column, we will add the drop-down list beside each consumer name so that the user can select it independently.

To create a drop-down list,

  • First, select the cells or column you want to add to a drop-down list.

create dropdown list for the target dataset in google sheet

  • Then go to Data and select Data validation.

use data validation for dropdown list in google sheet from another sheet

  • The apply range is default selected as we select the cells before going to the data validation tab.

Select range for data validation

  • Enter Criteria: Dropdown (from a range) and then go to select a data range.

enter criteria for dropdown list in google sheets

  • Select the data to range from imported data in Sheet2.

select range from another sheet

  • After selecting data, you can find all the values for the drop-down list in the data validation bar. A color customization bar will automatically add to your drop-down values. You can customize the color for each value to mark them separately.

customize color for each value in drodown list

  • Press Done to run the method.

apply dropdown list using data from another sheet in google sheets

  • You can find the drop-down in the desired column that we have selected.

dropdown list add in google sheets by using data from another sheet

  • Now you can select any options for your desired cells.

select desired option value for every cells

Read More: How to Add Color to Drop Down List in Google Sheets (Easy Steps)

Things to Remember

  • For Import data, the URL must be put properly.
  • For data validation, you have to select the ranges carefully.


Now you can easily create a Google Sheets dropdown list from another sheet and can make your dataset more effective for different purposes. To explore more techniques, you can visit our OfficeWheel website. Feel free to comment if you have any queries.

Related Articles

Annyca Tabassum

Annyca Tabassum

Hello, I'm Annyca Tabassum. I currently work for the SOFTEKO as a technical writer and content developer. I have completed my graduation and post-graduation from SUST.

We will be happy to hear your thoughts

Leave a reply