This post explains that how to **limit calculated cell using Data Validation** in excel. You can use the Data Validation function to limit what a user can type in a cell. And you can also use the data validation to limit user to whole number, dates, a list of selections or a specific range of values.

For example, there is a cell D1 that has the formula =SUM(B1:B3), if the sum of B1:B3 is greater than 20, then you want excel to pop up a warning message dialog and stop entering value in the range B1:B3. How to do this? You can use the Data Validation to set the criteria to limit this calculated cell using the formula =SUM($B$1:$B$3)<=20 in the Data Validation dialog box.

Table of Contents

**Using Data Validation to Limit Calculated Cell**

To Use Data Validation to limit Data Entry in calculated cell, you can follow these steps:

**#1** select the calculated cells that you want to limit.

**#2** go to **DATA **tab, click **Data Validation** command under **Data Tools **group, then select **Data Validation… **menu from the drop down menu list. The **Data Validation** dialog will appear.

**#3** select **Custom **from the drop-down list box of **Allow**, then type the formula **=SUM($B$1:$B$3)<=20** into the Formula text box. Click **OK**.

**#4** switch to **Error Alert** tab in the **Data Validation** dialog box, select **stop **style from the drop-down list of **style**. Then you can enter the error warning message in the text box of** Error message**. Click **OK**.

**#5 **if the sum of the range B1:B3 is greater than 20, the warning dialog will pop-up.

### Related Functions

- Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…

## Leave a Reply

You must be logged in to post a comment.