Skip to Main Content

Escape from Spreadsheet Hell

The website for the workshop! TL;DR we’ll teach you how to create spreadsheets optimized for reuse by you and others. Your future self will thank you.

Data validation

The Data Validation tool in Excel allows you to control what is entered into a cell based on a set of rules. This is a particularly useful feature to use when performing data entry. You can find this tool in the Data tab of Excel as one of the Data Tools provided.

To check input data against a list of allowed values, under Data Validation - Settings pick Validation criteria to Allow as "List" and provide a range of cells containing the desired list under Source. 

Pick Validation criteria to Allow as "List" and provide a range of cells containing the desired list under Source

You can require the value of a numerical input to be within a chosen range of values, set a maximum number of characters for text input, or other options.

 

Data Validation also has a feature to display a pop-up message when a chosen cell is clicked.  Use the Input Message tab of Data Validation, click the checkbox to enable this feature, and type the desired reminder note under "Title" and/or "Input message."

What you type under "Title" will be in bold text. What you type under "Input Message" will be in regular text.

 

You can also customize the Error Alert that displays if information entered in the cell is not of the correct type. 

Error alerts can be customized, including a choice of warning icons.