How to clear restricted values in cells in Excel?

In this article, we will learn how to remove data restrictions applied on a cell of a worksheet. Many times we encounter an error saying “The value you entered is not valid. A user has restricted values that can be entered into this cell.” This is because, the worksheet designer has applied some data validation in the sheet for specific data values only.

We will use two methods to remove the data restrictions from a worksheet as following −

• Using Data Validation Option under Data Tools

• Using Paste Special to delete data validation rules

Let’s see the steps one by one.

Using Data Validation Option under Data Tools

Step 1− Select the cell in the worksheet from which you need to remove the data value restriction.

Step 2− Click Data Validation under Data > Data Tools.

Step 3− Click Clear All under the Settings Tab of the dialog box as shown below. If you want to remove the similar data restriction from all other cells. Then select the option, “Apply these changes to all other cells with the same settings” and then click Clear All followed by OK.

Using Paste Special to delete data validation rules

This method is useful for those users who does not use the mouse clicks so much. To use this method to remove data restrictions, keyboard keys will only be used. Follow the below steps to remove the data restrictions.

Step 1− Select an empty cell without any data restriction and press Ctrl+C to copy the cell.

Step 2− Now, select the cell from which data restriction needs to be removed.

Step 3− Next, press Ctrl+Alt+V and then select Validation under Paste in the Paste Special dialog box opens.

Step 4− Now click OK and the data restriction from the selected cell will be removed

Conclusion

Finally there are two methods to remove data restrictions from a worksheet. Please not that Data Validations are applied by the sheet owner to maintain the uniformity of the data. Always ensure before remove these restrictions. However, to find all the data validation on the current sheet, got to Find & Search > Data Validation to select all of the validated cells. This will highlight all the cells where data validation has been applied.