Excel Numerical Data Entry
When it comes to numerical data entry, efficiency is key - but not if it comes at the expense of accuracy. This procedure covers a way that you can use the data validation feature to make it impossible for users to enter anything but numerical entries in a cell range. The downside of adding this type of data entry safeguard is that it can possibly stop the flow of your data entry cold. The upside is that you don't have to worry about errant punctuation and letter keystrokes converting cell entries into text that later spawns error values in formulas that reference those cells.
To ensure numerical data entry, you need to use Excel's ISNUMBER function in a formula as part of a Custom type of data validation setting. ISNUMBER is an Information-type function that returns a logical TRUE value when its argument is numerical and a logical FALSE value when it's not numerical. This function uses the following syntax:
=ISNUMBER(value)
Whenever the ISNUMBER function returns a TRUE value as a part of a Custom-type data validation setting, Excel finds the data entry to be valid and allows the data entry. However, whenever the function returns a FALSE value, the program finds the entry to be invalid and rejects the data entry.
Define the numerical data validation that copy with Paste Special to the rest of the range:
- Choose Data → Validation to open the Data Validation dialog box.
- On the Settings tab, select Custom in the Allow drop-down list.
- In the Formula text box, type =ISNUMBER (and then click cell C4 to enter it as the argument before typing a close parenthesis [)] to close off the function.
For this data validation setting, you don't need to add an input message. (Here, you already know what kind of entry you intend to make.) All you need to add is a custom error alert message that helps the user identify the nature of his or her error. - Select the Error Alert tab.
- Leave the default Stop style selected, enter a heading for the message's title in the Title text box, and enter the text for the message in the Error Message list box.
- Click OK to close the Data Validation dialog box.
Before copying the Custom data validation setting to other cells in the range, you need to test it out and make sure that it's truly bulletproof when it comes to text entries.
When you're convinced that your data validation setting is airtight, you can copy the setting to all the other cells in the range where you intend to do numerical data entry. To do this, you first copy the cell with the original data validation setting to the Clipboard (Ctrl+C) and then select the cell range. Finally, you click the Validation option button in the Paste Special dialog box (Edit → Paste Special) and then click OK.
