If you’re planning to create an Excel sheet for others to enter data into (for example, a survey or a form), there’s a simple way to check the validity of data they enter. If users enter information in some other format, Excel will prompt them to type the correct information in each cell. For example, in a cell where they require to type their age, you can prompt them to enter an age within a specific range (say, 40 to 75) when they click the cell. If by mistake they enter a number outside that range, you can set a pop-up alert to appear assisting them to enter a number between 40 and 75.
In this post, We’ll show you how to set these prompts in your Excel sheet.
- First, click inside the cell where the user’s age will be entered. If you want to perform the check on a number of cells, select them all. Next, click the Data tab, then select the Data Validation option under the Data Tools section.
- In the new window that opens, you’ll see three tabs – Settings, Input Message and Error Alert. In the Settings tab, set the rules you want using the drop-down menus. You can force users to enter numbers, dates within a specific range. You can also define the minimum and maximum length of text users can enter in the cell.
- Next, click the Input Message tab and write the message you want to display when someone clicks that cell. If they fail to insert the values you requested for in the Settings tab; they’ll see the message you typed in the Error Alert field as a pop-up window.