Budget Limit

Your Exercise

Please download here.

This example teaches you how to use data validation to prevent users from exceeding a budget limit.

1. Select the range B2:B8.

Note: cell B10 contains a SUM function that calculates the sum of the range B2:B8.

2. On the Data tab, in the Data Tools group, click Data Validation.

3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.

Explanation: the sum of the range B2:B8 may not exceed the budget limit of $100. Therefore, we apply data validation to the range B2:B8 (not cell B10!) because this is where the values are entered. Because we selected the range B2:B8 before we clicked on Data Validation, Excel automatically copies the formula to the other cells. Notice how we created an absolute reference ($B$2:$B$8) to fix this reference.

5. To check this, select cell B3 and click Data Validation.

As you can see, this cell also contains the correct formula.

6. Enter the value 30 into cell B7.

Result. Excel shows an error alert. You cannot exceed your budget limit of $100.

Note: to enter an error alert message, go to the Error Alert tab.

Google Sites

Report abuse