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.
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.