How do I apply a drop-down list for an entire column?

We begin with a table of Divisions where each Division contains a list of Apps.

The goal is to have the user select from the first drop down list a Division name. Next, the user will select an App from the second drop down list. The key is to only display the apps associated with the selected Division from the first drop down list.

NOTE: In the real world, the list of Divisions and Apps would likely be placed on a separate (possible hidden) sheet, or the list would be placed somewhere far away from the data entry portion of the sheet where the user is not likely to ever find. For this example, we are placing the list close to the user-area to make the demonstration easier to follow.

Creating the First Drop Down List (Division)

The first drop down list will allow the user to select a Division. This one is simple.

We begin by creating a Data Validation drop down list for cell A5 that points to the Divisions listed in cells F4 through H4.

1. Select cell A5
2. Select Data (tab) -> Data Tools (group) -> Data Validation
3. In the Data Validation dialog box, set Allow to List and Source to =$F$4:$H$4
4. Click OK

To get the drop down list to appear for multiple rows, select A5 and copy/paste the contents to adjacent cells (ex: A6:A20).

Creating the Second Drop Down List (App)

As stated earlier, we want the second list of Apps to only show items based on the selected Division from the first drop down list.

Using the OFFSET function

We will use the OFFSET function to select Apps from one of the three listed Divisions.

If youre not familiar with the OFFSET function, it allows us to start from a defined point then moves away from that point by a set number of rows and/or columns. This establishes a new start point. From here, we can select a set number of rows and/or columns.

The OFFSET function has the following syntax:

OFFSET(reference, rows, cols, [height], [width])

• reference the cell or range of cells you want to base the offset (e. the starting point).
• rows the number of rows down or up you want to move. A positive number moves down; a negative number moves up.
• cols the number of columns right or left you want to move. A positive number moves right; a negative number moves left.
• [height] the number of rows you want returned. This must be a positive number.
• [width] the number of columns you want returned. This must be a positive number.

NOTE: For an explanation of the OFFSET function, see this post/video.

Ultimately, we need to create this formula in the Data Validation dialog box.

Since the Data Validation dialog box does not possess the IntelliSense feature that is so helpful when writing formulas, we will write and test our formula in a standard Excel cell.

Once we have the formula working, we will transfer it to the Data Validation tool for its final test.

Establishing the Starting Point

We will always begin our search for Apps from the upper-left corner of our table (cell F4).

Start the OFFSET function as follows.

We need to make this reference to cell A4 as an absolute reference so each copied/pasted version begins from the same point.

Moving Down or Up

Next, we tell OFFSET how many rows to move down or up. The Apps list begins 1 row below our start point, so we assign a value of 1 to the rows argument.

Moving Right or Left

Now we tell OFFSET how many columns to move right or left.

This is determined by the users Division selection from the first drop down list.

For this, we can use the MATCH function to locate the selected Division in cell A5 from the list of Division in cells F4 through H4. We also need to match exactly (0 in the last argument).

NOTE: We only locked the A part of the A5 reference because we dont want to point to other columns when we replicate this to adjacent cells, but we do want to point to other rows when creating additional drop downs. If thats a bit unclear, stick with us because it will become clearer very soon.

If we highlight the MATCH portion of the formula and press F9, we can see MATCH returns a number indicating the discovered position in the search range.

NOTE: Remember to press CTRL-Z to undo your formula back to its original structure.

The Problem with MATCH

A minor issue we have with MATCH is that it counts cells beginning with the value 1.

This means that Productivity is in the first column of the data, Game is in the second column, and Utility is in the third column.

Because were trying to inform OFFSET how many columns to move to the right, these values are incorrect.

If we want Productivity, we dont want to move left or right. If we want Game, we want to move 1 column to the right, and if we want Utility, we want to move 2 columns to the right.

We need to reduce the returned value of MATCH by 1 to adjust for this discrepancy.

Selecting the Height to Return

We dropped 1 row from the start point to land on the first App. We also moved right to the proper Division column.

The tricky part is to determine the number of rows to select. This will be the list of Apps that will drives the second drop down list.

Notice that our three lists do not contain the same number of items.

