In this article, I will show you how to use data validation to prevent users from going over their budget limit in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to limit budget in Excel
1. Select the area B2:B8.
Note: cell B10 contains the 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 Allow, click Custom.
4. In Formula, enter the formula shown below and click OK.
Explanation: the sum of the range B2:B8 cannot exceed the budget limit of 100. Therefore, I applied data validation to the range B2:B8 (but not cell B10). Since this is where the values are entered.
5. To double-check the formula, select cell B3 and click Data Validation.
6. Enter the value 30 in cell B7.
Result. Excel will display an error warning. You cannot exceed your budget limit of $100.
Note: to change the error alert content, go to the Error Alert tab.
In addition, you can also view many other excel articles here.
