How to reject cell value in Excel

In this article, we will learn How to reject cell value in Excel.

How to reject input cell value by another user in Excel ?

Data validation tool restricts the user to input data to ensure its accuracy and consistency in Excel. It helps in the consistency of the data. It guides the user to input the type of option which is necessary for your spreadsheet.

We can custom restrict our cell using Data validation tool. There are three options under the data validation.

1. Settings : the type of data restrictions with its various criteria, criteria can be text, number, date, time, text length or via using a logic formula .
2. Input Message : Message conveying the entry user to understand the data validation applied over the cell.
3. Error Alert : Excel disallows the invalid context in the cell and displays a message with a dialog box. The alert message can be customized.

Data validation in Excel

Data validation Settings option can be customized as per the requirement. It offers you different types of option to use as per the condition required over the cell.

Let's understand these options one by one as shown below.

Validate Integer numbers : select the whole number option and choose the condition after.

Validate decimal numbers : select the decimal option and choose the condition after.

Validate list values : restrict the user to allow from the pre mentioned list values only. List values is given as cell reference.

Validate date values : Excel has a set date format, validate only date values using Date option and choose the condition from later list.

Validate Time values : Excel has a set Time format, validate only TIme values using Time option and choose the condition from later list.

Validate the Text length values : Restricts the user to apply a condition on the length of the input text value using the Text length option.

Validate Values using the formula condition : Customize the input cell value using the logic condition over the cell using the Custom option. The Formula must return True or False.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have a list of Department and we need to fill the department ID manually. ID must have a specific substring "DT".

But we need to lock this input cells with some rules. Now we learn how to do it.

We will use the data validation attribute of excel. Follow the steps to do the same:

• Select the cells where data validation need to apply.
• Click Data > Data Validation option from the menu.

• The Data validation dailog box appears in front.
• 

• In the Settings option, Select the Custom option from the Allow list.
• A formula box appears under the same option.
• Use the formula in the formula box.

Explanation:

• FIND function returns the position of substring in text or returns error if not found.
• ISNUMBER function checks if FIND function returned number or error.
• ISNUMBER function returns TRUE if number or FALSE if error.

FIll the details as shown below and Click OK

Now when I enter the value as shown below. A default error generates.

As you can see an error is generated "This value doesn't match the data validation restrictions defined for this cell".

But now the problem is how the user know what restrictions are there and what type of value can the cell accept.

So for this, we will customize some changes as shown below.

In Input Message option, type the details under Title & Input Message box as shown above.

In Error alert option, type the details under Title & Error Message box as shown above.

Click Ok.

A message is displayed when the cell is selected And an error message is displayed as shown below.

Now fill the right answers, to complete the table.

To copy the data validation rule to the required cells use the drag down or drag right option in excel.

As you can see, our table is complete. Now its user friendly and informative as well.

Hope this article about How to use the ... function in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

Data Validation in Excel : Restricts users to input value manually using this tool in Excel.

Way to use Vlookup function in Data Validation : allow values from the vlookup table in Excel.

Restrict Dates using Data Validation : allow dates in the cell which lays within Excel date format in Excel.

How to give the error messages in Data Validation : customize your data validation cell with error messages in Excel.

Create Drop Down Lists in Excel using Data Validation : restricts users to input data manually in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.