Limit the Total Amount a User Can Enter into a Range of Cells in Excel

How to limit the amount that a user can enter into a range of cells in Excel.  This works great for budgeting worksheets and really any type of worksheet where you need to force a user to keep values within a certain range or limit.

To achieve this result, we will use the Data Validation feature of Excel.

Here is our sample budget spreadsheet:

Steps to Limit the Amount Entered into a Range

1. We must first create a custom formula that will check the max amount to spend.
   =SUM($B$3:$B$9)<=$D$2

   $B$3:$B$9 is the range that we want to limit to a certain value.  This is where the user will enter the budget items.  This example uses the range B3:B9.  Make sure that you put dollar signs in the range like in the example above or it will not work when we add the data validation.

   $D$2 is the cell that contains the max value that the range can add to.  Change this to the appropriate cell in your spreadsheet and keep the dollar signs in place OR you can simply put a number in here like 10,000 instead of referencing a cell that contains that number.

   Create this formula in the spreadsheet so that you know it works:

   
2. Select the cells whose value you want to limit:
   
3. Go to the Data tab and then click Data Validation or just hit Alt + D + L
   
   
4. In the window that opens, click the Allow drop down menu and select Custom:
   
5. In the Formula section, paste the formula that we created in step 1.
   
6. Hit OK and now we are ready to rock and roll! Test it out:
   

   Oops, looks like the employees aren't getting paid! So, this guy needs to move some budget items around or increase the budget.

Protecting the Max Amount Allowed

In the above example, it is easy for a user to change the max allowed amount and this, usually, isn't a good thing.  Even if you hard-code the value into the Data Validation window, it can still be easily changed.

One simple way to protect the max amount is to put the cell that contains this amount on a separate worksheet and protect that worksheet[LINK] so that only the user who has the correct password can change it.

Notes

If you want to notify the user of the max amount allowed in the cells, you can do this through the error message if you go to the Error Alert tab in the Data Validation window.  Type whatever you want in there and the user will see it when they try to go over the max allowed amount.

Don't forget to download the spreadsheet used in this tutorial.