Budget limit in Excel

In this example, we’ll show you how to use data validation to prevent users from going over budget.

1. Highlight a range V2:V8.

Note: Cell V10 contains a function SUM (SUM), which calculates the sum of the cells in a range B2: B8.

2. On the Advanced tab Data (data) click Data Validation (Data validation).
3. Select item Custom (Other) from the drop down list Allow (data type).
4. Enter the formula shown below in the field Formula (Formula) and press OK.

   =SUM($B$2:$B$8)<=100

   =СУММ($B$2:$B$8)<=100

   Explanation: Sum of numbers in range cells B2: B8 cannot exceed the budget of $ 100. We apply data validation in the range B2: B8 (not in cell B10!), because that's where the summed values ​​are entered. Before opening the data validation window, we selected the range V2:V8, so Excel automatically inserted the formula into all cells in that range. Note that we are using an absolute range reference.

5. To be sure, select a cell B3 And click the Data Validation (Data validation).As you can see in the figure, this cell also contains a formula.
6. Enter value 30 into a cell B7.

Result: Excel issues an error message. It is impossible to exceed the budget with a limit of $100.

Note: Click the tab Error Alert (Error Message) to write the text of the error notification yourself.