Limit the Total Amount Entered in Excel

You can use Excel’s Data Validation to limit the total amount that users enter in a group of cells. For example, if budget is $3500, you can prevent entries that will go over that total amount.

DataValBudget

  • Cell F1 contains the total amount allowed for the budget, and the cell is named BudgetTotal.
  • The user can enter budget amounts in cells C3:C7.
  • Cell C8 sums the amounts entered in cells C3:C7.
  • Cell F3 shows the amount remaining (BudgetTotal minus total budgeted in C8).

Add the Data Validation

The data validation is applied to cells C3:C7, because this is where the data entry occurs.

  1. Select cells C3:C7
  2. On the Ribbon, click the Data tab, then click Data Validation. (In Excel 2003, choose Data|Data Validation)
    DataValClick
  3. Choose Allow: Custom
  4. For the formula, use SUM to total the values in the range $C$3:$C$7. The result must be less than or equal to the amount in the BudgetTotal cell.
    =SUM($C$3:$C$7)<=BudgetTotal
  5. Click OK to close the Data Validation dialog box.

DataValTotalLimit

Test the Data Validation

After setting up the data validation, you can test it, to make sure that it works as expected.

  1. In cell C7, enter $900, which is higher than the amount remaining.
  2. Press Enter, and you should see an error message.
  3. Click Cancel or Retry, and enter a valid amount.

DataValBudgetError

_______________________

You may also like...

7 Responses

  1. Toad says:

    Wow. This really is a great tip.

    Thanks, Debra!

  2. Thanks Toad, glad you like it!

  3. Dermot says:

    I like it! I always knew about Data Validation in Access, but never thought of it in Excel, I can’t wait to use it now!

  4. Thanks Dermot! Users can override Excel’s Data Validation, but it’s a good way to point people in the right direction.

  5. begainer says:

    I was trying similar formula such as =Sum($a$1:$e$1)<=$f$1. it works. But I need to copy the same rule to the same range in other rows as well.
    As you know because of $ symbols copy and past do not work. The rule has to be edited in each row. Not a feasible solution as I have to put the same validation rule in 100s of rows.

    So I tried the formula without $ symbols. The formula does not work for the range.
    Any clue how to make it work in the first range and also allow it to be copied and paste to other rows to work with row numbers automatically changed to the row copied to.

  6. Nijee says:

    Hi, can you do this for two columns or rows in excel?

  1. July 26, 2012

    […]    Shared Workbook Limits in Excel 2010 » Control the Budget With Excel Data Validation If you're building a budget in Excel, you can limit the total amount that is entered, to help prevent anyone from going over budget. I shared this tip a few years ago, in this blog post: Limit the Total Amount Entered in Excel […]

Leave a Reply to begainer Cancel reply

Your email address will not be published. Required fields are marked *