Temporarily, we will treat each list as if they contained 15 items. This will allow us to see if the [height] argument is working. Later, well make the height selection dynamic so it will return the perfect number of Apps for our second drop down list.

For any columns that contain less than 15 Apps, this will produce empty slots in the Data Validation drop down list. If youre okay with that, you can leave the [height] argument set to the largest expected list length. This could be a problem if one list has hundreds of items while another list has only dozens.

Like I said a moment ago, well start statically then make it dynamic later, so we eliminate the empty slots.

Selecting the Width to Return

We only ever want a single column of Apps to be returned by the OFFSET function, so we will set the [width] argument to 1.

Setting Up Data Validation

Now that we have completed our test formula, its time to use the logic in a Data Validation drop down list.

1. Select cell B5
2. Highlight the OFFSET/MATCH formula displayed in the Formula Bar
3. Copy the formula to memory
4. Press ESC to get out of Edit Mode
5. Select Data (tab) -> Data Tools (group) -> Data Validation
6. In the Data Validation dialog box, set Allow to List
7. In the Source field, paste the recently copied OFFSET/MATCH formula
8. Click OK

Testing the OFFSET/MATCH Formula

If we select a Division from the first drop down list (A5), we then see the list of related Apps in the second drop down list (B5).

Because we are using a fixed [height] argument, we see blank slots for any Division where the Apps list is less than 15.

Getting the Perfect Height

To get a list of Apps that do not display blank slots at the bottom, we need to calculate the height of the selected Divisions Apps.

We need to take the previously hard-coded 15 for the [height] argument and make it dynamic.

Enter the COUNTA Function

A perfect way to determine the number of Apps in a give Division is to use the COUNTA function.

As a test, we select an unused cell in our sheet and write the following formula:

This yields a result of 15.

If we point the COUNTA function to G5:G19, we get 12, and if we point the COUNTA function to H5:H19, we get 13.

We dont want to hard-code the count range, we want the range to be based on the selected Division.

We can replace the hard-coded range with the same OFFSET formula used earlier.

NOTE: the 15 defined in the [height] argument represents the largest number of Apps we would expect to see in any given Division. You should set this value to whatever number you believe you would never reach, such as =COUNTA(OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) 1, 500, 1).

Testing the COUNTA Formula

In cell A5, if we select Productivity we are returned a value of 15. If we select Game we are returned a value of 12, and if we select Utility we are returned a value of 13.

Updating the Data Validation Formula

Our final step is to update the formula previously placed in the Data Validation tool for cell B5 to include this dynamic logic for [height] calculations.

1. Select the cell that contains the temporary COUNTA formula
2. Highlight the COUNTA/OFFSET formula displayed in the Formula Bar
3. Copy the formula to memory
4. Press ESC to get out of Edit Mode
5. Select cell B5
6. Select Data (tab) -> Data Tools (group) -> Data Validation
7. In the Source field, remove the 15 from the original formula and paste the recently copied COUNTA/OFFSET formula
8. Click OK

Testing the Updated Formula

If we select a Division form cell A5 that has less than 15 Apps, we are no longer presented with empty slots at the bottom of the Apps drop down list (B5).

Copying the Data Validation to Adjacent Cells

To replicate the second Data Validation drop down list to the row cells below cell B5:

1. Select cell B5
2. Press Copy
3. Select your destination cells (ex: B6:B15)
4. Right-click the highlighted range and select Paste Special
5. In the Paste Special dialog box, select Validation and click OK

Final Test

You are now able to select a cell from the Select Division column, select a Division, then click the corresponding Select App cell to be presented with the Apps for the selected Division.

Closing Comment

Granted, I did say that we would use a single formula. I never said it would be short and simple.

The nice thing is that with a small bit of practice, this formula is fairly easy to write. You could even copy/paste it form project to project, reusing it with just minor modifications.

I think its easier to stick to a single strategy for building these dependent drop down lists and get good at it, versus learning dozens of other techniques.

Yes, some of those other techniques may be more efficient or have more features, but unless you need fancy and exotic, this one will do just fine.

Practice Workbook

Feel free to Download the Workbook HERE.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.