A drop-down list is a way you can control a user’s input in Excel, to ensure that they don’t make a mistake when entering in data. It can also serve as a helpful way to make your chart more dynamic. In this post, I’ll show you how that’s possible.
Starting with a regular chart
For this example, I’m going to use the following table in Excel that shows historical World Cup attendance between 1930 and 2018. It shows the total, average, and highest attendance at each tournament:
Now, you could chart this out but the problem is that things can get a bit crowded:
Another issue here is because the chart is looking at total attendance along with average and highest numbers, the scales will distort the chart, making it difficult to compare averages and highest attendances. The solution to this is to use a drop-down list where the user can select which metric they want to see.
Setting up the drop-down list
Creating a drop-down list is simple and it involves just going into the Data tab and selecting the Data Validation button, where you can select the List option and enter all the possible selections you want a user to be able to choose from:
The key is to use the user selection and then populate a column with those values. For example, I’ll set a column header so that it is linked to the drop-down selection. That way, if someone selects Total Attendance, that will be the the header for the new column. I will also use the OFFSET function to determine which of the columns that I’m copying the values over from:
=OFFSET(A2,0,MATCH($F$1,$A$1:$E$1,0)-1)
In the above formula, I’m looking for cell F1 (the header that’s referencing the drop-down selection) within the range A1:E1, to see which one of the headers it matches up with. Using the OFFSET function, I can then pluck the value from the correct column. If I copy the formula down, then my new column will be based on the drop-down selection and it will automatically update based on the selection that is made
And that column, which is highlighted in yellow, is now the only one that is used in my chart. Now, the chart is cleaner and only includes the selected series rather than all three of them:
If you liked this post on Use Drop-Down Lists With Charts in Excel to Make Them Dynamic, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In a previous post, I covered how to create a form in Excel. Although I didn’t go over drop down lists specifically, they are one element you could incorporate into them. The problem is that your list can change over time, getting bigger or smaller. And that can make it difficult to maintain if your list isn’t dynamic as it will involve you always having to manually change the range of your drop down list. Otherwise, it could be incomplete or contain blanks. Below, I’ll show you how you can manually change your drop down list in excel and create it without blanks while also making it dynamic so that you don’t need to worry about whether it changes over time.
Setting up the drop down list
First, let’s start with the basics — creating the list. To create a drop down list in Excel, you just need a series of options to choose from. My list is going to be made up of the top 30 places to visit. I’m just going to put those names in a list.
After entering in the list of places into Excel, the next thing I will do is select all the values, and create a named range. This is as simple as just entering a value next to the formula bar, where you see the cell location. I will call this range VacationSpots:
There is no need to add headers or anything else. Just select the values, enter in a name for the list, and hit enter. A longer approach would be to go to the Formula tab and select Name Manager:
Clicking this will show you all of the named ranges in the workbook:
It shows you the named range I created. However, I could also create one from this screen and also Edit my existing range. This is where you would go to make the change manually. Clicking on the Edit button would give you this screen:
As you can, here I can manually change the address as needed in case the list changes. However, this is obviously not optimal as it can be a tedious process if your list changes frequently.
Creating the drop down
Now that my list has been created, I can set up the actual drop down. To do this, I’m going to select a cell and under the Data tab, click on Data Validation. Here, there is a place to enter your list of values:
Under the Allow section, I choose List. And for the Source, I enter the ‘=’ sign followed by my named range, VacationSpots. Now, when I click OK and go to the cell that contains the data validation, this is what I see when I select it:
Clicking on the drop down arrow will show me my list of options, in the same order that they appear in my list:
I can select any of the values and my cell will update accordingly. This is great, but what if I decided to add more items to my list, perhaps 10 or 20 more locations I want to visit? Next, I’ll go the different ways you can create drop down lists in Excel without blanks.
Option 1: Create extra spaces in your drop down list at the very end
Technically this step involves blank spaces, which is not what this post is supposed to be about. However, I just wanted to show you how this could work. If your list has dozens of items, then having extra blanks may not be that big of a deal. For example, say I edit my named range so that it goes to 50 rows. If you do that and include empty cells, this is the biggest problem you’ll face:
My list no longer starts from the top, it goes to the first blank cell. This can be an annoying problem because now it looks like all of my options aren’t there (they are, but I have to scroll up every time). This is probably the main reason people want to avoid having blank values in their lists. If the blank values simply came after all of your selections, that might be more tolerable. But because they impact where your drop down list begins from, it can be a nuisance.
The good news is there is a simple way to get around this. For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren’t empty because they contain a space. And by doing so, the drop down list now starts from the top again. You will still have blank values, but this time they will show at the bottom of your drop down list:
If this is acceptable then you can stop here. If you are still intent on getting rid of any possible blank value whatsoever, then head over to the next option.
Option 2: Creating a table to create a nonblank list
This option is the easiest method for getting rid of blank values. What you need to do here is convert your list into a table. Select a cell on your list, click on the Insert tab and then click Table:
Leave the option for headers unchecked and then click OK. You should see something like this:
By default, Excel will apply its formatting and design but you can change the look of it to make it blend in more with your spreadsheet. You can also re-name the header from Column1. Either way, you can now create a new drop down list from this table. Since the values are in range A2:A31 in my spreadsheet, that is what I will enter for my new Data Validation list:
You can either select the range, or enter it in yourself. But if you enter it, you need to include the $ signs otherwise it will not auto-update properly. Now, I’ll go to my list add ‘New City’ to the bottom of the table. When I do that, the table automatically expands which you can notice because I haven’t changed the design and so the colors change:
And if I go back to the Data Validation settings, my source has automatically been updated:
This is a really easy way to make your drop-down list automatically update without the need for any formulas.
If the table you are referencing isn’t on the same sheet as your drop-down list, then you will need to use the INDIRECT function reference it. For instance, if you have created a table called Table1 (which should contain just one column for your list) on a different sheet, you can reference it the following way:
This will allow you to reference the list even if it is on a different sheet.
Option 3: Using a formula to remove the blanks in your drop down list
If for whatever reason creating a table isn’t an option for you, you can still create a dynamic list using a formula. Here, I’ll go back to creating a named range. Except rather than selecting a fixed set of cells, I will rely on a single formula. First, I’ll go back to the Name Manger. I’ll create a new named range. The formula for this can look a bit complex so I will break it down into parts.
First, I’m going to use the OFFSET function. This is because it can allow me to specify a height and width, which is crucial to making this work. My data starts in cell A1, and that’s where my formula will begin:
=OFFSET(A1,0,0
A1 is my starting point and that is the first argument. The next two arguments are whether I want to offset and move to any adjacent rows or columns. Since I don’t, I leave those values as zeros. It is the next argument that is critical, as it relates to the height. Here is where I will use a COUNTA function. I want to count the number of nonblank values in my list. My formula looks as follows:
=COUNTA(A:A)
I will embed this within my previous formula:
=OFFSET(A1,0,0,COUNTA(A:A)
For the width, I will set the last argument to 1, since I don’t want to include any extra columns. Here is my completed formula:
You always want to used $ signs in named ranges so that they don’t move on you. Now that this is set up, I can use this NewRange as my Data Validation source. And just like with a table, whether the list gets bigger or smaller, my named range and the drop down list will automatically update.
However, what if your list contains some formulas that look blank but really aren’t? Formulas are a great example of cells that can look empty even if they aren’t. The COUNTA function will count these values and you could again be back to square one with additional blank values. One way you can get around this is by counting the cells that are blanks, and subtracting that from the total rows. The formula would look as follows:
=ROWS(A:A)-COUNTIF(A:A,””)
Using this, you should correctly arrive at the number of cells that contain text and that aren’t blank as a result of a formula You can then insert that formula in your named range, in place of the COUNTA formula:
As you can see, this method isn’t the easiest and that is why I left it for the end. However, there are multiple different ways you can create a drop down list in excel without blanks. But it’s important because by removing blanks, it will make your form or spreadsheet look more polished by not having blank values in them.
If you liked this post on how to create a drop down list in Excel without blanks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you need to collect user input in an Excel spreadsheet, you’ll want to be able to minimize errors and typos in the information that you receive. That’s why it’s important to know how to create a drop-down list in Excel as it will limit the selections that someone will have to choose from. Using lists can prevent someone from making a data-entry error which can save you a lot of grief later on when you go to analyze the data. Here are the steps involved in creating a drop-down list:
1. Create the list of items that you want your user(s) to be able to select from
The first step in learning how to create a drop-down list in Excel is to first identify your list of selections. This seems like an obvious step but sometimes people don’t actually set aside a space on their spreadsheet for a list of items and simply hardcode the selections later on. By listing the items, you can easily modify them later and visually see what are the user’s options. In this example, I’m going to give a user a list of stores to choose from:
2. Convert the list into a table
You don’t need to do this but there’s an important reason to do so: if you add items later, the range you select for your drop-down list will automatically update. If you just select a regular range, you’ll have to modify it if you add more options to it later. The goal is to make this as easy as possible. And if you always have to adjust the range for subsequent additions, it’s an easy step to miss.
To create a table, just select your range and on the Insert tab, click on the Table button:
Make sure to include your header in the select and that you tick off that your data includes headers:
Now you’ll likely see some automatic formatting applied to your list that shows it’s now a table:
3. Create a named range
Within the table, where you selections are, create a named range. Just select the cells you want to use (they should be everything in the column) and just assign a name to them. Here’s how you can create named ranges in Excel. Don’t worry if after assigning a named range it doesn’t reflect the name in the reference in the Name Box. Since it’s a table, it’ll still reference the table name. In my example, I created a named range called Stores.
By referencing named ranges, you avoid having to rely on cell references and it’ll make your list dynamic. This is also going to play an important role if you want to have multiple lists, with one based on a prior list’s selections.
4. Create the drop-down list using data validation
Decide where you want your drop-down list will go. Sometimes it’s helpful to highlight it so that it’s easy to distinguish it from other cells. In my example, I’ve highlighted the cell in yellow. Then, click on Data Validation under the Data tab:
From there, select a List for your range. The list should be equal to your named range or the range of cells you want to use for your drop-down selections. I’m going to set this equal to the Stores named range. When using named ranges, always put the equals sign in front:
Now, the drop-down list is ready to go:
You don’t have to use a named range for a list. You can just select the range manually or you can just type the options in:
As you can imagine, this is a very cumbersome approach and can be very time consuming if you have a long list. Unless you’ve got only a few options that will never change (e.g. yes/no), it probably doesn’t make sense to manually enter the selections this way.
A key benefit of using a named range, within a table, is that if you add selections they’re automatically updated. I’m going to add StoreF to my list of stores. And all that involves is just typing that store directly below the last value in the table:
If I go back to my drop-down list, the selection’s already there:
Had I manually entered the drop-down selections, the list wouldn’t update automatically. I would need to adjust it manually. This can obviously save a lot of time if your list will grow over time.
Creating drop-down lists based on prior drop-down list selections
If you’ve got multiple drop-down lists and want to make them dependent on prior selections, this section is for you. The good news is that it’s largely the same approach. If you know how to create a drop-down list in Excel, adding dependent lists won’t be much more difficult.
You can make some very complicated and nested drop-down lists possible by using a combination of tables and named ranges. In my example, suppose not every store sells the same product. So a scenario can be that a customer is placing an order and selects a store and then in their next selection they can select from a product that’s available in that specific store.
What we’ll need to do in that case is create another named range for that specific store. That range will show the products that store has available. Now, unless the number of selections will remain the same (e.g. same number of stores as there are products available in that store) — which I’m going to guess is unlikely — you’ll want to create a separate table. In this example, I’ll create a list of products just for StoreA, and convert it into a table:
I also need to select the list of products and assign a named range to them. For the named range, it’s important that I assign it the name of the store: StoreA. The reason this is important is that this becomes key to my formula in order to link the previous drop-down selection (where I chose a store) to the new named range.
In the next data validation list, I’ll need to use the INDIRECT function and refer to the previous selection (for stores). In my spreadsheet, the cell that contained the store selection was L6, and so my INDIRECT function will need to reference that in the data validation:
Since the range is hardcoded in the data validation settings, if you move your drop-down box you’ll need to update the indirect formulas. Here’s how my second drop-down selection looks now:
By using the INDIRECT function, the drop-down selections for the product category were updated based on the StoreA selection. I’ll create another table for StoreB where only ProductD and ProductE will be available. Now, when I go to select StoreB, these are what my selections look like:
The named range is set to StoreB for those selections. What we can do is drill down even further. Suppose ProductA and ProductB came in various sizes – for simplicity’s sake, I’ll just call them Size1, Size2, and Size3. To do that, I’ll again need to create a table for those selections, and a named range that refers to the product name:
These ranges are called ProductA and ProductB. I’ve put them in separate tables because since there are a different number of options, I don’t want them to be part of the same table. If they were in one table then ProductB would include blanks. And if only selected the first two selections then it wouldn’t automatically update if I added more selections.
Here’s what my new drop-down selection now looks like if I select ProductA:
For the third drop-down selection, I have to again use data validation and use the INDIRECT function and refer to the adjacent cell. If I select ProductB, I only have two sizes to choose from. Here’s how the drop-down lists look in action:
If you liked this post on how to create a drop-down list in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
