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.
Now in the Ribbon menu click the Data tab and then the Data Validation button from the list of Data Tools.
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.
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)
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.
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.
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.
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.