Excel: Dynamic Dependent Dropdown Lists

In Advanced Excel training, you learned about creating dropdown lists using Data Validation. That was pretty handy on its own… but what if you would like to create a dynamic dependent dropdown list?

For example, below I would like to select a Dessert Type in Column A, and have only those specific desserts appear in my secondary list in Column B.

Two sets of dropdown lists

This can be an incredibly useful tool; imagine lists of cities by state, or students by class, and so on. Lets see how we can set up this slightly more complicated Data Validation.

Exercise File

Interested in following along? Use the exercise file below.

DependentDropdownExercise

Primary List

When you attended Excel: Advanced Formulas with me, you learned all about Data Validation. The first part of this setup will feel just like what we did in training.

1. Select all of Column A.

2. In the Data tab, select Data Validation.

Data tab, data validation

3. In the popup:

      • Under Allow, select List. 
      • Click into the Source box
      • Select the cells with the dessert types: E1 through J1. Click OK.

All the cells in column A now have a dropdown list of dessert types.

Desert Type dropdown

Dependent Dropdowns

So far, this is nothing new… but remember that your goal was to create a dependent dropdown list: i.e. when someone selects “Cake” as a Dessert Type,  you would like only the cake options to appear, and so on.

Named Ranges

1.  Select the entire range that contains the lists: E1 through J12.

    • Notice how Cake has more options than any other type: we will address that later, but for now, cast as wide a net as possible to include all the desserts.

Lists selected, as described

2. In the Formulas tab, Defined Names group, select Create from Selection.

Formulas tab, create from selection

3. Check Top Row and click OK.

Top row selected

This is going to create named lists based on that top row. Which could have a variety of uses… one will be what we will do next.

Apply Data Validation

For Column B, similar to the first step, you want to again apply some Data Validation, but this time, the options you want to appear need to be based off of what users select in Column A.

1. Select all of Column B.

2. Revisit Data Validation: go to the Data tab and select Data Validation.

Data tab, data validation

3. Change Allow dropdown to a List, just like in the first Data Validation

4. For Source, instead of selecting cells, insert the formula =INDIRECT(A1).

    • This is going to reference cell B1 to look at A1, and through the magic of Relative References (also discussed in Advanced Formulas training), each subsequent row will reference the cell to the left of it to access those named lists you established in the “Named Ranges” step.

Data Validation screen, as describe in text

5. If you receive an error message that the Source “evaluates to an error,” click Yes.

    • Don’t worry, this error is not an issue; the formula is currently evaluating blank cells in Column A and doesn’t know what they reference.

Error message

The fun part: try it out!  Select a Dessert type in column A, and look at the customized options that appear.

Secondary dropdown list working in Excel file

We still have work to do, though. Remember how we have lists of different lengths? This is especially apparent when you pick a shorter listed dessert type, like Confections.

This leads to some trailing blank spaces in those named range lists. We will get rid of these blank spaces in the next step.

Another secondary dropdown, showing blanks

By the way, if it looks like you can’t see all your options in that secondary list, there is a slider bar on the right… At this stage, you might have to scroll up on some of the shorter lists. Again, we will fix that shortly.

Dropdown list slider

Remove Blanks

You might remember this trick from a previous Byte, which covers how to Remove Blank Rows in a Document:

Excel: Remove All Blank Rows in a Document

In this case, we are going to remove a few blank spaces with the goal of cleaning up those dependent dropdown lists that contain blanks.

1. Select the range of cells surrounding your lists.

Lists selected, as described

2. In the Home tab, find the Find and Select dropdown and select Go To Special

Find and Select dropdown, go to special highlighted

3. Select Blanks and click OK.Blanks radio button selected

4. Right click over the selected blanks and click Delete. Right click menu, delete highlighted

5. Select Shift cells up and click OK.

Shift cells up selected

That takes care of the rogue blanks in the dropdowns in Column B. Much better!

Secondary list displayed, blanks no longer appear

Cleaning up the Header Row

This is optional, but if it bothers you that your header (in this case cells A1 and B1) both contain unnecessary dropdowns with Data Validation, you can remove those as a last step.

  1. Select Cells A1 and B1
  2. Go to Data tab, Data Validation.
  3. Select Clear All in the lower left, and click OK.

Data Validation Screen

One More Thought

In real life, you would likely set this up with your source lists on a separate, hidden sheet. That would look much cleaner than having all the lists in view, like we did here. But for the simplification of this example, I included them all on one sheet here.

Thoughts?

What do you think, will you use dependent dropdown lists in your workbooks? I would love to hear from you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

    • Caroline Beach

Teams: Breakout Rooms

Breakout Rooms in Teams: I know so many of you have been awaiting the addition of this feature before you were comfortable moving classes and meetings from Zoom to Teams.

Now that Teams Breakout Rooms have launched (hooray!), lets take a look at how they work…

Breakout rooms icon in the menu

Creating Breakout Rooms

You will need to start your meeting in order to access the Breakout Rooms feature. At this time, you cannot set up rooms in advance of the meeting.

In your meeting, you will find a button for Breakout Rooms in the upper right menu of your Teams meeting, represented by two squares.

Breakout rooms icon in the menu

The Breakout Rooms button will toggle out a menu on the right side of your Teams Meeting screen.

In this screen you can control:

A. The number of rooms
B. Assignment of members: automatic vs manual assignment
C. When finished press Create Rooms.

Note: Unlike Zoom, clicking Create Rooms will not move your participants. There will be an additional step.

Create breakout rooms prompt with room settings

Once you create your rooms, a side panel will appear showing the rooms that have not yet been opened. There are a few additional options you can set before you open the rooms.

Breakout rooms screen

Options and Settings

In the upper right of the Breakout Room screen is an ellipses (…). Click on this to access Rooms Settings.

Ellipses options, "rooms settings"

Here you can control:

  • Whether participants will move automatically to rooms when you open them (this defaults to being on).
  • Whether participants can return to the main meeting on their own (this defaults to being off).

Settins: Automatically move participants, and option to allow them to return to the main meeting

Start Rooms

Once your Breakout Rooms are set up and you are ready to move participants, click Start Rooms.

Note: Unlike Zoom, it can take some time, around 30 seconds or so, for attendees to be moved into their rooms.

Start rooms prompt

After the rooms have been started, you can join your attendees in specific rooms by clicking on the ellipses next to each room and selecting Join Room.

Ellipses next to room: join room option

Returning Participants to the Main Room

In the Breakout Rooms menu, where you opened the rooms, will be a new option to Close Rooms. This will return all the participants to the main meeting.

Close rooms button

If you had toggled on the ability for attendees to return to the main meeting on their own, they will have a new option in their Meeting menu:

  • In addition to a Leave button will be a Return button.
  • Be sure they know to click Return if they would like to come back to the main meeting. If they click Leave, they will leave the entire meeting.

Return button added to attendees' breakout rooms

Want to know more?

This post just scratches the surface of breakout rooms. If you would like to see more details and options, take a look at this helpful writeup from Microsoft: Use Breakout Rooms in Teams Meetings

Thoughts?

What do you think, will you use Breakout Rooms in Teams? Or maybe you have used them already? I would love to hear how the feature is working for you!

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Corey Herl
  • Evan Ohlman