Data Validation Percentage Retry

Today I heard from someone who was having problems with data validation, in cells that were formatted in Percent Style. In the workbook, Enable automatic percent entry is turned on (Office button, Excel Options, Advanced, Editing options).

ExcelOptionsAutoPct

In the cells that are formatted as Percent Style, the percent sign automatically appears as you start typing a number.

NumberPctSign

The data validation allows Decimals between zero and 100.

DataValDecimal

Invalid Entries

All goes well if you enter a valid number in the formatted cells. However, if you enter text, or an invalid number, the Data Validation error message appears.

DataValError

If you click Cancel, the cell is cleared. You can type another value in the cell, and the percent sign is automatically added.

However, if you click the Retry button, the cell isn’t cleared. The existing entry, including the percent sign, is highlighted. When you type a new number, it replaces the existing entry, and Excel doesn’t automatically add a percent sign.

The result is a percentage much higher than what you intended. Here, it’s 5500% instead of 55%

PercentageHigh

Retry Percentage Workaround

If you use the Retry button, remember to type the percent sign yourself. Or, click Cancel, to start a new entry.  As a reminder, you could add those instructions to your Data Validation error message.

Or, turn off the Enable automatic percent entry option, and always add a percent sign if typing a whole number in these cells. There’s more information on the Enable automatic percent entry option in the Microsoft Knowledgebase.

__________________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *