Limit Budget Amounts with Data Validation Rules

Use simple data validation rules to limit budget amounts that can be entered in a budget plan. The total budget amount is entered on a different worksheet. On the data entry sheet, the budget item amounts cannot add up to more than that set budget total.

Video: Limit Budget Amounts

This short video shows how to limit the amounts that can be entered in a budget worksheet, using data validation rules. The written steps are below, in the Budget Total Maximums section.

Limit the Budget Total

In this example, there is a set amount for the budget total, on the Admin sheet. As amounts are entered in the budget plan, data validation rules prevent the plan total from going over that limit.

The budget plan amounts are entered on the Budget01 sheet, in cells C4:C9, with a total in cell C10.

If an amount is entered that takes the total over the budget limit:

• a data validation error message appears
• the entry is blocked.

Budget Limit

First, set up the budget limit cell. It could be in a hidden row or column on the same sheet as the budget plan, or on a different sheet.

• In this example, there is a cell named Bgt01Max, on the Admin sheet.
  • For details on setting up named cells, go to the Naming Ranges page
• Enter the budget limit in the Bgt01Max cell.

NOTE: The Admin sheet could be hidden, to prevent changes to the budget limit

Budget Entry Rules

To set up the data validation rule, follow these steps:

1. On the Budget01 sheet, select the green cells, C4:C9, where budget amounts will be entered
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula, use SUM to total the values in the range $C$4:$C$9
5. The result must be less than or equal to the limit entered in the named cell, Bgt01Max:
   •    =SUM($C$4:$C$9)<=Bgt01Max
6. Click OK, to apply the data validation rule

NOTE: The sum is also shown in cell C10, in the table's Total row. The data validation formula sums the item amounts, instead of checking that Total row, in case the Total row is hidden, or the formula there is changed.

Test the Budget Limit Rules

To test the budget limit rules, follow these steps:

• For the Travel budget, type 1500
• Press Enter

Because that amount would push the total over the budget limit, a data validation error message appears:

• "This value doesn't match the data validation restruction defined for this cell."

To continue, click Cancel, or click Retry, and enter a lower amount

• If any amount of 1000 or less is entered in this example, the amount is accepted.

Budget Total Maximums

NOTE: This budget limit example is shown in the video, at the top of this page.

In this example, there is a set amount for the budget total, on the Admin sheet. As amounts are entered in the budget plan, data validation rules prevent the plan total from going over that limit.

The budget plan amounts are entered on the Budget02 sheet, in cells C6:C10, with a total in cell C11.

If an amount is entered that takes the total over the budget limit:

• a data validation error message appears
• the entry is blocked.

Budget Limit

First, set up the budget limit cell. It could be in a hidden row or column on the same sheet as the budget plan, or on a different sheet.

• In this example, there is a cell named Bgt02Max, on the Admin sheet.
  • For details on setting up named cells, go to the Naming Ranges page
• Enter the budget limit in the Bgt02Max cell.

NOTE: The Admin sheet could be hidden, to prevent changes to the budget limit

Budget Entry Rules

To set up the data validation rule, follow these steps:

1. On the Budget02 sheet, select the green cells, C6:C10, where budget amounts will be entered
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula, check that cell C11 is less than or equal to the limit entered in the named cell, Bgt02Max:
   •   =$C$11<=Bgt02Max
5. Click the Error Alert tab, and enter a Title and Error message. This will replace the default data validation error message.
   • Title: Over Limit
   • Error Message: Over limit. Enter a lower number.
   • 
6. Click OK, to apply the data validation rule

Test the Budget Limit Rules

To test the budget limit rules, follow these steps:

• For the Supplies budget, type 2500
• Press Enter

Because that amount would push the total over the budget limit, a data validation error message appears, with the custom error alert:

• "Over limit. Enter a lower number."

To continue, click Cancel, or click Retry, and enter a lower amount

• If any amount of 2000 or less is entered in this example, the amount is accepted.

Get Sample File

To see the budget limit examples from this page, get the Excel Budget Limit Rules workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Data Validation Drop Down List

Named Ranges

Named Excel Table