Create a Drop-Down Menu From Multiple Ranges in Google Sheets

Creating an in-cell drop-down menu from an array is not rocket science. In Google Sheets, you can use Data Validation for this. But what about creating a drop-down menu from multiple ranges (arrays)?

It depends on the values to include in the menu arranged in your Sheet. Also, it depends on whether the values are ‘static’, I mean no changes (modification or addition) will occur in the future, or a growing list of values.

I am considering all the above scenarios in my examples. See the examples below to understand how to create a drop-down menu from multiple arrays in Google Sheets.

Examples to In-Line Drop Down Menu from Multiple Ranges in Sheets

To create an in-line drop-down menu, you can approach two methods. Both are using data validation. First of all, understand those basic methods.

In column C2:C11 I have the numbers 1 to 10. In cell A2 (or any other cell), I can create a drop-down menu with the above numbers as the menu items.

Approach 1:

Click on cell A2 and then go to the menu Data > Data Validation.

In that make sure that the “Criteria” are “List from range“. Select the range C2:C11 and your in-cell drop-down menu are ready! There is a second method, i.e. List of items.

Approach 2:

In this against the “Criteria” in data validation select “List of items” and manually enter your values in C2:C11 as comma separated. Here I am going to share a cool tip.

If the values to type in the “List of items” is already in your Sheet (here we have it in C2:C11), you can use the below tip.

You can use the below TEXTJOIN formula in a blank cell to generate a comma-separated list to use in data validation.

=textjoin(",",TRUE,C2:C11)

It will convert the numbers in the array C2:C11 as a comma separated. Copy that numbers and paste it in the above field in data validation. It’s time-saving, right?

Now to the topic, i.e. how to create an in-cell drop-down menu from multiple ranges in Google Sheets. For this, I am going to use the above two approaches. Let me begin with approach 1 above which is more flexible.

Data Validation Drop-Down Range in Two or More Columns

List from Range Approach (Approach 1)

For all the examples, I am using sequential numbers as menu items. This is for my convenient to explain the steps. You can use text strings or dates too.

The range C2:C11 contains the numbers 1 to 10 and E2:E11 contain 11 to 20. Combined, it’s the sequential numbers 1-20. Please note the values are in two remote ranges.

In any spare/blank column combine these ranges using the below formula. I am using cell G2 for this.

={C2:C11;E2:E11}
Combining two columns for a single in-line data validation menu

If the range is growing, then you can include the Filter function as below.

={filter(C2:C,C2:C<>"");filter(E2:E,E2:E<>"")}

Then select G2:G in the data validation “List from range” field. This way you can include a two column list in a single data validation drop-down.

If you have more columns or even that columns are in different range you can use the same method. Just arrange each column one below another, using a formula similar to the one above.

Assume the values to create your drop-down are arranged in multiple adjoining columns in same Sheet or two Sheets within the same file. Then there is a simple formula to arrange the values in a single column to use in the drop-down.

Values in Sheet1 and Sheet2:

Multiple ranges in two tabs for drop-down

Formula and Drop-Down in Sheet3:

=sort(TRANSPOSE(split(TEXTJOIN(",",TRUE,Sheet1!A2:C11)&","&TEXTJOIN(",",TRUE,Sheet2!A2:C11),",")))
Drop-Down menu from multiple ranges in Google Sheets

List of Items Approach (Approach 2)

Here is another way of creating a drop-down menu from multiple ranges in Google Sheets. If the above values are ‘static’, I mean may not change in the future, you can use the above method also in multiple columns.

I mean just remove the outer SORT, TRANSPOSE, and SPLIT functions in the above formula. Copy the generated comma separated values into data validation “List of items” field.

That’s all. Enjoy!

Related Reading:

  1. A Drop-down Menu in Google Sheets to View Content from Any Sheets.
  2. How to Get an All Selection Option in a Drop-down in Google Sheets.
  3. Populate a Full Month’s Dates Based on a Drop-down in Google Sheets.
  4. Create a Drop-Down to Filter Data From Rows and Columns.
  5. Multi-Row Dynamic Dependent Drop Down List in Google Sheets.
  6. Auto Populate Information Based on Drop down Selection in Google Sheets.
  7. Distinct Values in Drop Down List in Google Sheets.
  8. Data Validation – How Not to Allow Duplicates in Google Sheets.
  9. The Best Data Validation Examples in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. You are a genius, you are really good! Thank you for making me appreciate Sheets so much.

    I have noticed that unlimited ranges can also be used without problems (A: A). In your example, you can easily put it like this: Sheet1! A2:Z (or more columns), Sheets2! A2:Z (or more columns) …

    Thanks in any case! Greetings from Italy, from the Dolomites

  2. I have a follow-up question on Data Combination.

    ={C2:C11;E2:E11}

    Imagine if the data in E2:E11 are formatted (for example: a different font colour, size, etc) and you intend to retain same data format when combined. How would you go about it?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.