Tip #61 - Excel Data Validation

100 Computer Tips in 100 Days

Tip #61 - Excel Data Validation

In Excel, Data Validation refers to a feature of the program that allows you to limit the type of information that can be entered in a cell. You can limit it to a specific list of words, a range of numbers, only whole or decimal numbers, dates, time, etc. You even get to define the input message that appears when a mouse hovers over the cell as well as the error alert message that would appear if someone enters incorrect information.

This Excel feature is particularly important when you have multiple people entering data and you want to make sure the data is consistent and accurate. If someone has to order a minimum of 10 products and the data entry person enters an 8 in the cell, a warning dialog box appears. As part of the data validation you can customize the warning dialog box to clarify what the mistake is. In this case the dialog box might say “There is a minimum of 10 products. Please reenter the quantity.”

If you want to limit the entries based on a list, you first type the list entries on a worksheet in the workbook, it doesn't have to be the same worksheet. Click on the Data tab and choose Data Validation (Data menu for Apple) and from the Allow list choose List. You will need to identify the sheet and cells that the entries are coming from. You can do this by clicking in the source box, clicking on the appropriate sheet and dragging to select the cells that have the data. You might want to edit the Input Message that the data entry person sees. When finished click OK.  Now when you move your mouse to any cell in the range the Input Message will pop up and the pull down arrow from which you are to choose will appear.

My book, 100 Amazing Computer Tips - Shortcuts, Tricks, and Advice to Help Everyone from Novice to Professional, is available NOW at Amazon.com and Barnes & Noble in both print and ebook formats. There's also a handy link on top right side of this window.

Happy computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.