Excel Campus



TUTORIALS (BLOG)
RESOURCES
COURSES
ADD-INS
CONTACT
ABOUT ME
Excel Tutorial Drop Down Lists in Cells
Tables & Data, Tips, Tips & Shortcuts
How to Create Drop-down Lists in Cells – Data Validation Lists
August 8, 2018Jon Acampora
7 COMMENTS
Bottom Line: The complete Excel guide on how to create drop-down lists in cells (data validation lists). Includes keyboard shortcuts to select items, copying drop-downs to other cells, handling invalid inputs, updating lists with new items, and more.

Skill Level: Beginner



Download the Excel File
You can download the file I’m using in the video here:

Data Validation Lists Examples.xslx (21.0 KB)

What Are Data Validation Lists?
Creating a drop-down list is a great way to ensure that entries are uniform and free from spelling errors. It also helps restrict entries so that only values you’ve approved make it onto the sheet.

That’s why they are also called data validation lists. They help to make sure that only valid data makes it into the cells that you’ve applied it to.

Drop-down Lists in Excel Prevent Input Errors Interactive2

This can be helpful when multiple users are entering data on the same sheet and you want the options to be limited to a list of items or values that you’ve already approved.

We can also use drop-down lists to create interactive reports and financial models, where results change when the user changes a cell’s value.

How to Create a Drop-down (Data Validation) List
To create a drop-down list, start by going to the Data tab on the Ribbon and click the Data Validation button.

Select the Data Validation Button on the Data Tab

The Data Validation window will appear. The keyboard shortcut to open the Data Validation window is Alt, A, V, V.

You’ll want to select List in the drop-down menu under Allow.

Data Validation Window Allow List

At this point there are a few ways that you can tell Excel what items you want to include in your drop-down list.

Drop-down List from Comma Separated Values
The first way is by typing all of the options that you want in your drop-down list, separated by commas, into the Source field. For example, if there are only two options to choose from, such as Yes and No, you would simply type “Yes, No” (do not include the quotation marks) in the Source box. It doesn’t matter whether a space follows your comma or not.

A longer list of options might look like this: “Red, Blue, Green, Purple, Orange, Yellow, Brown”. The options in your drop-down list will appear in the exact same order that you have typed them.

Data Valiation Window Comma Separated List

