Table of Contents
Budget Limit
To avoid exceeding a budget limit in Excel simply use data validation and the SUM function.
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 lock 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.
