Budget Restriction in Excel

In this budget restriction example, you learn how to design a spreadsheet, wherein a user is prohibited from entering values in cells that exceed a budget limit set previously.

1. In cell C12, type in “=SUM(C4:C10)” to calculate the total cost of the party.

2. Select the cell range C4:C10.

3. From the Data tab, click “Data Validation”.

3. From the Settings tab of the Data Validation dialog box, select Custom and then type in a rule in the formula box, which says that the sum of the range C4:C10 can not be greater than 250.

4. Click the Error Alert tab. . Type in the title as “Birthday Party Budget” and the Error message as “You are exceeding the Budget limit of $250”.

5. Click OK.

6. Select cell C9. Try to enter a value that will cause the total cost to exceed $250. For instance, we enter 100 in cell C9.

7. Press Enter.

Result: Excel shows the warning message for the user as you have set.

Note: To remove all the data validation rules, select the cell range and then click Data tab -> Data Validation -> Clear All -> OK.

3 of 8 finished! Recommending more on Data Validation: Next Example >>

<< Previous Example | Skip to Next Chapter 09: Using Templates