Note: On some language versions of Excel you will need to use a semicolon ( instead of a comma.

Drop-down List from a Range of Values
The second way to fill your list with options is to choose them from a range of values. To do this, instead of typing values into the Source field, you want to select the icon to the right.

Data Validation Window Select a Range of Values

Selecting this icon will open up a small window that will auto-fill when you select a range of cells on the worksheet. Once you've selected the values you want to appear in your drop-down list, you can click on the corresponding icon to take you back to the Data Validation window.

Data Validation Choose a Range

At this point, the range you've selected will show in the Source box and you can just hit OK.

Data Validation Window Select a Range of Values

Now the values in the range that you've selected show as options that you can choose from in your drop-down list.

Data Validation List Drop-down Source Range.

Shortcut for Selecting from the Drop-down List
To choose the option you want from your drop-down list, you can use your mouse to click on the option you want. Another way to select it is to use the keyboard shortcut Alt+?. This brings up the drop-down list and you can use your up and down arrow keys to highlight the selection you want, and then press Enter to select.

Keyboard Shortcuts for Drop-down lists

How to Search the Drop-down List
Unfortunately, Excel doesn't have an option to search the drop-down list for a particular item, but I've created an add-in that gives you that option. It's called List Search and you can access that add-in here:

List Search Add-in Free Download 640x360

Click here to download the List Search Add-in

Note: You will create a free account for the Excel Campus Members site to access the download and any future updates. The download site also contains installation instructions and videos.

How to Copy the Data Validation List to Other Cells
If you have created a drop-down list for a particular cell and would like other cells to have the same data validation list, you can easily copy (extend) that list to other cells.

Start by clicking on the cell that has the list, and then select any additional cells that you want to extend the drop-down list to. This can include blank cells or cells that already have values in them.

Extend Data Validation Selection

As before, you will click on the Data Validation button in the Data tab, but this time a warning will appear that says, “The selection contains some cells without Data Validation settings. Do you want to extend the Data Validation to these cells?”

Information Box Extend Data Validation to Other Cells

Choose Yes, and then hit OK when the Data Validation Window appears. You'll see that each of the cells in your selection now has the same drop-down options as the original cell.

Extend Data Validation

It's also worth noting that you can copy and paste Data Validation from one cell to another just as you would copy and paste normal values and formatting.

Copy and Paste Data Validation

Handling Errors and Invalid Inputs
What happens when we enter a value into a cell that has a Data Validation List, but that value is not one of the options in the list? That depends on the Error Alert settings, which we have control of.

Data Validation Window Error Alert Style

To change the kind of message the user receives when they enter an extraneous value, you can go back to the Data Validation window. Under the Error Alert tab, you can find three options: Stop, Warning, and Information.

You’ll also notice that there are fields where you can change the title of the error message and the text of the message itself, so that when the user enters data that’s not part of your validation list, they will receive an alert that’s worded in the way you want it to appear.

Here is an explanation of each Error Alert Style:

Stop Style
When the user types an invalid entry, an error message will appear that gives the option to either retype the entry or cancel the attempt. The message looks like this:

Stop message Value Doesn't Match

Warning Style
The Warning style displays a message that gives the user a choice to allow an entry that isn’t on the preset list.

Warning Style for Data Validation Message

Information Style
The Information style displays a message that automatically allows the entry no matter what the value is. The user is presented with informative text about validation rules.

Information Style for Data Validation Message

Error Checking Alert
When any invalid entry is made in a cell, the error checking alert will appear in the cell. The error is indicated with the green triangle in the top-left corner of the cell. Clicking the Error Box button will allow you to see more info about data validation error. You can select “Display Type Information” from the list to see the cause of the error.



Disable Error Alerts

Another option under the Error Alert tab is to uncheck the box that says, “Show error alert after invalid data is entered.” This allows any value to be entered into the cell, and no message box will appear.

Data Validation Window Error Alert Allow Invalid Entries

Adding New Data to the Source Range of the List
Adding new options to our drop-down list is possible, but it isn't automatic when we add new items the bottom of our source list. We need to tell Excel what our new extended source range is. You can do that in the Data Validation window by just typing in the new range, or re-selecting the range to include the new data. (See the section above entitled “Create a Drop-Down List from a Range of Values” for how to select your range.)

The great thing is that we don't have to redefine these settings for each cell that has Data Validation. The “Apply these changes to all other cells with the same settings” checkbox does this for us. When you click the checkbox, the other cells will selected in the background. This gives you a visual indication of what will be updated.

Data Validation Window Apply Settings to Similar Cells

Then press OK. Any cells that shared the same data validation settings will now include the updated changes that you’ve made.

There is a way to automate the process so that any change you make to the source data instantly updates your drop-down list. It involves using Excel tables and named ranges. You can find out how in this post:

How to Add New Rows to Drop-down Lists Automatically – Dynamic Data Validation Lists

Removing Data Validation from a Cell
Getting rid of a Data Validation list is simple. Open the Data Validation window and click the Clear All button.

Clear or Remove Data Validation List

If you want to clear the validation settings from other cells with the same settings, make sure to click that checkbox before hitting the Clear All button.

Make Your Workbooks Interactive
Data Validation lists are a great tool to add to your Excel toolbelt. They help us keep our data clean and make our spreadsheets easier to use. We can use them as the source of lookup formulas to create interactive financial models and reports. I will do some follow-up posts with these techniques as well.

Once you feel comfortable with drop-down lists, you may want to try dependent (also called cascading) lists. These are lists that change depending on what you've already chosen in another list. For example, you may create a list of car brands, like Toyota, Ford, and Honda. Then you can have a second list of car models that populates with specific options depending on what you choose in the first list. If you choose Toyota in the first list, you might see Corolla, Camry, and Tacoma in the second. But if you go back to the first list and choose Ford, the options in the second list can change to Mustang, Explorer, and Focus. Learn how to create dependent cascading lists here.

If you have any questions or comments about how to use drop-down lists, don’t hesitate to leave a comment below. Thanks! �

PREVIOUS
How to Add New Rows to Drop-down Lists Automatically – Dynamic Data Validation Lists
NEXT
The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method
YOU MAY ALSO LIKE
Secret Excel Menu for Right Click Paste Values 640
Paste Values with the Right-click & Drag Mouse Shortcut
5 Keyboard Shortcuts to Paste Values in Excel
5 Keyboard Shortcuts to Paste Values in Excel
4 Ways to Automatically Sort Drop Down Lists Excel 640
How to Sort Drop Down Lists Automatically in Excel
Split Screen View Video Thumb 640
Tips for Split Screen in Windows & Mac
7 COMMENTS
Your email address will not be published. Required fields are marked *

Comment


Name *

Email *

Website

Avatarhanan
August 14, 2018 at 12:00 pm
Awesome! Thanks so much..

REPLY
AvatarJON ACAMPORA
August 14, 2018 at 12:06 pm
Thanks Hanan! �

REPLY
AvatarLawal
August 14, 2018 at 5:39 am
You are a gift to humanity and God will continue to pave way for you.Thank you so much I have learn a lot from your free excelcampus.

REPLY
AvatarHappy Excellist
August 12, 2018 at 2:47 am
Thanks, Jon. The keyboard shortcuts info was particularly useful. Look forward to your name ranges video.

REPLY
AvatarJON ACAMPORA
August 13, 2018 at 8:31 am
Awesome! Thanks so much! The next video should be out tomorrow. �

REPLY
AvatarMonther Jubran
August 9, 2018 at 2:44 pm
Great video, Jon. When are you planning to publish the second video on name ranges, etc..? Thank you

REPLY
AvatarJON ACAMPORA
August 10, 2018 at 8:38 am
Thank you Monther! We are just finishing it up and will have it out next week. Have a great weekend! �

REPLY

Type here to search...

JOIN US & LEARN EXCEL


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

ABOUT ME
Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

BLOG CATEGORIES
Add-ins
Charts & Dashboards
Functions & Formulas
Keyboard Shortcuts
Pivot Tables
Power Pivot
Power Query
Tables
Tips & Techniques
VBA & Macros
TUTORIALS
COURSES
ADD-INS
DOWNLOADS
ABOUT
CONTACT
MEMBER LOGIN

© 2019 EXCEL CAMPUS. ALL RIGHTS RESERVED.