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

Add to Favorites
Author:

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Select Ranges in the Worksheet from a UserForm
Tutorial: Select a range in Excel from a UserForm and have that range input into the form so that yo...
Use a Form to Enter Data into a Table in Excel
Tutorial: You can enter data into a table in Excel using a form; here I'll show you how to do that....
Put Data into a Worksheet using a Macro in Excel
Tutorial: How to input data into cells in a worksheet from a macro. Once you have data in your macro...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course