Relative Reference in Drop-Down Menu in Google Sheets

Published on

You can now unlock rows and columns in the data validation “List from a range” drop-down menu. It is called relative reference in a drop-down menu in Google Sheets.

Earlier the reference in the drop-down was locked by default. That means Google Sheets was only supporting absolute references by default.

What is the benefit of this new update?

Suppose I have a drop-down menu in cell E10. When I click the drop-down in cell E10, I will see a list of items to select.

If I have created the drop-down using the “List from a range” not “List of items”, with the new update, we can decide whether to change the list when we copy the drop-down to another cell.

As a side note, “List from a range” and “List of items” are two types of lists that the drop-down in Google Sheets supports.

The former uses a list from a cell range, whereas the latter uses a comma-separated list as values within the drop-down menu field itself.

The new unlock rows and columns feature makes my multi-row dependent drop-down list much more flexible and useful.

Let’s learn more about relative reference in drop-down menu in Google Sheets.

How to Use Relative and Absolute References in Drop-Down in Google Sheets

The below two examples will help you learn the use of relative and absolute references in the data validation “List from a range” drop-down in Google Sheets.

Absolute Reference (Locked Rows and Columns) in List From a Range

In a blank Google Sheets, let’s create a drop-down menu in cell B5. For that, we require a list. Here is it to use in E1:E4.

I have got the following values in E1:E4.

Approved
Pending
Rejected
Incomplete

Click on cell B5. Then click on the menu “Data” and then click on “Data validation”. It’ll open the below window (please see the image below).

Click the field containing “Enter a range or formula” which is the field against the Criteria “List from a range”. Then select the range E1:E4.

We want to get an absolute reference, not a relative reference, in the Drop-Down Menu. So change E1:E4 to =$E$1:$E$4.

Absolute Reference in Drop-Down Menu

Click “Save”.

Note:- At present, even if you do not change E1:E4 and click “Save”, Google Sheets will convert it to =$E$1:$E$4.

Now you have a drop-down in cell B5 that contains the list “Approved”, “Pending”, “Rejected”, and “Incomplete” to select.

Right-click on cell B5. Then select “Copy” in the short-cut menu.

Then right-click on cell B6 and “Paste”

Check both the drop-downs. You can see that both contain the same list.

Example to Locked Reference in Drop-Down

What’s the reason?

The reference in the drop-down in cell B5 is not changing when you copy it to cell B6. You can check it by opening the cell B6 drop-down in Data validation.

It is called locked rows and columns (absolute reference) in the drop-down.

Relative Reference (Unlocked Rows and Columns) in List from a Range

This part explains how to get the relative reference (unlocked rows and columns) in a drop-down menu in Google Sheets.

I want to change the reference when I copy and paste the drop-down into another cell or drag it down. Let’s see how to do that.

This time the range E2:G5 has the following horizontal lists.

JanFebMar
AprMayJun
JulAugSept
OctNovDec

In cell B2 let’s create the drop-down menu as below.

Relative Reference in Drop-Down Menu

In the field against “List from a range”, the reference must be =E2:G2, not mere E2:G2.

If you use the reference without the equal sign, Sheets may convert it to absolute.

Copy-paste B2 drop down to B3, B4, and B5.

Example to Relative Reference in Drop-Down

Now when you check, you can see that each drop-down has a different list from range.

The “List from a range” will be as follows.

B2 =E2:G2
B3 =E3:G3
B4 =E4:G4
B5 =E5:G5

This way, we can get a relative reference in a drop-down menu in Google Sheets.

That’s all. Enjoy!

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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

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

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.