Budget Limit

 

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

1. Select the range B2:B8.

Budget Limit Example in Excel

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.

Click Data Validation

3. In the Allow list, click Custom.

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

Data Validation Formula

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.

Formula Check

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.

Budget Limit Result

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