2022 Easy Dropdown Lists Using Excel
OVERVIEW:
Drop-down lists allow us to make cell input easier. By creating dynamic drop-down lists, your options can grow.
TIMESTAMPS:
0:00 Overview
0:13 Creating a List and Table
0:55 Creating a Named Range
1:23 Using Data Validation
3:21 Using the Dropdown List
3:48 Trying to Enter Values Not in the List
3:58 Adding to your Dropdown Options
4:27 Sorting the Dropdown List Alphanumerically
VIDEO TRANSCRIPT:
Utilizing controlled dropdown lists in Excel is a great way to ensure data integrity; in this Raptor Solutions quick-hit video, we'll show you how to create them.
Let's get started by creating a new, blank spreadsheet. Change the Sheet1 tab to States. In the A1 cell, enter a heading of State and then populate several state abbreviations in the cells below.
To allow for growth of the list in the future, we need to create a table out of the list and then specify a "Named Range". First, select all of the cells in your list including the heading. Then, select Insert. From the Insert menu, select Table. When the "Create Table" dialog box appears, toggle on the option for table headers. Select OK.
You'll notice that the format of your list has changed to a table.
Next, we'll create a "Named Range" by simply selecting the cells of data. Do not include the heading in your selection this time. In the box above the spreadsheet, on the top left, type the word "State" and press Enter. This will create a "Named Range" that can be used from other parts of the worksheet.
And, because its within a table, the selection list can grow if you need to add more states in the future.
Now, we'll put the drop-down list into action. First, we'll create a new tab called Properties and give it a couple of fields; one for Property and one for State.
As we start to populate the information in the tab, you'll notice that the State field is free-form, meaning that we could put in any value. To constrain the entry, we want to utilize the list we created on the prior tab.
To begin, we'll establish a "Data Validation" for the State column. To do so, select the entire column by selecting the "B". Then, to include all of the fields EXCEPT for the heading, hold down the "Control Key" and select the cell B 1.
Next, we'll add the validation by selecting "Data" and then "Data Validation".
In the Data Validation dialog box, we'll perform a few actions. First, change the "Allow" option to "List". Next, we'll type "equals" "state" as a simple formula in the "Source" field. That formula tells Excel to get the list from the "Named Range" that we established earlier.
Be sure to keep the defaults for "Ignore Blank" and "In Cell Dropdown" toggled "On".
Before leaving this dialog, we're going to continue on through the other two tabs.
To keep the process as smooth as possible for the person doing the data entry, we'll go to the Input Messages tab and toggle-off the option to "Show Input Message".
Finally, select the "Error Alert" tab. As we want to constrain their entry to only allow those items in the list, we want to keep the "Show Error" option toggled "On" and add a message to the "Stop" style. For the title, we'll just say "Incorrect State Abbreviation". For the message, we'll ask them to select from the list of available states.
Once you select ok, you'll notice that the cell for each "State" entry is now a dropdown list.
When you type in a value, Excel is comparing your entry
to that of the list constrained by the Data Validation.
Excel does NOT honor UPPER or lower case comparisons
to your list.
If the values match (regardless of Case), the entry will be allowed.
So, what happens when someone attempts to enter a value for a state that isn't in your list? They'll see the error that you created in the Data Validation box. No entry will be allowed that isn't from your list.
So, what if you need to now add a state to your list? You can do so easily by going to the "States" tab and adding to the bottom of the state list. Because we created our list as a table and specified a Named Range within that table, the list can expand quite easily.
Once you've added the state to your list, That State will now be available as a selection from the dropdown list.
If you'd like to make sure that your list appears in alphabetical order, this too is easily accomplished because of the usage of a table. On the States tab, select the little arrow next to the States heading. Simply choose the option to Sort from "A" to "Z".
Now, all of your selections for State will be in alphabetical order.
If you found this tip helpful, please consider "Liking" and "Subscribing"!
CONTACT:
Website: https://www.raptornh.com/
SOCIAL MEDIA:
Instagram: / raptorsolutionsnh
Facebook: / raptorsolutionsnh
LinkedIn: / raptor-solutions1