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).
In the cells that are formatted as Percent Style, the percent sign automatically appears as you start typing a number.
The data validation allows Decimals between zero and 100.

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.
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%

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.

Limit the Total Amount Entered in Excel

You can use Excel's Data Validation to limit the total amount that users enter in a group of cells. For example, if budget is $3500, you can prevent entries that will go over that total amount.

  • Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal.
  • The user can enter budget amounts in cells C3:C7.
  • Cell C8 sums the amounts entered in cells C3:C7.
  • Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8).

Add the Data Validation

The data validation is applied to cells C3:C7, because this is where the data entry occurs.

  1. Select cells C3:C7
  2. On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)
  3. Choose Allow: Custom
  4. For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to the amount in the BudgetTotal cell.
  5. Click OK to close the Data Validation dialog box.


Test the Data Validation

After setting up the data validation, you can test it, to make sure that it works as expected.

  1. In cell C7, enter $900, which is higher than the amount remaining.
  2. Press Enter, and you should see an error message.
  3. Click Cancel or Retry, and enter a valid amount.