Top Nav

Setup Excel 2013 Drop-Down Lists with Data Validation

Data Validation is an effective tool in Microsoft Excel when you need to share a spreadsheet with a team and want to keep the content unified. In cells with validation enabled, creator of the spreadsheet can prompt users to choose data from a list, restrict cells to certain data types, or do a few other things that involve complicated custom settings. In Excel 2013, basic data validation is easy to setup. If you’re looking to learn how to use it, this guide will show you.

Create a Data Table in Excel 2013

First, you need to set up a basic data table in Excel to know what’s being worked with. This is done just by typing into each cell manually or pasting from another source. A list of data can be created along a column or in a row. You can enable data validation for the entire row/column, or just select individual cells to apply it to.

In this example, I want to apply it to all data entered in the B column, so I clicked B to select it.

select the excel column

Now in the Ribbon menu click the Data tab and then the Data Validation button from the list of Data Tools.

data validation button in excel 2013

In the Settings tab of the Data Validation pop-up is where the magic happens. You can set Excel to restrict entries to dates, numbers, decimals, times or a certain length. But if you want a drop-down menu, select the List option.

option 1: write in the listoption 2: source the list

An Input Message is optional. This is just a little tip box that appears when a user selects the cell with data validation enabled. You can enter whatever you want in this box, but generally, it’s a good practice to keep it short and instructional.

optional input message

An Error Alert is also optional. This is just a message that will pop-up when someone tries to enter data that doesn’t match the validation settings. This message can also say anything you want, but best practice is to keep it informational.

optional error alert box

Now with all of the above steps in place, a drop-down menu now appears on the boxes that have data validation enabled. When a cell with data validation is selected a small arrow will appear at the right of it. If users click on this small arrow it will open the validation menu, and let them choose an option from the list of choices entered into the source box from the Settings tab of the Data Validation menu.

data validation example in office 2013

If you found this article helpful, please leave a comment! Also be sure to check out our other Office 2013 tips for more groovy Excel love.

More Reading:

, ,

8 Responses to Setup Excel 2013 Drop-Down Lists with Data Validation

  1. Ranjith March 4, 2013 at 1:18 am #

    Really helpful…! Thank you and keep up the good work…

  2. Bonny April 23, 2013 at 5:30 pm #

    That’s great! I have a quick question please, how could we achieve autocomplete feature with this list and auto dropdown if possible.
    Thank you.

  3. Asuman April 30, 2013 at 7:32 pm #

    Thank you! Straight forward, easy and very quick 🙂

  4. Jacqueline March 12, 2015 at 11:30 am #

    Very easy to follow, thank you!!

  5. Lawrence September 3, 2015 at 9:18 am #

    Nice and so easy to follow. Thanks for the good work. Keep it up Sir.

  6. David October 31, 2015 at 8:24 am #

    Thank you for a quick and easy way to set up drop down menus. Perfect! So easy to follow!

  7. ABDUL RAHMAN January 20, 2016 at 9:51 pm #

    Thanks I found it easy way to learn. Your embedded screen shots are most helpful to understand the subject.

  8. rthie February 18, 2016 at 1:22 am #

    What is the maximum size of this drop down list?

Leave a Reply


Free Learning


Don't miss a single tip, how to or tech news update. Subscribe to my free newsletter and receive updates, right to your inbox.

You have Successfully Subscribed!