Budget Limit

Data validation is one of Excel's most powerful tools, allowing you to set limits for input.

For instance, you might limit dates to a specific period or integers to an upper and lower limit using the built-in settings. What you might not realize is that you can enter custom formulas when the built-in settings aren't adequate.

Budgets help control the expenditures even before they actually incur. The concept of budget is to provide a ceiling or cap to expenditures so that departments or cost centers don’t exceed a certain limit.

If we have to draw a budget in Excel then we can use data validation to limit the total spending. And intimate the user that expenditure cannot exceed authorized limit.

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.