A drop down list in Excel allows users to pick a value from a restricted set. By providing user a choice and yet preventing impermissible values, drop down lists help make your spreadsheets interactive and foolproof. While there are more than 1 way to make them, the most commonly used method is using data validation. You can create both static and dynamic drop down lists in Excel – the later re-sizing themselves as newer values are added or existing ones removed. So let’s explore.
Create Static Drop Down Lists in Excel
Before we begin, let’s get familiar with the data validation. As the name implies, this features validates the values entered by the user in a cell. You can limit the values entered by the user to numbers, dates, text of specified length, values based on formulas or a specified list defined by you.
1. Let’s start off with creating a drop-down with a specified list of values. In Excel, the data validation features is located under the Data ribbon menu.
3. In the “Source” box, enter the list of values that you want to users to select from. Remember to separate each value by a comma. For our example, assume we want to ask the user to select the continent they live in. We will further restrict the list of continents to 3 – namely North America, Asia and Europe. To have these values appears as a drop down, enter the values as shown in the image below:
4. Click ok. You will see that a drop down arrow appear. When you click on it, all the values that we entered in the data validation box will appear in the list.
5. As we said earlier, the data validation features prevents entry of impermissible values (ie. values outside the data defined by you). To check if that if working, try entering a value in the cell that is not in the list of drop down values. The moment you type in the value and press enter, the following message box appears.
Create Static Drop Down Lists in Excel using Named Range
Another feature that is useful in creating drop down lists in Excel is the Named Range. Named range, simply put, is a range with a name You select a range of cells, give it a name by typing a name in the “Name Box” field and press enter….and viola ….you can now use that name to refer to that range. You can use it in a formula just as you would do for a range Ex. =COUNTA(continents) would give you the same result as =COUNTA(B3:B5).
We can use the Named Range concept to create drop down lists.
1. Type of list of values in contiguous cells – either vertically or horizontally.
2. Give them a name by entering any descriptive name in the Name Box (we will call ours “continents”).
3. Click on data validation icon and open up the validation form.
4. Select “List” from the validation criteria options and enter =continents in the “Source” field. Press Ok.
You would see that the drop-down created in this manner appears to be the same as the one created in the prior example by typing in values in the source field. By the way, you don’t always have to use a named range to pick the list of drop down values from a range, can also directly enter the address of the range in the source field as shown in the example below:
If you compare the two images above, you would see that the only difference is the while we have used the name of the named range in the former, we have used the address of the range in the later, both functioning as intended.
So far we have covered ways to create a drop down list in Excel by using typed in values, range address and named ranges. In all these examples, the list of values was fixed, ie. once defined, no new values can be added or deleted from the drop down lists. This would be perfectly ok for many scenarios but if you are working with data that is dynamic in nature, the values are likely to change and the last thing we want is for the user to be left stuck with say the same old values in the drop down while the fresh data contains a larger set. Let’s now look at ways in which the drop down lists can be made dynamic so that new items in the drop down appear automatically as fresh values are added.
Create Dynamic Drop Down Lists in Excel
Before we begin, let’s take a look at OFFSET formula. Offset function returns a reference to a range that is offset by a specific number of rows and columns from another range or cell. So the trick to creating dynamic drop down lists lies in determining how many non-blank cells are there in a range and then using the offset function to generate a new range that expands or contracts accordingly.
I would recommend reading about the OFFSET formula.
=OFFSET(A1,1,1) would give you a range that’s 1 row towards the bottom and 1 column to the right of the cell A1 which would be cell B2.
=OFFSET(A1,2,3) would give you a range that’s 2 row towards the bottom and 3 column to the right of the cell A1 which would be cell D3.
However is is not enough since we want a range that changes its dimension to accommodate new data as it filters in. To do this, we need to utilize the remaining two parameters of the OFFSET function ie. the height and width of the new range. Use a positive number for expansion and a negative number for contraction.
=OFFSET(A1,1,1,2,4) would give us a range that’s starts 1 row towards the bottom and 1 column to the right of the cell A1 and has a height of 2 cells and a width of 4 cells. This would result a new range starting from cell B2 and end at E3 ie. B2:E3. Similarly =OFFSET(A1,1,1,10,1) would give us a range that’s starts 1 row towards the bottom and 1 column to the right of the cell A1 and has a height of 10 cells and a width of 1 cell. Now if we could just only tell the formula to pick up height or width value that reflects the dynamic count of drop down values. The formula that allows us to do that is COUNTA which provides a count of number of non-blank cells in a range.
Let’s create an OFFSET formula that resizes dynamically.
The formula used is =OFFSET(B3,0,0,COUNTA(B3:B8),1)
When we put this formula in the source field of the data validation form, the drop down that is generated is now capable of resizing itself as new values are added.
Create two dynamic, dependent Drop Down Lists in Excel
Often times, we need to create two drop down lists, the 2nd list dependent upon 1st list. For example, continuing our example with a slight change – let’s assume that we want to present two options to the user – the first one is the name of the continent and the second the name of the country that the user lives in. The second list (name of the country) is dependent upon the first one (name of the continent). Complicating this scenario is the requirement that the list of new continents and consequently, new countries can continue to grow or shrink as new names are added or deleted.
You will observe that we have created two named ranges, the first refers to the range that contains the name of continents and the second one the name of the countries. Interestingly, there are empty cells in both the range – placeholders for new values that may get added in the future.
Let’s look at the formulas that we will use to create these drop down in Excel.
Formula for the first drop down list : =OFFSET(B5,0,0,1,COUNTA(list_of_continents))
Formula for the second drop down list : =OFFSET(list_of_countries,0,MATCH(E1,list_of_continents,0)-1,COUNTA(OFFSET(list_of_countries,0,MATCH(E1,list_of_continents,0)-1,,1)),1)
As you can see in the above example, as new values are entered, the drop down lists expand and begin to show the added values.
You can download a copy of the workbook with this example of drop down list in excel here or click on the button below
As I indicated earlier, there are numerous other ways to create drop down lists in excel, the approach outlined here is just one of them. Happy discovering !