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.

validation criteria > list

Now under the list Source there’s two options. The first is to just manually type out the items that will appear in the drop-down list — using a comma to separate items. This is pretty basic, but can be a pain to edit unless all of your cells are using the same items.

Option two is to create a list of items in a series of cells elsewhere in the spreadsheet. This is the method I prefer because it’s easier to manage. And the cells can be hidden from view later your spreadsheet is ready for publishing. To select your list as a source click the selection tool at the right-side of the Source box, or just try to figure out the Excel code using the screenshot below.

(Click either image to enlarge them)

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.

, ,

3 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 :)

Leave a Reply

 

×

Amazon.com Year End Deals! Shop Now